PHP AJAX with mySQL

@StaceyBNov 30.2009

Hi,
I am trying to implement AJAX to my forms. I have one dropdown field which is being populated by a backend $mysql_query. I want to click a button “Go” and the selected value returns the row of data via a mysql_fetch. I am following ~~[URL=”http://www.w3schools.com/php/php_ajax_database.asp”]~~this example[/URL] but having problems calling the function. Anyone have another example?

to post a comment

PHP

10 Comments(s) _↴

@opifexNov 30.2009 — #Are you going to write this yourself based on whatever tutorial or try to implement it using one of the ever so popular js frameworks? It could make a difference in the way you want to approach this.

@StaceyBauthorNov 30.2009 — #Hi! Well, I am trying to learn as I go so, but I want to learn it right. I was only using the tutorial as a guide.. What do you suggest?

Here's what I have so far. In my form I have 1 dropdown field and 1 button to execute the AJAX technology, which should populate my form fields.

[CODE]<select name="myfield" id="myfield">
 <?php
 $host="Host name "
 $username="Mysql username ";
 $password="Mysql password ";
 $db_name="Database name ";$tbl_name="Table name";
 
 // Connect to server and select databse.
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");
 
 //dropdown field for Shipping Profile
 $q = mysql_query("SELECT * FROM $tbl_name WHERE key='$key'");
 
 //build the field
 echo "<select name ='myfield'>"
 ;while($r = mysql_fetch_assoc($q)) {echo '<option value="'.$r['ColValue'].'">'.$r['ColValue'].'</option>';}echo '</select>';mysql_close();?>
 </select>
 <input name="Go" type="button" id="Go" value="Go" onclick="showUser(this.value)"></td> 
 
 //this is part of the .js file (based on the example-it's where I get lost)
 function showUser(str) <br />
 { <br />
 xmlhttp=GetXmlHttpObject(); <br />
 if (xmlhttp==null) <br />
 &nbsp;&nbsp; { <br />
 &nbsp;&nbsp; alert ("Browser does not support HTTP Request"); <br />
 &nbsp;&nbsp; return; <br />
 &nbsp;&nbsp; } <br />
 var url="get_location.php"; <br />
 url=url+"?q="+str; <br />
 url=url+"&amp;sid="+Math.random(); <br />
 xmlhttp.onreadystatechange=stateChanged; <br />
 xmlhttp.open("GET",url,true); <br />
 xmlhttp.send(null); <br />
 }
 
 I am just trying to populate form fields based on the mysql_query but the .js function is not working for me.  Any suggestions?
 [/CODE]

@opifexNov 30.2009 — #If what you posted is really what is in the .js file, it won't.

Get rid of all the "<br />" and " " in the js... looks like you picked up the html when you copied from the page. And the javascript needs to be in a file named "selectuser.js" (or whatever you want to name it) linked into your page.

Clean that up first and see what happens.

What localhost server are you running this on or are you uploading to a live server to test?

@StaceyBauthorNov 30.2009 — #Will do. I am uploading to live server.

@StaceyBauthorNov 30.2009 — #Ok,got it working!! Would you mind assisting me in setting the field values. I don't want a <div>.

[CODE]   while($row=mysql_fetch_array($result)) {
 $return[] = $row;
 }
 return $return;
 }
 ?>
 [/CODE]

My fields & columns have the same name.

@opifexNov 30.2009 — #I'm having to guess here.... based on the tutorial.

You can output the redponseText into any html element with id="txtHint"
[CODE]document.getElementById("txtHint").innerHTML=xmlhttp.responseText;[/CODE]
if you want it in a table cell, your html would be

[code=html]
 <td id="txtHint"></td>[/code]

and the resulting html the browser sees will be

[code=html]
 <td id="txtHint">
 <table border='1'>
 <tr>
 <th>First Name</th>
 <th>Last Name</th>
 <th>Age</th>
 <th>Hometown</th>
 <th>Job</th>
 </tr>
 <tr>
 <td>First Name Data</td>
 <td>Last Name Data</td>
 <td>Age Data</td>
 <td>Hometown Data</td>
 <td>Job Data</td>
 </table>
 </td>
 [/code]

...again based on the tutorial data.

Could be a <blockquote>, <span>.... any block element you can put a table into.

@StaceyBauthorNov 30.2009 — #I appreciate your help. I just want to set my fields on my form.

something to this effect:

while($row=mysql_fetch_array($result)) {

$_POST['fld0'] = $row[0];

$_POST['fld1'] = $row[1];

$_POST['fld2'] = $row[2];

...

}

?>

Is this possible? Can I only set the field values with javascript as you suggested with document.getElementById(). Also, I think I need to use mysql_fetch_row instead since I only need one row returned?? Sorry if confusing (learning the basics).

Thanks for all.

@opifexNov 30.2009 — #We'll have to actually see the code that you have written and the form... the tutorial info can only be used so far.

For the "document.getElementById()", that is the way javascript walks the DOM works basically. All you have to do is set the id, so that the javascript knows where to write the output. There aren't any "field values" ... the rows from your db get put where and how you tell them to in the php.

... if you don't want a table as your output, you can change that in the php.

A list for example...

[code=php]
  echo "<ul>";
 echo "<li>" . $row['FirstName'] . "</li>";
 echo "<li>" . $row['LastName'] . "</li>";
 echo "<li>" . $row['Age'] . "</li>";
 echo "<li>" . $row['Hometown'] . "</li>";
 echo "<li>" . $row['Job'] . "</li>";
 }
 echo "</ul>";
 [/code]

You can do just about anything you want to with your data from the php. Like if there are rows that you don't need... just don't print them. Simple as that.

@StaceyBauthorNov 30.2009 — #Hi again. Sorry, it's me. My brain cannot wrap around how to write this code with document.getElementById(). I am getting syntax errors.

This form displays shipping profiles for clients stored in mysql. I am simply populating the fields within the form by clicking the button. The button fetches the sql query that matches the displayed address in the dynamic dropdown field. If you could assist me how on the correct syntax to write the values to one field element based on the query results (ie. ShipName, ShipAddress,...), I should be able to handle the rest!!:rolleyes:

the form connects to db. Here's some of its fields:

[code=html]//dropdown field for Shipping Profile
 $q = mysql_query("SELECT * FROM $tbl_name WHERE hf_AcctNo='$AcctNo'");echo "<select name ='select_ship'>"
 ;while($r = mysql_fetch_assoc($q)) {echo '<option value="'.$r['ShipAddress'].'">'.$r['ShipAddress'].'</option>';}echo '</select>';mysql_close();?>
 </select>
 <input name="Go" type="button" id="Go" value="Go" onclick="showUser(document.shipform.select_ship.value)"></td>
 </tr>
 <tr>
 <td>Ship To Name: </td>
 <td><input name="ShipName" type="text" id="ShipName" value="<?php echo $ShipName?>" /></td>
 <td>&nbsp;</td>
 <td>&nbsp;</td>
 </tr>
 <tr>
 <td>Address: </td>
 <td><input name="ShipAddress" type="text" id="ShipAddress" value="<?php echo $ShipAddress?>" /></td>
 <td>Address: (line 2) </td>
 <td><input name="ShipAddress2" type="text" id="ShipAddress2" value="<?php echo $ShipAddress2?>" /></td>
 </tr>
 </form>[/code]

Here's the file I need to change. It is called from a js function showUser(). It currently creates a table in a div on the html form perfectly. Instead, I just want to populate the fields in the form.

