PHP autofill text box from MySQL

[dybalabj]

I realize this should probably be a simple solution, but I have only been learning PHP and MySQL for a couple weeks. I am attempting to write an invoicing system that uses a MySQL database to store invoices, product pricing, and customer contact info. I would like to automatically fill in the customer's contact info after typing in the first and last name, and the product pricing info after typing in the product name. From my searches I've found that the onChanged event may be what I'm looking for. However, this only seems to reference JavaScript and AJAX. Is there a way to use this just with PHP? I've done some searching but have very little understanding of JS and AJAX.

Just to make sure my question is clear, I don't want the user to have to click a button to fill in the data, and I don't want to have to reload the page. I just want the customer contact info and pricing info to automatically fill in from the database after tabbing out of the corresponding name text box. I.E. type in first and last name, press Tab, then the phone and address automatically fill in their own text boxes. Or type a product name and press tab, and the pricing info automatically fills in its own text box.

Sorry for being so long-winded. Any suggestions? Is there any code I should post that would help? I can submit the form and update my database w/o any problems, I just have no clue where to begin on this one.

Thanks,
Dybs

[ak1dnar]

http://www.w3schools.com/php/php_ajax_database.asp

In this example can select the FirstName and LastName from a list menu and other details will display using Http Resqusts/Responses. what you have to do get the concept and make it as per your requirement. If you have any doubt please feel free to ask. Good luck!

[dybalabj]

Ajaxrand,

Thanks for the info. It looks to be almost exactly what I need. However, it only looks to fill the info into a table format. I'd like the info to go into text boxes that the user can edit (say, if the customer moves and needs to change a phone number or something). The only way I know to specify a value in a text box is when it's created by <input type="text" value="somethin g>. Is there anywhere else I can specify the "something" for the text box?

Thanks

[The1corrupted]

Ajaxrand,

Thanks for the info. It looks to be almost exactly what I need. However, it only looks to fill the info into a table format. I'd like the info to go into text boxes that the user can edit (say, if the customer moves and needs to change a phone number or something). The only way I know to specify a value in a text box is when it's created by <input type="text" value="somethin g>. Is there anywhere else I can specify the "something" for the text box?

Thanks

Here, if you used a post method in a form, this is what it would look like but on the second page, it shows you how to embed php values in a text box.

First Page, callinvoice.htm l

[HTML]
<form action="get_inv oice.php" method="post">
First Name: <input name="first_nam e" type="text" size="20"><br>
Last Name: <input name="last_name " type="text" size="20">
<input type="sumbit" value="Submit">
</form>
[/HTML]

Second Page, get_invoice.php

[PHP]
<?php
$first_name = strtolower($_PO ST['first_name']); //Eliminates case sensitivity
$last_name = strtolower($_PO ST['last_name']);

$table=mysql_qu ery("SELECT * FROM `your_table` WHERE `first_name`='$ first_name' AND `last_name`='$l ast_name'"); //Get your info
$invoicearray=m ysql_fetch_arra y($table); //Stuff it into an array

echo "<form action='updatei nvoice.php' method='post'>
First Name <input type='text' name='fn' value='".$invoi cearray['first_name']."'><br>
Last Name <input type='text' name='ln' value='".$invoi cearray['last_name']."'>
<input type='submit' value='Submit'>
</form>"; //Call each value by their table structure name and embed them into the values of the textboxes. Put single quotes around the embedded information
?>
[/PHP]

[dybalabj]

OK, It's almost working perfectly now :) I used that tutorial from w3schools and I now have the product and customer info updating correctly, but not quite in the right locations on the page. I think the problem has to do with the placeholder from the <div> tag. I have 2 of these: one for the customer towards the top of the page, another further down for each product line. Here's the code for the customer info:

[PHP]<?php
echo "Customer Info:<br>";
echo "Last:
<input type='text' size=20 name='last'> &nbsp &nbsp &nbsp ";

echo "<script src='selectcust omer.js'></script>";
echo "First:
<input type='text' size=20 name='first' onblur='showCon tact(this.value , last.value)'><b r>";

echo "<div id='cust_info'> ";
echo "Phone 1: &nbsp
<input type='text' size=12 name='phone1'>< br>";

echo "Phone 2: &nbsp
<input type='text' size=12 name='phone2'>< br>";

echo "Address: &nbsp
<input type='text' size=50 name='address'> <br>
</div>";[/PHP]

Here's the code for my product lines:

