I have read many posts here and on other sites where they explain how to read a MySQL database and show the data on an HTML form. The problem with all that information is that the examples build the form withing the PHP. Mine already exists and already loaded.
My HTML/PHP:
<html>
<head>
<title>Alpaga Wasi - Facture</title>
<meta http-equiv="Content-Type" content="text/html;charset=windows-1252" >
<meta name="description" content="">
<meta name="keywords" content="">
<link rel="stylesheet" type="text/css" href="StyleSheet_Invoice.css"/>
<style>
@media print
{
input.Button {display:none;}
button.Button {display:none;}
}
</style>
</head>
<body>
<form action="InvoiceViewFunction.php" method="post">
<?php include("InvoiceForm.php"); ?>
<input class="Button" type="submit" value="Get Invoice" name="nGetInvoice"/>
</form>
</body>
</html>
The <?php include("InvoiceForm.php"); ?> line brings the HTML that contains the table,tr,td and all the input fields. This way I can reuse the same "InvoiceForm" for both inputing data into the database and retrieving it.
Here is my test code I have so far to get the data from the database when the user clicks the "Get Invoice" button.
<?php
//Connect to database
include("../ConfigFiles/ConnectDB_local_i.php");
//Populating the variables
$InvoiceNo = $_POST["nInvoiceNo"];
//Reading a specific invoice from DB
echo "<br>Trying to read from DB with invoice = <br>" . $InvoiceNo . "<br>"; //This tells the correct number just fine.
$query = "SELECT * FROM `invoicedata_table` WHERE InvoiceNo = '$InvoiceNo'";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
if($result->num_rows > 0)
{
while($row = $result->fetch_assoc())
{
echo stripslashes($row['ClientName']) . "<br>";
}
}
else
{
echo 'NO RESULTS';
}
//Close the DB connection
$mysqli->close();
?>
I am still pretty new at programming in general. The above code works just fine to test that my SQL worked fine. However, I don't want the data to simply echo to a new blank screen. I want it to populate the form where I clicked the "Get Invoice" button. I don't want to rebuild it within the PHP unless you experts can tell me it is the common way to do things. Should I be putting my SELECT somewhere else, like client side javascript? There are 41 fields to populate?
Answer as per OP :
Create a php script to receive http requests and fetch data from the database
.
<?php
//--------------------------------------------------------------------------
// Example php script for fetching data from mysql database
//--------------------------------------------------------------------------
$host = "localhost";
$user = "root";
$pass = "root";
$databaseName = "ajax01";
$tableName = "variables";
//--------------------------------------------------------------------------
// 1) Connect to mysql database
//--------------------------------------------------------------------------
include 'DB.php';
$con = mysql_connect($host,$user,$pass);
$dbs = mysql_select_db($databaseName, $con);
//--------------------------------------------------------------------------
// 2) Query database for data
//--------------------------------------------------------------------------
$result = mysql_query("SELECT * FROM $tableName"); //query
$array = mysql_fetch_row($result); //fetch result
//--------------------------------------------------------------------------
// 3) echo result as json
//--------------------------------------------------------------------------
echo json_encode($array);
?>
Create a client script to fetch data from the API script using JQuery AJAX
.
<!---------------------------------------------------------------------------
Example client script for JQUERY:AJAX -> PHP:MYSQL example
---------------------------------------------------------------------------->
<html>
<head>
<script language="javascript" type="text/javascript" src="jquery.js"></script>
</head>
<body>
<!-------------------------------------------------------------------------
1) Create some html content that can be accessed by jquery
-------------------------------------------------------------------------->
<h2> Client example </h2>
<h3>Output: </h3>
<div id="output">this element will be accessed by jquery and this text replaced</div>
<script id="source" language="javascript" type="text/javascript">
$(function ()
{
//-----------------------------------------------------------------------
// 2) Send a http request with AJAX http://api.jquery.com/jQuery.ajax/
//-----------------------------------------------------------------------
$.ajax({
url: 'api.php', //the script to call to get data
data: "", //you can insert url argumnets here to pass to api.php
//for example "id=5&parent=6"
dataType: 'json', //data format
success: function(data) //on recieve of reply
{
var id = data[0]; //get id
var vname = data[1]; //get name
//--------------------------------------------------------------------
// 3) Update html content
//--------------------------------------------------------------------
$('#output').html("<b>id: </b>"+id+"<b> name: </b>"+vname); //Set output element html
//recommend reading up on jquery selectors they are awesome
// http://api.jquery.com/category/selectors/
}
});
});
</script>
</body>
</html>
Answer put up only for reference, it is from here.