[CODE]<?php
 $q=$_GET['q'];
 
 $host=Host name 
 $username=Mysql username 
 $password=Mysql password 
 $db_name=Database name 
 $tbl_name=Table name
 
 // Connect to server and select databse.
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");
 
 
 $sql="SELECT * FROM $tbl_name WHERE ShipAddress='".$q."'";
 
 $result = mysql_query($sql);
 
 echo "<table border='1'>
 <tr>
 <th>ShipName</th>
 <th>ShipAddress</th>
 <th>ShipAddress2</th>
 <th>ShipCity</th>
 <th>ShipState</th>
 </tr>";
 
 while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['ShipName'] . "</td>";
 echo "<td>" . $row['ShipAddress'] . "</td>";
 echo "<td>" . $row['ShipAddress2'] . "</td>";
 echo "<td>" . $row['ShipCity'] . "</td>";
 echo "<td>" . $row['ShipState'] . "</td>";
 echo "</tr>";
 }
 echo "</table>";
 
 mysql_close();
 ?>
 
 [/CODE]

@opifexDec 01.2009 — #OK.... I wrote this up while I was giving exams. It sovers a little bit different scenario, but everything you need is here.

[code=html]<select name="users" onchange="showUser(this.value)">[/code]
is from the demo...

what it is doing is declaring the content of the value tag as the string for the javascript function and firing the function on click...ok?

so it really doesn't change much for your purpose... let's use the an empty value tag as the query for the text input.... with a submit button.

so... for example....

[code=html]
 <form >
 Query by Address:
 <input id="ShipAddress" name="ShipAddress" type="text" size="20" value="" onblur="showAddress(this.value)"/>
 <input  id="GetAddress" name="GetAddress" type="button" value="Search" />
 </form>
 [/code]

and add in the other text queries in a similar fashion.

Smoke and mirrors on this one... the button is only a distraction to click outside of the input tag to loose focus.

The easiest way (not the most compact or cleanest way) for you to do this is to duplicate the showUser function in the js for each of the queries that you want to make... linking then to its own php query. To keep things simple and have it working in 15 minutes , lets just duplicate the php file in its entirety and make the appropriate changes to the query.

new function in your javascript file...

[code=html]
 function showAddress(str)
 {
 xmlhttp=GetXmlHttpObject();
 if (xmlhttp==null)
 {
 alert ("Browser does not support HTTP Request");
 return;
 }
 var url="getaddress.php";
 url=url+"?q="+str;
 url=url+"&sid="+Math.random();
 xmlhttp.onreadystatechange=stateChanged;
 xmlhttp.open("GET",url,true);
 xmlhttp.send(null);
 }
 [/code]

and create a similar new function for each query.

finally the php files.... this one serves as getaddress.php

[code=php]
 <?php
 $q=$_GET["q"];
 
 $con = mysql_connect('localhost', 'User', 'password');
 if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }
 
 mysql_select_db("database_name", $con);
 
 $sql="SELECT * FROM $tbl_name WHERE ShipAddress LIKE '".$q."'"; // allows upper/lower case differences
 
 //$sql="SELECT * FROM $tbl_name WHERE ShipAddress LIKE '%".$q."%'"; // allows partial match
 
 $result = mysql_query($sql);
 
 echo "<table border='1'>
 <tr>
 <th>ShipName</th>
 <th>ShipAddress</th>
 <th>ShipAddress2</th>
 <th>ShipCity</th>
 <th>ShipState</th>
 </tr>";
 
 while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['ShipName'] . "</td>";
 echo "<td>" . $row['ShipAddress'] . "</td>";
 echo "<td>" . $row['ShipAddress2'] . "</td>";
 echo "<td>" . $row['ShipCity'] . "</td>";
 echo "<td>" . $row['ShipState'] . "</td>";
 echo "</tr>";
 }
 echo "</table>";
 
 mysql_close();
 ?>
 
 [/code]

ahd repeat for the other queries. This could be a lot neater, but this way you can see how it is working.

If you are using a "known" id you can do things a little easier. Just modify the procedure to taste. the above really does cover everything. Just echo the data the way you need it. Hint: do the html first and then put it into the php file.

Final exams and projects will have me very busy for the next 10 days, but I'll try to check in to make sure you got this straight.

Enjoy!

Also in #PHP _↴

Should I use UUIDs to identify data in my database PHP include with parameters Another IDE Thread

Success!

Help @StaceyB spread the word by sharing this article on Twitter...

Tweet This

PHP AJAX with mySQL

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

PHP AJAX with mySQL

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