I'm querying a post code prefix from a database. The query is a few joins in length but the record set is only a max of 1200. The query keeps timing out. Can any one help me with this?
Code below, query ###### out
Pete Jones
<?
include("header.txt");
if(!$userpcode):
print("Please enter a UK postcode! Please click <a href=http://www.phpbuilder.com/board/archive/index.php/\"javascript:history.go(-1);\">here</a> to return to the search");
else:
$server = "localhost";
$userid = "root";
$pass = "";
$database = "hwc";
$dbtable = "";
$limit = 10;
$connection = mysql_connect("$server","$userid","$pass") or die ("Can't find the server");
$db = mysql_select_db("$database",$connection) or die("Can't find the database");
if (empty($offset) || $offset < 0) {
$offset=0;
}
if (empty($index)) $index=0;
// Perform String splitting to determine format of post code
$checked = substr($userpcode,1,1);
// If second digit is a number select only the first letter and convert to upper case
if($checked >= "0" && $checked <= "9"):
$lpc = substr($userpcode,0,1);
$lpcc = strtoupper($lpc);
// parse_str($lpcc);
else:
// If second digit a letter select first 2 letters and convert to upper case
$lpc = substr($userpcode,0,2);
$lpcc = strtoupper($lpc);
// parse_str($lpcc);
endif;
########################################
// Count number of results in query
$getrows = mysql_query("SELECT L.locationid FROM counties C, districts D, owneractivitylocation O, locations L WHERE O.locationid = L.locationid AND O.districtid = D.districtid AND D.countyid = C.countyid AND O.activityid = $activityid AND L.locationpcode = '$lpcc'", $connection);
$numrows=mysql_num_rows($getrows);
// Perform query to get results
$sql = "SELECT L.locationid, L.locationname, L.locationcat, D.districtname, C.countyname FROM counties C, districts D, owneractivitylocation O, locations L WHERE O.locationid = L.locationid AND O.districtid = D.districtid AND D.countyid = C.countyid AND O.activityid = $activityid AND L.locationpcode = '$lpcc' ORDER BY C.countyname, D.districtname, L.locationname LIMIT $offset,$limit ";
$sql_result = mysql_query($sql, $connection);
##########################################
// Check if there are any results else send back
if($numrows == 0):
print("<tr><td colspan=\"25\" align=\"center\" valign=\"top\"><h2><font color=\"ff0000\">Sorry! There are no Highway Code locations registered in the requested postal area</font></h2>Please click <a href=http://www.phpbuilder.com/board/archive/index.php/\"javascript:history.go(-1);\" class=\"bodylink\">here</a> to return to the search</td></tr>");
else:
// Display header for page
$head = mysql_fetch_object($sql_result);
print("<tr><td><h3>You are currently searching in $head->countyname</h3></td></tr>\n");
// Results loop
while ($row=mysql_fetch_object($sql_result)){
// Increment line index by 1
$index++;
// Display table of results
print("<tr><td colspan=\"2\" valign=\"top\" bgcolor=\"#999999\" class=\"bodylink\">$row->locationname in $row->districtname, $row->countyname</td>\n");
// Check thumbnail image is available and display or blank if failed
$lid = $row->locationid;
$lidt = $lid."t.jpg";
if(!file_exists("./images/$lidt")) {
print("<td rowspan=\"2\" width=\"100\" height=\"60\" valign=\"top\"><img src=\"./images/thumbnailt.gif\"></td></tr>\n");
}
else
print("<td rowspan=\"2\" width=\"100\" height=\"60\" valign=\"top\"><img src=\"./images/$lidt\"></td></tr>\n");
print("<tr><td colspan=\"2\" valign=\"top\" height=\"40\"><p>$row->locationdesc</p></td></tr>\n");
$cat = $row->locationcat;
if($cat >0) {
print("<tr><td colspan=\"2\"> </td><td align=\"center\" width=\"100\"><a href=\"#\" class=\"bodylink\" onClick=\"MM_openBrWindow('details.php3?locationid=$lid','details','toolbar=no,location=yes,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=350,top=50,left=20')\">More info.</a></td></tr>\n");
}
else
print("<tr><td colspan=\"2\"> </td><td align=\"center\" width=\"100\"> </td></tr>\n");
}
if ($numrows <= $limit) {
/* Added this statement so if the result shows less that or the
equal ammount of the limit to show nothing at all , to kill
the "1" that was just generated */
}
else {
// Don't display PREV link if on first page
if ($offset!=0) {
$prevoffset=$offset-$limit;
"<a onMouseOver=\"window.status='Previous $limit Results'; return true\"; href=\"$PHP_SELF?offset=$prevoffset&index=$prevoffset&activityid=$activityid&userpcode=$userpcode\"><B>[Previous]</B></a>   ";
}
/* I particularly like having the [Previous] on the first page
if it has enough results to display at all with my mod , just helps
set the over all structure of the navigation system its self
so ill just add this to the above if statement */
else echo "<b><font color=666666>[Previous]</font></b>   ";
/* If its not to your likeing simply comment it out
*/
// Calculate total number of pages in result
$pages = intval($numrows/$limit);
// $pages now contains total number of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// Now loop through the pages to create numbered links
// ex. 1 2 3 4 5 NEXT
for ($i=1;$i<=$pages;$i++) {
// Check if on current page
if (($offset/$limit) == ($i-1)) {
// $i is equal to current page, so don't display a link
echo " <b><font color=666666>$i</font></b> ";
} else {
// $i is NOT the current page, so display a link to page $i
$newoffset=$limit*($i-1);
echo " <a onMouseOver=\"window.status='Page $i Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset&activityid=$activityid&userpcode=$userpcode\"><B>$i</B></a> \n";
}
}
// Check to see if current page is last page
if (!((($offset/$limit)+1)==$pages) && $pages!=1) {
// Not on the last page yet, so display a NEXT Link
$newoffset=$offset+$limit;
echo "    <a onMouseOver=\"window.status='Next $limit Results'; return true\"; href=http://www.phpbuilder.com/board/archive/index.php/\"$PHP_SELF?offset=$newoffset&index=$newoffset&activityid=$activityid&userpcode=$userpcode\"><B>[Next]</B></a><p>\n";
} /* Im doing the same thing here as i did in the [Previous] above */
else echo "   <b><font color=666666>[Next]</font></b>";
}
mysql_free_result($sql_result);
mysql_close($connection);
endif;
endif;
include("footer.txt");
?>
Code below, query ###### out
Pete Jones
<?
include("header.txt");
if(!$userpcode):
print("Please enter a UK postcode! Please click <a href=http://www.phpbuilder.com/board/archive/index.php/\"javascript:history.go(-1);\">here</a> to return to the search");
else:
$server = "localhost";
$userid = "root";
$pass = "";
$database = "hwc";
$dbtable = "";
$limit = 10;
$connection = mysql_connect("$server","$userid","$pass") or die ("Can't find the server");
$db = mysql_select_db("$database",$connection) or die("Can't find the database");
if (empty($offset) || $offset < 0) {
$offset=0;
}
if (empty($index)) $index=0;
// Perform String splitting to determine format of post code
$checked = substr($userpcode,1,1);
// If second digit is a number select only the first letter and convert to upper case
if($checked >= "0" && $checked <= "9"):
$lpc = substr($userpcode,0,1);
$lpcc = strtoupper($lpc);
// parse_str($lpcc);
else:
// If second digit a letter select first 2 letters and convert to upper case
$lpc = substr($userpcode,0,2);
$lpcc = strtoupper($lpc);
// parse_str($lpcc);
endif;
########################################
// Count number of results in query
$getrows = mysql_query("SELECT L.locationid FROM counties C, districts D, owneractivitylocation O, locations L WHERE O.locationid = L.locationid AND O.districtid = D.districtid AND D.countyid = C.countyid AND O.activityid = $activityid AND L.locationpcode = '$lpcc'", $connection);
$numrows=mysql_num_rows($getrows);
// Perform query to get results
$sql = "SELECT L.locationid, L.locationname, L.locationcat, D.districtname, C.countyname FROM counties C, districts D, owneractivitylocation O, locations L WHERE O.locationid = L.locationid AND O.districtid = D.districtid AND D.countyid = C.countyid AND O.activityid = $activityid AND L.locationpcode = '$lpcc' ORDER BY C.countyname, D.districtname, L.locationname LIMIT $offset,$limit ";
$sql_result = mysql_query($sql, $connection);
##########################################
// Check if there are any results else send back
if($numrows == 0):
print("<tr><td colspan=\"25\" align=\"center\" valign=\"top\"><h2><font color=\"ff0000\">Sorry! There are no Highway Code locations registered in the requested postal area</font></h2>Please click <a href=http://www.phpbuilder.com/board/archive/index.php/\"javascript:history.go(-1);\" class=\"bodylink\">here</a> to return to the search</td></tr>");
else:
// Display header for page
$head = mysql_fetch_object($sql_result);
print("<tr><td><h3>You are currently searching in $head->countyname</h3></td></tr>\n");
// Results loop
while ($row=mysql_fetch_object($sql_result)){
// Increment line index by 1
$index++;
// Display table of results
print("<tr><td colspan=\"2\" valign=\"top\" bgcolor=\"#999999\" class=\"bodylink\">$row->locationname in $row->districtname, $row->countyname</td>\n");
// Check thumbnail image is available and display or blank if failed
$lid = $row->locationid;
$lidt = $lid."t.jpg";
if(!file_exists("./images/$lidt")) {
print("<td rowspan=\"2\" width=\"100\" height=\"60\" valign=\"top\"><img src=\"./images/thumbnailt.gif\"></td></tr>\n");
}
else
print("<td rowspan=\"2\" width=\"100\" height=\"60\" valign=\"top\"><img src=\"./images/$lidt\"></td></tr>\n");
print("<tr><td colspan=\"2\" valign=\"top\" height=\"40\"><p>$row->locationdesc</p></td></tr>\n");
$cat = $row->locationcat;
if($cat >0) {
print("<tr><td colspan=\"2\"> </td><td align=\"center\" width=\"100\"><a href=\"#\" class=\"bodylink\" onClick=\"MM_openBrWindow('details.php3?locationid=$lid','details','toolbar=no,location=yes,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=350,top=50,left=20')\">More info.</a></td></tr>\n");
}
else
print("<tr><td colspan=\"2\"> </td><td align=\"center\" width=\"100\"> </td></tr>\n");
}
if ($numrows <= $limit) {
/* Added this statement so if the result shows less that or the
equal ammount of the limit to show nothing at all , to kill
the "1" that was just generated */
}
else {
// Don't display PREV link if on first page
if ($offset!=0) {
$prevoffset=$offset-$limit;
"<a onMouseOver=\"window.status='Previous $limit Results'; return true\"; href=\"$PHP_SELF?offset=$prevoffset&index=$prevoffset&activityid=$activityid&userpcode=$userpcode\"><B>[Previous]</B></a>   ";
}
/* I particularly like having the [Previous] on the first page
if it has enough results to display at all with my mod , just helps
set the over all structure of the navigation system its self
so ill just add this to the above if statement */
else echo "<b><font color=666666>[Previous]</font></b>   ";
/* If its not to your likeing simply comment it out
*/// Calculate total number of pages in result
$pages = intval($numrows/$limit);
// $pages now contains total number of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// Now loop through the pages to create numbered links
// ex. 1 2 3 4 5 NEXT
for ($i=1;$i<=$pages;$i++) {
// Check if on current page
if (($offset/$limit) == ($i-1)) {
// $i is equal to current page, so don't display a link
echo " <b><font color=666666>$i</font></b> ";
} else {
// $i is NOT the current page, so display a link to page $i
$newoffset=$limit*($i-1);
echo " <a onMouseOver=\"window.status='Page $i Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset&activityid=$activityid&userpcode=$userpcode\"><B>$i</B></a> \n";
}
}
// Check to see if current page is last page
if (!((($offset/$limit)+1)==$pages) && $pages!=1) {
// Not on the last page yet, so display a NEXT Link
$newoffset=$offset+$limit;
echo "    <a onMouseOver=\"window.status='Next $limit Results'; return true\"; href=http://www.phpbuilder.com/board/archive/index.php/\"$PHP_SELF?offset=$newoffset&index=$newoffset&activityid=$activityid&userpcode=$userpcode\"><B>[Next]</B></a><p>\n";
} /* Im doing the same thing here as i did in the [Previous] above */
else echo "   <b><font color=666666>[Next]</font></b>";
}
mysql_free_result($sql_result);
mysql_close($connection);
endif;
endif;
include("footer.txt");
?>