[PHP]$i=1;
echo "<script src='selectpric e.js'></script>";
while($i<=5){
echo "$i";
echo "<input type='text' size=5 name='quantity$ i'> &nbsp ";
echo "<input type='text' size=90 name='product$i ' onblur='showPri ce(this.value, $i)'>&nbsp ";
echo "<div id='unit'><inpu t type='text' size=6 name='unit$i'></div> &nbsp ";
echo "<input type='text' size=10 name='total$i'> <br>";
$i++;
}[/PHP]


The loop creates a new line with quantity, description, unit price, and total price text boxes. But the <div> tag creates a line break before and after showing the unit price text boxes. Also, For some reason all the updates are occurring in the first product line. So when I update the customer info (which is closer to the top of the form), the phone and address info shows up where the unit price for the first product should be. In my javascript files I do reference the correct div id's, too. Is there something I'm missing?

Thanks,
Dybs

[ak1dnar]

Thanks for the info. It looks to be almost exactly what I need. However, it only looks to fill the info into a table format. I'd like the info to go into text boxes that the user can edit (say, if the customer moves and needs to change a phone number or something). The only way I know to specify a value in a text box is when it's created by <input type="text" value="somethin g>. Is there anywhere else I can specify the "something" for the text box?

If I am continuing with my earlier post, in that example, Its using "responseTe xt". in other words its printing the server page's out put as it is inside the client side. That's what InnerHTML is doing. So in that case in the client side page they are using a "id" (for <DIV> tags) and that paticular responseText out put coming from the server page will set inside the id using innerHTML. here is the line to which doing the job;
[code=javascript]
document.getEle mentById("txtHi nt").innerHTML= xmlHttp.respons eText

[/code]

For your requirement you have do some thing else.

• Send the name to the server page
• Get the records for the specific user/customer and print them in the server page. use XML output instead of HTML out put.
• Parse the XML file,get the xml node values and Set them to the input Text boxes in the client side page. Instead of "innerHTML" you can use "value" attribute for this.

here is another example:

http://www.w3schools.com/php/php_ajax_responsexml.asp

[dybalabj]

If I am continuing with my earlier post, in that example, Its using "responseTe xt". in other words its printing the server page's out put as it is inside the client side. That's what InnerHTML is doing. So in that case in the client side page they are using a "id" (for <DIV> tags) and that paticular responseText out put coming from the server page will set inside the id using innerHTML. here is the line to which doing the job;
[code=javascript]
document.getEle mentById("txtHi nt").innerHTML= xmlHttp.respons eText

[/code]

For your requirement you have do some thing else.

• Send the name to the server page
• Get the records for the specific user/customer and print them in the server page. use XML output instead of HTML out put.
• Parse the XML file,get the xml node values and Set them to the input Text boxes in the client side page. Instead of "innerHTML" you can use "value" attribute for this.

here is another example:
http://www.w3schools.com/php/php_ajax_responsexml.asp

Ajaxrand,
Thanks again for your help. Just to make sure I understand that that example is doing:

1. The <span> tags are placeholders to individual fields, and I can arrange them wherever I need to, they don't necessarily have to be on separate lines, like the <div> tags seem to do.

2. In the PHP page in the example, for the line:
[PHP]echo "<firstname >" . $row['FirstName'] . "</firstname>";[/PHP]

I could replace $row['FirstName'] with
<input type="text" value="$row['FirstName']"> to make sure that my fields are filled in to text boxes in the proper arrangement. Also, the <firstname> tag matches w/ the id in the <span> tag in the HTML form.

Is all that correct? If so, this may be just what I need. Thanks again.

[ak1dnar]

I'll answer for all your questions, first tell me this. do you want to make this with php-ajax or with pure Php?

[ak1dnar]

create your Client Side HTML form Tags with a "ID" attribute on it.

[HTML]<input id="txt_name_of _the_id" name="txt_name_ of_the_id" type="text" size="60"/>[/HTML]

Parse the genarated XML file and set the values to the text input boxes, Then The User can Edit them.

[CODE=javascript]
document.getEle mentById("txt_n ame_of_the_id") .value= 'Set the XML Response Text Value Here instead of this line';
[/CODE]

[dybalabj]

I'll answer for all your questions, first tell me this. do you want to make this with php-ajax or with pure Php?

If possible, I'd like to do this with pure PHP, but if it's easier to do w/ php-ajax I'm certainly willing to learn it. As for the document.getEle mentByID() line in your last post, would those go in the stateChanged() function in the javascript file from the examples on w3schools? Or would those go in the php file after my sql query returns my results?

