[SOLVED] Letting PHP Ajax fetch data from SQL

[MadFly]

HiI have a client database called my_db, more or less the same as from the tutorials from w3schools. I have a table called clients.And i got this nice ajax-php script here on this website, that allows me to select a certain client, and then display all info about that client underneath the selection. which is exactly what i want to use. But everytime there is a new client, i have to update the dropdown menu in the html file to add the new client as an option.Is there a way that I can tell the ajax to fetch only the firstname and lastname from table clients, and then list it in a dropdown menu automatically? Instead of me having to edit the html file everytime to add the new client as an option?the tutorial or code i am referring to can be found here

[jeffman]

Yes. But you're really discussing two different technologies.1. There is the post query and the PHP mechanism that responds to the query. If you are not familiar with JSON syntax, now's a good time to learn. The point is to output a minimal amount of information rather than a whole HTML document.2. There is the client-side response. You'll need a technique for updating your menu dynamically so as to incorporate data extracted from the response. I can't tell you how to do that because "menu" means a lot of different things in different contexts. If you mean a <select> element, that should be pretty straight forward. Something else might require changing some element's innerHTML.

[MadFly]

Thanks for the speedy reply. Much appreciated.About the "menu", yes i was referring to the <select> element, where it would look something like this

<form>Choose Client<select><option value="selected">Choose...</option><option>Clientname and Surname 1</option><option>Clientname and Surname 2</option><option>Clientname and Surname 3</option></select></form>

And then somewhere inbetween there, I would like for it to connect to the sql database, and retrieve the firstname and lastname of every client. So that as I add new clients on the addnewclient.html page, it will show up on this Chooseclient dropdown menu as a new option. Would that be alot of effort to get it do that?

[jeffman]

The only real effort is figuring out how AJAX works. Your request data should be simple enough that PHP can identify just one record. Then it returns a response. I suggest JSON syntax for serializing the response. Again, that probably sounds weird if you've never done it before, but once you get it, it's really very simple.There are plenty of tutorials out there for learning this stuff.This explains how to add an option to a select element. Notice that you need to adjust the method for IE.The best way to get help is to write as much code as you can and come back here when something doesn't work.

[ShadowMage]

Why not just use PHP to generate the list instead of AJAX? Use SQL to pull all the clients from the database and loop through the results to create each of the <option>'s for the select.That way whenever the page is loaded it will always generate the entire list of clients without you making any changes to it.

[MadFly]

Interesting, that sounds a little easier.How exactly would i go about that if the names i need is in a table called clients. And there is a field for FirstName and LastName?

[ShadowMage]

How exactly would i go about that if the names i need is in a table called clients. And there is a field for FirstName and LastName?

Well, I can't tell you exactly how it's done off the top of my head. I use a custom class to create and manipulate database connections so I'm not too brushed up on the functions needed.You will obviously need to connect to the database so you can start here: http://w3schools.com/php/php_mysql_connect.aspJust follow the tutorial and see what you can come up with. If something doesn't work, come back and ask.

[MadFly]

I figured it out somehow, to let "php" and "ajax" work together to automatically pull the client name from the sql database and then to put it into a dropdown menu, and when i click on a certain user, it displays the info of that user...This code goes into the <head> section of your html page

<script type="text/javascript">function showUser(str){var url="getuser.php";url=url+"?q="+str;url=url+"&sid="+Math.random();if (window.XMLHttpRequest)  {// code for IE7+, Firefox, Chrome, Opera, Safari  xmlhttp=new XMLHttpRequest();  }else  {// code for IE6, IE5  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  }xmlhttp.onreadystatechange=function()  {  if (xmlhttp.readyState==4 && xmlhttp.status==200)	{	document.getElementById("txtHint").innerHTML=xmlhttp.responseText;	}  }xmlhttp.open("GET",url,true);xmlhttp.send();}</script>

And this is the actual form or dropdown menu

<table border="3"><tr><td>Choose Client:</td><?php $con = mysql_connect("localhost","user","pass");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("my_db", $con);$res02 = mysql_query("SELECT * FROM Clients");?><td><select name="users" onchange="showUser(this.value)"> <?php while( $row = mysql_fetch_row( $res02 )) {						$sel = ( Clients === $row['FirstName'] ) ? "id='sel' selected" : "";   	 printf ( " <option %s value='%s'>%s</option>\n", $sel, $row[0] , $row[1].','. $row[2]);  					  }; 					  					  mysql_close($con);					  ?>				  </select>	  </td></tr></table><br /><div id="txtHint"><b>Client details will be shown here.</b></div>

It is basically the same as the tutorial from php-ajax database thing, at http://www.w3schools.com/php/php_ajax_database.asp, all that is different is the

<select name="users" onchange="showUser(this.value)"> <?php while( $row = mysql_fetch_row( $res02 )) {						$sel = ( Clients === $row['FirstName'] ) ? "id='sel' selected" : "";   	 printf ( " <option %s value='%s'>%s</option>\n", $sel, $row[0] , $row[1].','. $row[2]);  					  };