scriptygoddessWell, this was fun! I just made a store locator for a client using PHP and AJAX! 😀
I was basically following the instructions from this page: SomeCoders.com (Retrieving database information with AJAX, PHP and MySQL)
Take a look…
Here's what the heart of the "user" page looks like.
At the top – before the HTML tag – I open a connection with the database:
<?php
$dbconnection = mysql_connect ("localhost", "mysqlusername", "mysqlpassword") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mydatabase", $dbconnection) or die("Couldn't open database: ".mysql_error());
?>
Then inside the page…
<h1>Store Locator</h1>
<p>To locate a store, select a state from the list below.</p>
<form name="selectastore">
<?php
$result = mysql_query("SELECT DISTINCT state from storelocator order by state");
?>
<div class="row">
<div class="label">State:</div><div class="field"><select name="state" onchange="getcities(this.value)">
<option value="" selected="selected">Select a State...</option>
<?php
while ($row = mysql_fetch_array($result)) {
?>
<option value="<?=$row['state']; ?>"><?=$row['state']; ?></option>
<?php } ?>
</select></div>
</div>
<div id="cities" class="row">
</div>
<div id="stores" class="row">
</div>
</form>
I used the javascript from the example. There's probably a way to combine it into one function, but because I'm still sorting my way through this, I just duplicated the function to pull cities, and then pull store names…
This is the function to grab the list of cities… (I've removed the comments, please see their post for a more detailed explanation on how this function works.)
function getcities(state){
document.getElementById('stores').innerHTML = "";
/*
doing this in case someone changes their mind and wants to select a different state. When they do - any stores that are already listed will be cleared.
*/
var xmlhttp=false;
try {
xmlhttp = new ActiveXObject('Msxml2.XMLHTTP');
} catch (e) {
try {
xmlhttp = new
ActiveXObject('Microsoft.XMLHTTP');
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!='undefined') {
xmlhttp = new XMLHttpRequest();
}
var file = 'getcities.php?state=';
xmlhttp.open('GET', file + state, true);
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) {
var content = xmlhttp.responseText;
if( content ){
document.getElementById('cities').innerHTML = content;
}
}
}
xmlhttp.send(null)
return;
;
}
This is the function (almost exact duplicate of the above) to grab the list of stores… This time, I'm passing in the selected city, and the selected state.
function getstores(city, state){
var xmlhttp=false;
try {
xmlhttp = new ActiveXObject('Msxml2.XMLHTTP');
} catch (e) {
try {
xmlhttp = new
ActiveXObject('Microsoft.XMLHTTP');
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!='undefined') {
xmlhttp = new XMLHttpRequest();
}
xmlhttp.open('GET', 'getstores.php?state=' + state + '&city=' + city, true);
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) {
var content = xmlhttp.responseText;
if( content ){
document.getElementById('stores').innerHTML = content;
}
}
}
xmlhttp.send(null)
return;
}
Now onto the PHP pages I'm calling with the javascript…
"getcities.php" will populate the storelocator page with another dropdown box of cities it pulls from the database. Here's what that looks like:
<?php
$dbconnection = mysql_connect ("localhost", "mysqlusername", "mysqlpassword") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mydatabase", $dbconnection) or die("Couldn't open database: ".mysql_error());
$result = mysql_query("SELECT DISTINCT city from storelocator where `state`= '".$_GET['state']."' order by city");
echo "<div class=\"label\">City:</div><div class=\"field\"><select name=\"city\" onchange=\"getstores(this.value, document.selectastore.state.options[ document.selectastore.state.selectedIndex ].value)\">";
echo "<option selected=\"selected\" value=\"\" >Select a City...</option>";
while ($row = mysql_fetch_array($result)) {
echo "<option value=\"".$row['city'] ."\">" . $row['city'] ."</option>";
}
echo"</select></div>";
?>
When you select a city, a second php script gets called… "getstores.php". This one grabs all the store data for the selected city and state and updates the page with the listing of stores…
<?php
$dbconnection = mysql_connect ("localhost", "mysqlusername", "mysqlpassword") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mydatabase", $dbconnection) or die("Couldn't open database: ".mysql_error());
$result = mysql_query("SELECT * from storelocator where `city`='".urldecode($_GET['city'])."' AND `state`='".$_GET['state']."';");
while ($row = mysql_fetch_array($result)) {
echo "<p>";
if ($row['url'] != "") {
echo "<a href='".$row['url']."' target='_blank'>";
}
echo $row['store'];
if ($row['url'] != "") {
echo "</a>";
}
echo "<br>";
echo $row['address']."<br>";
if ($row['address2'] != "") {
echo $row['address2']."<br>";
}
echo $row['city'].", ".$row['state']." ".$row['zip'];
if ($row['phone'] != "") {
echo "<br>".$row['phone'];
}
echo "</p>";
}
?>
I haven't done much with AJAX, so if you see any issues, feel free to let me know and I'll update the post with the corrections…
If I get some free time (*cough*HA!*cough*) I'll try to package it all up into a downloadable .zip file – including the "admin" side which has the adding/editing/deleting store functionality.
5/26/2008 Update Since there's still some interest in this, here is a download for the store locator. It includes the sql to create the store locator table, the admin pages, etc. There's no "design" to the pages – and the "login" for the admin pages is pretty bare-bones.
