Pulling data from mySql table using PHP and AJAX

**olaand** · 06-11-2011 06:22 AM

Hi, I am a PHP newbie and need some help with this storelocator I am building.

I would like to user to select the county they would like to find store located in by using a drop down menu.

this is the html and AJAX for the user interface

HTML Code:

<html>
<head>
<script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  }
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","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<form>
<select name="shop" onchange="showUser(this.value)">
<option value="">Select a County/Region:</option>
<option value="1">West Midlands</option>
<option value="2">East Midlands</option>
<option value="3">South West</option>
<option value="4">South East</option>
<option value="5">The North</option>
<option value="6">Scotland</option>
<option value="7">Wales</option>
<option value="8">London</option>
<option value="9">Herefordshire</option>
</select>
</form>
<br />
<div id="txtHint"><b>Try one of these outlets or shops</b></div>

</body>
</html>

I have a table called store in mySQL db with the following fields: company_name, address, county, country, phone, web_site.

I would like the user to be able to select e.g. "Herefordshire" and all shops with county = Herefordshire should be displayed. This is the page for the php operation:

PHP Code:


<?php

$q=$_GET["q"];



$con = mysql_connect('localhost', 'shops', 'pwds123');

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }



mysql_select_db("shops", $con);



$sql="SELECT * FROM shop WHERE county = '".$q."'";



$result = mysql_query($sql);



echo "<table border='1'>

<tr>

<th>Company Name</th>

<th>Address</th>

<th>City/Town</th>

<th>County</th>

<th>Post Code</th>

<th>Phone</th>

<th>Web Site</th>

</tr>";



while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['company_name'] . "</td>";

  echo "<td>" . $row['address'] . "</td>";

  echo "<td>" . $row['city_town'] . "</td>";

  echo "<td>" . $row['county'] . "</td>";

  echo "<td>" . $row['post_code'] . "</td>";

   echo "<td>" . $row['phone'] . "</td>";

    echo "<td>" . $row['web_site'] . "</td>";

  echo "</tr>";

  }

echo "</table>";



mysql_close($con);

?>

The above doesn't pull any data - which I assume it because I don't know how to set up the query properly.
The reason I am using AJAX on the html script is because I found an example on the web but I realise it might not be the best way but am not capable to determine that.

Appreciate any help or nudge in the right direction. As I said, am a newbie so need some detailed help.

**NogDog** · 06-11-2011 03:08 PM

Your AJAX function in the JavaScript is calling a page called "getuser.php". Is that the correct file? (I'm asking because the PHP file doesn't seem to have anything to do with getting a user.

)

**olaand** · 06-13-2011 05:08 AM

hi nogdog - yes getuser.php is the correct file....

**maurycy** · 06-13-2011 06:06 AM

could you try this?

Code:

$sql="SELECT * FROM shop WHERE county = {$q}";

**olaand** · 06-13-2011 06:17 AM

thanks for taking the time to reply - I tried your suggestion but no luck. I don't get any error messages or warnings either.... Perhaps using java is a bit too convoluted?

**maurycy** · 06-13-2011 06:19 AM

did you tried to get all records from that call?

Code:

$sql="SELECT * FROM shop";

does it return anything?

change that code too to check if everything is ok:

Code:

if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }else{
alert('something is wrong');
}

try to start your PHP file directly: getuser.php?q=[your value] and see if there is any result

**olaand** · 06-13-2011 06:24 AM

good idea - Yes

PHP Code:


$sql="SELECT * FROM shop";

returns everything so should be nearly there......

**maurycy** · 06-13-2011 06:41 AM

did you tried other suggestions?

**olaand** · 06-13-2011 07:00 AM

...hmm did just now..and when I change the script to

HTML Code:

<html>
<head>
<script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  }
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;
    }
  } else{
alert('something is wrong');
}
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>

it goes back to not pulling any data at all....

**olaand** · 06-14-2011 04:41 AM

With some great tips from above author I managed to narrow it down and by

changing:

HTML Code:

<option value="8">London</option>
<option value="9">Herefordshire</option>

to:

HTML Code:

<option value="london">London</option>
<option value="herefordshire">Herefordshire</option>

it is now working fine.

Installing Firebug helped be see what the query I was using was trying to do.

**maurycy** · 06-14-2011 05:18 AM

hehe, so you was sending wrong value

have a nice day

Cheers

**olaand** · 05-10-2012 06:00 AM

right, I would now like to modify this script so that the html dropdown menu automatically pulls any county that has been added to the database (at the moment I have to add the counties manually to the dropdown). Here is the code for the html page

PHP Code:


<script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  }
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","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>

</head>
<body>

<form>
<select name="shop" class="output" onChange="showUser(this.value)">
<option value="">Select a County/Country:</option>
<option value="bristol">Bristol</option>
<option value="edinburgh">Edinburgh</option>
<option value="inverness">Inverness</option>
<option value="largs">Largs</option>
<option value="somerset">Somerset</option>
<option value="suffolk">Suffolk</option>
<option value="Tyne and Wear">Tyne and Wear</option>
<option value="west sussex">West Sussex</option>
<option value="international">**International**</option>
</select>
</form>
<br />
<div id="txtHint"><b></b></div>

</body>
</html>

and here is the code from the referenced getuser file:

PHP Code:


php
$q=$_GET["q"];

$con = mysql_connect('localhost', 'dbname', 'dbpwd');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dbname", $con);

$sql="SELECT * FROM shop WHERE county = '".$q."'";

$result = mysql_query($sql);

echo "<table width='775' border='0'>
<tr>
<th align=left>Name</font></th> 
<th align=left>Address</th>
<th align=left>Town</th>
<th align=left>Country</th>
<th align=left>Post Code</th>
<th align=left>Phone</th>
<th align=left>Web Site</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['company_name'] . "</td>";
  echo "<td>" . $row['address'] . "</td>";
  echo "<td>" . $row['city_town'] . "</td>";
  echo "<td>" . $row['region'] . "</td>";
  echo "<td width='80'>" . $row['post_code'] . "</td>";
  echo "<td>" . $row['phone'] . "</td>";
  echo "<td>" . $row['web_site'] . "</td>";
  echo "</tr>";
  }
echo "</table>"; 

mysql_close($con);
?>

Many Thanks in advance for any answer

**maurycy** · 05-10-2012 06:22 AM

Basicly what you want to do is populate SELECT box with data existing in database and you can do that in many ways, I'll mention two main solution:
SELECT content is generated on server side in country.php

PHP Code:


$mysql = mysql_query("SELECT Country FROM country_table ORDER BY Country DESC");

$html = ''

while($row = mysql_fetch_assoc($mysql)){

    $html .= '<option value="'.$row['Country'].'">'.$row['Country'].'</option>';

}

echo $html;

that will generate list like this one:
<option value="bristol">Bristol</option>
<option value="edinburgh">Edinburgh</option>
<option value="inverness">Inverness</option>
<option value="largs">Largs</option>
<option value="somerset">Somerset</option>
<option value="suffolk">Suffolk</option>
<option value="Tyne and Wear">Tyne and Wear</option>
<option value="west sussex">West Sussex</option>

then all you need to do is add that to your desired SELECT box

other solution is to export JSON object with Country/County names and then manipulate it from JS level, but i didn't saw any JS framework in your code so i suppose first solution will fit you.

BTW:

PHP Code:


$sql="SELECT * FROM shop WHERE county = '".$q."'";

bad bad bad bad bad, never EVER trust users, they are all filthy beasts waiting for mysql injection

Thread: Pulling data from mySql table using PHP and AJAX

Thread Tools

Display

Pulling data from mySql table using PHP and AJAX

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions