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]

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]

  document.getElementById("price"+num).innerHTML=xmlHttp.responseText

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.

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

[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.

[dybalabj]

I apologize for not reading the notice about posting homework problems. I did not mean to give the impression I am trying to get this work done for me. I am just trying to learn how to properly code this solution, and unfortunately the only teachers I can go to don't know much more than I do and haven't been much help. I'm more just trying to learn to proper syntax for what I want to accomplish, not have someone do the work for me. I will certainly read the links you sent, and thanks very much for you help so far.

[dybalabj]

Thanks again for your help so far. In the interest of time, I've decided to drop the autofill feature as it is, and break the form up into 3 different submissions: get customer info, get price info, and add invoice to database. The first 2 technically reload the form, but keep the already entered data, plus adding the information from the query. It may not be the most responsive and efficient solution, but it works well enough for the purposes of my project.

Thanks again for your help, and I apologize again for seeming so dependent and unwilling to work. As I mentioned in my previous post, web programming is something I am extremely unfamiliar with and am having problems figuring out where to really begin. Give me a c++ program any day :) FYI, I was able to get this feature working in a little less than an hour in .Net.

Dybs