I have a written a script which allows users to search a database for members, it all works fine if there are results, but when there are no results I get a mysql_fetch_array(): Error. I just can't see why, I will paste my php code below if anyone can help?
<?php
include_once("PHPScripts/checkuserlog.php");
?>
<?php
extract($_REQUEST);
// zipsearch func
function selectQry($sql,$return_type='')
{
//echo $sql;
$retResultSelect
=
array();
$rs
=
mysql_query($sql) or die("MySQL Error Happend : " .mysql_error());
if($retun_type == "")
{
while( ($row
=
mysql_fetch_assoc($rs)))
{
$retResultSelect[]
=
$row;
}
return $retResultSelect;
}
else if($retun_type == "resource") return $rs;
}
function zipRadiusSQL($varZip, $varLatitude, $varLongitude, $varMiles) {
$varLatRange = $varMiles / ((6076 / 5280) * 60) + ($varMiles / 1000);
$varLonRange = $varMiles / (((cos($varLatitude * 3.141592653589 / 180) * 6076.) / 5280.) * 60) + ($varMiles / 1000);
$zipRadiusSQL_str = "SELECT latitude, longitude, district , postcode";
$zipRadiusSQL_str = $zipRadiusSQL_str . " FROM ukpostcodes WHERE postcode != ''";
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND (";
$zipRadiusSQL_str = $zipRadiusSQL_str . "longitude <= (" . $varLongitude . " + " . $varLonRange . ")";
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND ";
$zipRadiusSQL_str = $zipRadiusSQL_str . "longitude >= (" . $varLongitude . " - " . $varLonRange . ")";
$zipRadiusSQL_str = $zipRadiusSQL_str . ")";
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND (";
$zipRadiusSQL_str = $zipRadiusSQL_str . "latitude <= (" . $varLatitude . " + " . $varLatRange . ")";
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND ";
$zipRadiusSQL_str = $zipRadiusSQL_str . "latitude >= (" . $varLatitude . " - " . $varLatRange . ")";
$zipRadiusSQL_str = $zipRadiusSQL_str . ")";
if ($varZip != "") {
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND postcode <> '" . $varZip . "'";
}
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND longitude <> 0";
$zipRadiusSQL_str = $zipRadiusSQL_str . " AND latitude <> 0";
$zipRadiusSQL_str = $zipRadiusSQL_str . " ORDER BY postcode ASC";
$zipRadiusSQL = $zipRadiusSQL_str;
return $zipRadiusSQL;
}
function zipDistCalc($Lat1, $Lon1, $Lat2, $Lon2, $UnitFlag) {
$PI = 3.141592654;
if (is_null($Lat1)) {
return;
}
if($Lat1 == 0 or $Lon1 == 0 or $Lat2 == 0 or $Lon2 == 0) {
$DistCalc = -1;
return $DistCalc;
} elseif ($Lat1 == $Lat2 and $Lon1 == $Lon2) {
$DistCalc = 0;
return $DistCalc;
}
$LatRad1 = $Lat1 * $PI / 180;
$LonRad1 = $Lon1 * $PI / 180;
$LatRad2 = $Lat2 * $PI / 180;
$LonRad2 = $Lon2 * $PI / 180;
$LonRadDif = Abs($LonRad1 - $LonRad2);
$X = Sin($LatRad1) * Sin($LatRad2) + Cos($LatRad1) * Cos($LatRad2) * Cos($LonRadDif);
$RadDist = atan(-$X / sqrt(-$X * $X + 1)) + 2 * atan(1);
$DistMI = $RadDist * 3958.754;
$DistKM = $DistMI * 1.609344;
If (strtoupper($UnitFlag) == "M") {
$zipDistCalc = $DistMI;
} else {
$zipDistCalc = $DistKM;
}
return $zipDistCalc;
}
function getZipCodes($zip_code,$radiusRangeLow='0',$radiusRangeHeigh='10')
{
$zipCodesArray=array();
$fetchZipInfoQry="SELECT postcode,latitude,longitude FROM ukpostcodes WHERE postcode='".addslashes($zip_code)."'";
$zipInfo=selectQry($fetchZipInfoQry);
if(sizeof($zipInfo)>0)
{
$fetchZipsInRangeSql = zipRadiusSQL($zipInfo[0]['postcode'], $zipInfo[0]['latitude'], $zipInfo[0]['longitude'], $radiusRangeHeigh);
$zipRangeInfo=selectQry($fetchZipsInRangeSql);
$zipRangeInfoSize=sizeof($zipRangeInfo);
if($zipRangeInfoSize>0)
{
for($i=0;$i<$zipRangeInfoSize;$i++)
{
$zipLatitude = $zipRangeInfo[$i]["latitude"];
$zipLongitude = $zipRangeInfo[$i]["longitude"];
$zipZipCode = $zipRangeInfo[$i]["postcode"];
$zipDistance = zipDistCalc($zipInfo[0]['latitude'], $zipInfo[0]['longitude'], $zipLatitude, $zipLongitude, "M");
if(($zipDistance > $radiusRangeLow) and ($zipDistance < $radiusRangeHeigh))
{
$zipCodesArray[]="'".$zipZipCode."'";
}
}
// rturn the $zipCodesArray
unset($zipcodeClass);
return $zipCodesArray;
}else
{
## no matching zip codes found
return $zipCodesArray;
}
}else
{
## zip code is invalid ( not exist in the zipcode db)
return $zipCodesArray;
}
}
$zipcode = trim($_REQUEST['zipcode']);
if (strpos($zipcode," ") > 0)
{
$zipcode = trim( substr($zipcode, 0, strpos($zipcode," ")) );
}
else if (strlen($zipcode) > 4)
{
$zipcode = trim( substr($zipcode, 0, strlen($zipcode)-3) );
}
// zipcode search
if($_REQUEST[zipcode]!='' && $_REQUEST[zip_range] > 0)
{
$zipCodeArray=getZipCodes(strtoupper($zipcode),0,$zip_range);
if(sizeof($zipCodeArray)>1)
{
$zipCodeString=implode(',',$zipCodeArray);
$str = "";
for ($k=0;$k<count($zipCodeArray);$k++)
$str .= "(zipcode like '%".str_replace("'","",$zipCodeArray[$k])."') OR (zipcode like '".str_replace("'","",$zipCodeArray[$k]).",%') OR";
$appendQuery = " AND (".$str." (zipcode = '".strtoupper($zipcode)."') OR (zipcode like '%".strtoupper($zipcode).",%') OR (zipcode like '%".strtoupper($zipcode)."')) ";
}
}
elseif($_REQUEST[zipcode]!='' && $_REQUEST[zip_range] == 0 )
{
$appendQuery = " AND ((zipcode = '".strtoupper($zipcode)."') OR (zipcode like '%".strtoupper($zipcode).",%') OR (zipcode like '%".strtoupper($zipcode)."')) ";
}
else
{
$appendQuery ='';
}
$appendQuery2 = " AND zipcode = '".strtoupper($zipcode)."' ";
?>
<?php
$Special = mysql_query("SELECT special.special FROM special");
$row_Special = mysql_fetch_assoc($Special);
$totalRows_Special = mysql_num_rows($Special);
?>
<?php
//calculate years of age (input string: YYYY-MM-DD)
function birthday ($birthday){
list($year,$month,$day) = explode("-",$birthday);
$year_diff = date("Y") - $year;
$month_diff = date("m") - $month;
$day_diff = date("d") - $day;
if ($day_diff < 0 || $month_diff < 0)
$year_diff--;
return $year_diff;
}
$birthday = ''.birthday($birthday).'';
?>
<?php
$fname = '';
$sname = '';
$speciality = '';
$zipcode = '';
$zip_range = '';
$listbyq = '';
$queryString = '';
$queryMsg = '';
if (isset($_GET['listByq'])){
if ($_GET['listByq'] == "pt_search") {
$speciality = $_GET['speciality'];
$fname = $_GET['fname'];
$fname = stripslashes($fname);
$fname = strip_tags($fname);
$fname = eregi_replace("`", "", $fname);
$fname = mysql_real_escape_string($fname);
$sname = $_GET['sname'];
$sname = stripslashes($sname);
$sname = strip_tags($sname);
$sname = eregi_replace("`", "", $sname);
$sname = mysql_real_escape_string($sname);
if ($fname){
$fname = " AND firstname LIKE '%$fname%'";
} else {
$fname = "";
}
if ($sname){
$sname = " AND surname LIKE '%$sname%'";
} else {
$sname = "";
}
if ($zipcode){
$zipcode = " AND zipcode LIKE '%$zipcode%'";
} else {
$zipcode = "";
}
if ($zip_range){
$zip_range = " AND zip_range LIKE '%$zip_range%'";
} else {
$zip_range = "";
}
if ($speciality){
$speciality = " AND special LIKE '%$speciality%'";
} else {
$speciality = "";
}
$queryString = "email_activated='1'$fname$sname$zipcode$zip_range$speciality$appendQuery";
$queryMsg = '<br/><br/><span class=slidingDivHeaderText>Search Found .$nr. Personal Trainers</span><br/><br/>';
}
}
else{
$queryString = "email_activated='1'";
$queryMsg = "<br/><br/><span class=slidingDivHeaderText>Showing Most Recent Personal Trainers</span><br/><br/>";
}
////////////// QUERY THE MEMBER DATA USING THE $queryString variable's value
$sql = mysql_query("SELECT id,firstname,surname,gender,zipcode,rateto,ratefrom,special FROM ptdata WHERE $queryString ORDER BY id DESC");
//////////////////////////////////// Pagination ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$nr = mysql_num_rows($sql); // Get total of Num rows from the database query
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
$pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
} else { // If the pn URL variable is not present force it to be value of page number 1
$pn = 1;
}
//This is where we set how many database items to show on each page
$itemsPerPage = 2;
// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
$pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
$pn = $lastPage; // force it to be $lastpage's value
}
// This creates the numbers to click in between the next and back buttons
// This section is explained well in the video that accompanies this script
$centerPages = "";
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> ';
} else if ($pn == $lastPage) {
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> ';
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> ';
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> ';
} else if ($pn > 1 && $pn < $lastPage) {
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> ';
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> ';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage;
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT id,firstname,surname,gender,zipcode,rateto,ratefrom,special FROM ptdata WHERE $queryString ORDER BY id DESC $limit");
///////////////////
///////////////////////////////////// Pagination Display Setup ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
// This shows the user what page they are on, and the total number of pages
$paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '<img src="images/clearImage.gif" width="48" height="1" alt="Spacer" />';
// If we are not on page 1 we can place the Back button
if ($pn != 1) {
$previous = $pn - 1;
$paginationDisplay .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '&fname=' . $fname . '&sname=' . $sname . '&zipcode=' . $zipcode . '&zip_range=' . $zip_range . '&speciality=' . $speciality . '&submit= '.Search.'&listByq='.pt_search.'"> Back</a> ';
}
// Lay in the clickable numbers display here between the Back and Next links
$paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
// If we are not on the very last page we can place the Next button
if ($pn != $lastPage) {
$nextPage = $pn + 1;
$paginationDisplay .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '&fname=' . $fname . '&sname=' . $sname . '&zipcode=' . $zipcode . '&zip_range=' . $zip_range . '&speciality=' . $speciality . '&submit= '.Search.'&listByq='.pt_search.'"> Next</a> ';
}
}
///////////////////////////////////// END Pagination Display
// Build the Output Section Here
$outputList = '';
while($row = mysql_fetch_array($sql2)){
$id = $row["id"];
$firstname = $row["firstname"];
$surname = $row["surname"];
$gender = $row["gender"];
$ratefrom = $row["ratefrom"];
$rateto = $row["rateto"];
$birthday = $row["birthday"];
$zipcode = $row["zipcode"];
//create a variable to load proper variable
$loadImg=($gender=="Male")?"images/Background/Male_Back.jpg":"images/Background/Female_Back.jpg" ;
/////// Mechanism to Display RateFrom or not //////////////////////////
if ($ratefrom == "") {
$ratefrom = "TBA";
} else {
$ratefrom = '£  '.$ratefrom.' ';
}
/////// Mechanism to Display RateFrom or not //////////////////////////
if ($rateto == "") {
$rateto = "";
} else {
$rateto = '- '.$rateto.'';
}
/////// Mechanism to Display Pic. See if they have uploaded a pic or not //////////////////////////
$check_pic = "Members/$id/image01.jpg";
$default_pic = "Members/FitNetwork.jpg";
if (file_exists($check_pic)) {
$user_pic = "<img src=\"$check_pic\" width=\"145px\" height=\"130px\" border=\"0\" />"; // forces picture to be 120px wide and no more
} else {
$user_pic = "<img src=\"$default_pic\" width=\"145px\" height=\"130px\" border=\"0\" />"; // forces default picture to be 120px wide and no more
}
$fullname = $firstname.' ' .$surname;
$outputList .= '
<table width="800" cellpadding="0px" cellspacing="0px" height="162px" background="'.$loadImg.'" style="background-repeat:repeat-x; padding:10px">
<tr>
<td width="157" align="center" rowspan="6"><a href="http://www.fitnetwork.co.uk/ptmemberprofile.php?id=' . $id . '" target="_blank">' . $user_pic . '</a></td>
<td style="padding-left:10px;" width="140" align="left" height="20"><span class=headerblacktextSmall>Name:</span></td>
<td align="left" width="327"><a style="text-decoration:none;" href="http://www.fitnetwork.co.uk/ptmemberprofile.php?id=' . $id . '" target="_blank"><span class="normal_text_search">' . $fullname . ' </span></a></td>
<td width="174" rowspan="3" align="left"><a style="text-decoration:none;" href="http://www.fitnetwork.co.uk/ptmemberprofile.php?id=' . $id . '" target="_blank"><span class="normal_text_search">View Profile</span></a></td>
</tr>
<tr>
<td style="padding-left:10px;" align="left" height="20"><span class=headerblacktextSmall>Age:</span></td>
<td align="left"><a style="text-decoration:none;" href="http://www.fitnetwork.co.uk/ptmemberprofile.php?id=' . $id . '" target="_blank"><span class="normal_text_search">' .birthday($birthday).'</span></a></td>
</tr>
<tr>
<td style="padding-left:10px;" align="left" height="25"><span class=headerblacktextSmall>Price Range:</span></td>
<td align="left"><a style="text-decoration:none;" href="http://www.fitnetwork.co.uk/ptmemberprofile.php?id=' . $id . '" target="_blank"><span class="normal_text_search">' . $ratefrom . ' ' . $rateto . '</span></a></td>
</tr>
</table>
<br/>
';
}// close while //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////// END QUERY THE MEMBER DATA & Build the Output Section ////////////////////////////
?>