Thanks,
Dybs

[ak1dnar]

If possible, I'd like to do this with pure PHP, but if it's easier to do w/ php-ajax I'm certainly willing to learn it.

With pure Php you cannot get the records with out reloading your page. (Read your Original Post, You were looking for a solution for AutoFilling input boxes with out using a Button)
So the only option is, you have to use JavaScript and XML on this.

As for the document.getEle mentByID() line in your last post, would those go in the stateChanged() function in the javascript file from the examples on w3schools?

Yes

Or would those go in the php file after my sql query returns my results?

No

And I think I can help you more on this. Please post the codes that you have used so far. Thanks.

[dybalabj]

Ajaxrand,

Thanks so much for your help. I apologize in advance for posting so much code, but you asked for it :)

Here is the segment of code for the product lines in the form:

[PHP]$i=1;
echo "<script src='autofill_p rice.js'></script>";
while($i<=5){
echo "$i";
echo "<input type='text' size=5 name='quantity$ i'> &nbsp ";
echo "<input type='text' size=90 name='product$i ' onblur='showPri ce(this.value, $i)'>&nbsp ";
echo "<input id = 'product$i' type='text' size=6 name='unit$i'> &nbsp ";
echo "<input type='text' size=10 name='total$i'> <br>";
$i++;
}[/PHP]

This is similar to the code I posted in #5. Here is the javascript I have so far for the products. The main functions of concern here would be showPrice() and stateChanged(), of course:

var xmlHttp
var num
function showPrice(str, i)
{ 
xmlHttp=GetXmlHttpObject()
num=i
if (xmlHttp==null)
 {
 alert ("Browser does not support HTTP Request")
 return
 }
var url="get_price.php"
url=url+"?q="+str
url=url+"&id="+i
url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}


function stateChanged() 
{ 
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
 { 
 document.getElementById("price"+num).innerHTML=xmlHttp.responseText 
 } 
}

function GetXmlHttpObject()
{
var xmlHttp=null;
try
 {
 // Firefox, Opera 8.0+, Safari
 xmlHttp=new XMLHttpRequest();
 }
catch (e)
 {
 //Internet Explorer
 try
  {
  xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  }
 catch (e)
  {
  xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
return xmlHttp;
}

Any finally here's the PHP that gets the product info out of the MySQL database and attempts to insert it into the form:

[PHP]<?php
include "connect.ph p";

$q=$_GET["q"];
$i=$_GET["id"];

$sql="SELECT unit_price FROM Product WHERE product_name = '".$q."'";

$result = mysql_query($sq l);

$row = mysql_fetch_arr ay($result);

echo "<?xml version='1.0' encoding='ISO-8859-1'?>
<price>";
echo "<product$i >" . $row['unit_price'] . "</product$i>";
echo "</price>";

mysql_close();
?>[/PHP]

Earlier attempts using the <div> tag did at least update the unit_price field in the form but all updates occurred in the same spot (the first product line). With the code I just posted, I get no updates at all, and no errors or warnings. I do have my php.ini file set to show all warnings. Thanks again for taking the time to look at this. If you need me to post anymore code, let me know.

Thanks,
Dybs

[ak1dnar]

[CODE=javascript] document.getEle mentById("price "+num).innerHTM L=xmlHttp.respo nseText[/CODE]

by using this line you want to print back the price of the product, right? but in here down below, there is no element named "price<num> ".
[PHP]$i=1;
echo "<script src='autofill_p rice.js'></script>";
while($i<=5){
echo "$i";
echo "<input type='text' size=5 name='quantity$ i'> &nbsp ";
echo "<input type='text' size=90 name='product$i ' onblur='showPri ce(this.value, $i)'>&nbsp ";
echo "<input id = 'product$i' type='text' size=6 name='unit$i'> &nbsp ";
echo "<input type='text' size=10 name='total$i'> <br>";
$i++;
}[/PHP]

and also from the server side, you are generating XML out put. But your client side javascript is wrong here because you are using responseText. Instead of you have to use responseXML.

And on the server side php script that you printing the XML file also you have to do some changes. print the xml nodes in a while loop and put this line out side the loop.
[CODE=php]echo '<?xml version="1.0" encoding="ISO-8859-1"?>
<your_node_name >';[/CODE]

[dybalabj]

I tried making most of the changes you suggested except for the loop idea, but nothing is getting updated in my form at this point. For now I'd like to focus on the customer section, then once that's working I can go back to the product lines.

Btw, this is for a school project in my Databases course, and I need a working demo by Friday, Nov 30 :)

Anyway, here's the form for the customer info:
[PHP]<form action="studio_ update.php" method="post">
<?php
echo "<script src='autofill_c ustomer.js'></script>";
echo "Customer Info:<br>";
echo "Last:
<input type='text' size=20 name='last' onblur='showCon tact(first.valu e, this.value)'> &nbsp &nbsp &nbsp ";

echo "First:
<input type='text' size=20 name='first' onblur='showCon tact(this.value , last.value)'><b r>";

echo "Phone 1: &nbsp
<input id='cust_phone1 ' type='text' size=12 name='phone1'> <br>";

echo "Phone 2: &nbsp
<input id='cust_phone2 ' type='text' size=12 name='phone2'> <br>";

echo "Address: &nbsp
<input id='cust_addres s' type='text' size=50 name='address'> <br>";

echo "<hr>";[/PHP]
The form close tag is further down after the products.

Below is my javascript code:

var xmlHttp

function showContact(first, last)
{ 
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
 {
 alert ("Browser does not support HTTP Request")
 return
 }
var url="get_customer.php"
url=url+"?f="+first
url=url+"&l="+last
url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged 
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

function stateChanged() 
{ 
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
 { 
 document.getElementById("cust_phone1").innerHTML=xmlHttp.responseXML 
 document.getElementById("cust_phone2").innerHTML=xmlHttp.responseXML
 document.getElementById("cust_address").innerHTML=xmlHttp.responseXML 
 } 
}

And finally my server side PHP script to get the customer info:
[PHP]<?php
include "connect.ph p";

$first=$_GET["f"];
$last=$_GET["l"];

$sql="SELECT customer_id, phone1, phone2 FROM Customer WHERE first = '".$first."' AND last = '".$last."'" ;

$result = mysql_query($sq l);

// Row contains the phone numbers
$row = mysql_fetch_arr ay($result);
$id = $row['customer_id'];

// Addr contains only the address
$sql = "SELECT address FROM Customer_Addres s WHERE customer_id = '".$id."'";
$addr = mysql_query($sq l);
$addr = mysql_fetch_arr ay($addr);

echo "<?xml version='1.0' encoding='ISO-8859-1'?>
<person>";

echo "<cust_phon e1>" . $row['phone1'] . "</cust_phone1>";
echo "<cust_phon e2>" . $row['phone2'] . "</cust_phone2>";
echo "<cust_address> " . $addr['address'] . "</cust_address>";

echo "</person>";
mysql_close();
?>[/PHP]

So here's my understanding of what should happen (which was sorta working before, just the updates were in the wrong location on the form):
1) I enter a first or last name, and tab out of that box.

2) The javascript function showCustomer() executes w/ the contents of the firstname and lastname text boxes, and sets the phone and address text boxes to be filled by the results of the responseXML generated by the PHP form.

3) The PHP script gets called, which gets the phone and address out of the database, and sends back an XML document w/ the appropriate info. This then gets parsed (by I don't know what exactly) and filled into the appropriate fields.

As I said, no text fields are being updated now. I think I've made all the changes you suggested (w/o the loop for the product at this point, of course), but I would think the customer should still be getting updated now. I don't see how a loop would be practical for this particular section. Should my echo <id_name> row[id] </id_name> lines include the <input type='text'> tags as well?

As for the products, since each product line has its onBlur event set to showPrice(str, i), I pass the line number into the function, so my PHP script should only update the most recently changed product line, not all the lines every time.

Again, any help or insight is appreciated.

Dybs

[ak1dnar]

I tried making most of the changes you suggested except for the loop idea

Yes for this script no need to put your XML declaration out side the loop. May be its giving only one record based on the Where clause. But there might be possibilities like, there will be two peoples/products with the same name. Then since you have put this XML declaration and the root xml element within the Loop they will repeat. That's what I asked you to move it to the out side.
I am sorry to say this too, as my understandings you are not reading the Tutorial completely. These are some points you hae to refer.

• HTML id Atribute
• document.getEle mentById
• basics of ajax

And Please note that we have some strict rules on posting course works, home work questions. you may better to read this too.
Posting Homework or Coursework Questions and Answers

This site is not a place where you can get your homework and course work done for you. Ignoring the questionable morals of getting someone else to do your work towards a formal qualification you will learn a lot more by attempting the problem yourself, then asking for help with the bits that are not working. You will be more likely to get help if you appear to have made an attempt at the problem yourself.