On the dealer search page of the site im building, I need SSC (Shimano Service Centre) and SSC_PLUS (Shimano Service Centre Plus) nearest shop results.
To be more precise I need the 10 nearest SSC and 2 nearest SSC_PLUS shops.
I have a Shimano DB table with an extra column which is 'SSC_OR_SSC_PLUS' - to distinguish between SSC and SSC_PLUS in the query and therefore results.
On the search page there are 3 ways of searching:
-Using a UK map you can click on any region.
-Using a textfield to enter the postcode.
-Using a DB populated dropdown box of regions.
Hope I have explained well enough, now here is the code with the relevant comments:
<?php
// function to make connection to MySQL database
include('db_connect.php');
$connect = db_connect(); // database connection
if(isset($_POST['postcode_dropdown']))
{
$region = $_POST['postcode_dropdown'];
$region_sql = "SELECT postcode_1 FROM dealers WHERE shop_region='$region'";
$region_sql_result = mysql_query($region_sql);
$row = mysql_fetch_array($region_sql_result);
$postcode = trim($row['postcode_1']);
getDistanceUK_ssc($postcode,100);
getDistanceUK_ssc_plus($postcode,100);
}
elseif(isset($_POST['postcode']))
{
$postcode = $_POST['postcode'];
getDistanceUK_ssc($postcode,100);
getDistanceUK_ssc_plus($postcode,100);
}
elseif(isset($_GET['region']))
{
$region = $_GET['region'];
$region_sql = "SELECT postcode_1 FROM dealers WHERE shop_region='$region'";
$region_sql_result = mysql_query($region_sql);
$row = mysql_fetch_array($region_sql_result);
$postcode = $row['postcode_1'];
getDistanceUK_ssc($postcode,100);
getDistanceUK_ssc_plus($postcode,100);
}
elseif(isset($_GET['postcode']))
{
$postcode = $_GET['postcode'];
getDistanceUK_ssc($postcode,100);
getDistanceUK_ssc_plus($postcode,100);
}
else
{
echo 'Please input search details';
}
function getDistanceUK_ssc($postcode,$area)
{
// database query to find postcodes withinh x ($area) distance from submitted postcode
$query = "SELECT postcode_1, postcode_2, shop_name, shop_address_1, shop_address_2, shop_address_3, shop_phone, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
FROM postcodes a, postcodes b, dealers
WHERE ssc_or_ssc_plus='ssc'
AND a.outcode LIKE '".$postcode."' AND b.outcode = dealers.postcode_1
HAVING (distance < '".$area."')
ORDER BY distance asc LIMIT 10";
$result = mysql_query($query);
echo "<table cellspacing='10' cellpadding='10'>";
echo "<div class='heading' style='width: 400px; padding-left: 30px;'>Your nearest ten Shimano Service Centre from postcode : $postcode</div>";
while ($list = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>";
echo "</td> ";
echo "<td>";
if($distance == 0)
{
echo "<div class='text'>Distance: 0 miles</div>";
}
elseif($distance == 1)
{
echo "<div class='text'>Distance: 1 mile</div>";
}
else
{
echo "<div class='text'>Distance: ".$distance." miles</div>";
}
echo "</td> ";
echo "</tr>";
echo "<tr>";
echo "<td style='vertical-align: top;'>";
echo "<a href='about.php'><img src='images/ssc.jpg' alt='Shimano Service Centre' title='Shimano Service Centre' border='0'></a>";
echo "</td> ";
$distance = $list['distance'];
$distance = round($distance);
echo "<td style='vertical-align: top;'>";
echo "<div class='text'>".ucwords(strtolower($list['shop_name']))."</div>";
echo "<div class='text'>".ucwords(strtolower($list['shop_address_1']))."</div>";
echo "<div class='text'>".ucwords(strtolower($list['shop_address_2']))."</div>";
echo "<div class='text'>".ucwords(strtolower($list['shop_address_3']))."</div>";
echo "<div class='text'>".$list['postcode_1'].$list['postcode_2']."</div>";
echo "</td> ";
echo "<td style='vertical-align: top;'>";
echo "<div class='text'>Telephone : ".$list['shop_phone']."</div>";
echo "</td> ";
echo "</tr>";
}
echo "</table>";
// if no results are found display message
$count=mysql_num_rows($result);
if($count==0)
{
echo "<br />";
echo "<br />";
echo "<div class='text'>There are no dealers within the area searched.</div>";
}
}
function getDistanceUK_ssc_plus($postcode_2,$area_2)
{
// database query to find postcodes withinh x ($area) distance from submitted postcode
$query_2 = "SELECT postcode_1, postcode_2, shop_name, shop_address_1, shop_address_2, shop_address_3, shop_phone, shop_fax, shop_website, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
FROM postcodes a, postcodes b, dealers
WHERE ssc_or_ssc_plus = 'ssc_plus'
AND a.outcode LIKE '".$postcode_2."'
AND b.outcode LIKE dealers.postcode_1
HAVING (distance < '".$area_2."')
ORDER BY distance asc LIMIT 2";
$result_2 = mysql_query($query_2);
echo "<table cellspacing='10' cellpadding='10'>";
echo "<div class='heading' style='width: 400px; padding-left: 30px;'>Your nearest two Shimano Service Centre Plus from postcode : $postcode_2</div>";
while ($list_2 = mysql_fetch_array($result_2))
{
echo "<tr>";
echo "<td>";
echo "</td> ";
echo "<td>";
if($distance_2 == 0)
{
echo "<div class='text'>Distance: 0 miles</div>";
}
elseif($distance_2 == 1)
{
echo "<div class='text'>Distance: 1 mile</div>";
}
else
{
echo "<div class='text'>Distance: ".$distance_2." miles</div>";
}
echo "</td> ";
echo "</tr>";
echo "<tr>";
echo "<td style='vertical-align: top;'>";
echo "<a href='about.php'><img src='images/ssc_plus.jpg' alt='Shimano Service Centre' title='Shimano Service Centre' border='0'></a>";
echo "</td> ";
$distance_2 = $list_2['distance'];
$distance_2 = round($distance_2);
echo "<td style='vertical-align: top;'>";
echo "<div class='text'>".ucwords(strtolower($list_2['shop_name']))."</div>";
echo "<div class='text'>".ucwords(strtolower($list_2['shop_address_1']))."</div>";
echo "<div class='text'>".ucwords(strtolower($list_2['shop_address_2']))."</div>";
echo "<div class='text'>".ucwords(strtolower($list_2['shop_address_3']))."</div>";
echo "<div class='text'>".$list_2['postcode_1'].$list_2['postcode_2']."</div>";
echo "</td> ";
echo "<td style='vertical-align: top;'>";
echo "<div class='text'>Telephone : ".$list_2['shop_phone']."</div>";
echo "</td> ";
echo "</tr>";
}
echo "</table>";
// if no results are found display message
$count_2 = mysql_num_rows($result_2);
if($count_2==0)
{
echo "<br />";
echo "<br />";
echo "<div class='text'>There are no dealers within the area searched.</div>";
echo "<br />";
echo "<br />";
}
}
?>
Can anyone please suggest why the second query (SSC_PLUS query) will not work.
Through trying to debug this I have found that the 2 variables come through to the getDistanceUK_ssc_plus() function okay but no results are returned.
For these reasons I think its most likely to be the dealers DB table that has a problem as the 1st query works.
Heres an example of its structure (well fields in that table):
ssc_or_ssc_plus id shop_name shop_address_1 shop_address_2 shop_address_3 shop_region postcode_1 postcode_2 postcode shop_email shop_phone shop_fax shop_website
Thanks anyone
