mattyt81 Posted March 26, 2012 Share Posted March 26, 2012 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 //////////////////////////// ?> Quote Link to comment Share on other sites More sharing options...
requinix Posted March 26, 2012 Share Posted March 26, 2012 Good God man, WTF is up with that code? Quote Link to comment Share on other sites More sharing options...
cpd Posted March 26, 2012 Share Posted March 26, 2012 Whitespaces are useful to split your code up but that's just extreme buddy. Your receiving an error when you don't have any returned data because mysql_fetch_array expects parameter one to be a resource ID returned from a mysql_query. If there is no data to be returned, mysql_query will return false, therefore your variable you think contains your resource ID is actually equal to false resulting in your mysql_fetch_array function outputting an error. Quote Link to comment Share on other sites More sharing options...
mr.noname Posted March 26, 2012 Share Posted March 26, 2012 please reforrmatting your code normaly mysql_fetch_array() is try to get the result from the given resource so if there are empty result, it will thrown out some warning Quote Link to comment Share on other sites More sharing options...
requinix Posted March 26, 2012 Share Posted March 26, 2012 Oh please. If there is no data to be returned, mysql_query will return false If by "no data to be returned" you mean "isn't SELECT, SHOW, EXPLAIN, or other type of query that creates resultsets" then yes. If you mean to include (executable) SELECTs that don't return any rows then no: mysql_query() will always return a resultset resource regardless of how many rows it found. if there are empty result, it will thrown out some warning Uh, no. OP: mysql_query() returned false because the query couldn't be executed. Print out the query and check for problems. There is probably something wrong with it. Quote Link to comment Share on other sites More sharing options...
cpd Posted March 26, 2012 Share Posted March 26, 2012 Oh please. If there is no data to be returned, mysql_query will return false If by "no data to be returned" you mean "isn't SELECT, SHOW, EXPLAIN, or other type of query that creates resultsets" then yes. If you mean to include (executable) SELECTs that don't return any rows then no: mysql_query() will always return a resultset resource regardless of how many rows it found. if there are empty result, it will thrown out some warning Uh, no. OP: mysql_query() returned false because the query couldn't be executed. Print out the query and check for problems. There is probably something wrong with it. Okay, seeming as you want to get extremely picky, I'll re-write my original post. Any statement which returns a resultset will output false if there is an error with your query else it will return a resource ID. If that resource ID points towards no data your mysql_fetch functions will error as it expects the resource ID to point at a memory location in the server memory which contains the data. This is why the error you receive is a "invalid resource ID" because it needs a resource ID which has data; therefore it can not fetch the data and parse it into an array. Detailed enough? Or should we discuss how the data is taken from the memory and parsed into an array? Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted March 26, 2012 Share Posted March 26, 2012 Let's stay on topic. OP: follow requinix suggestion and debug the query by outputting the SQL and checking for errors, then post the results. Quote Link to comment Share on other sites More sharing options...
mr.noname Posted March 26, 2012 Share Posted March 26, 2012 sorry requinix for the my mistake and thank you for you suggestion for the next post i will carefully review Quote Link to comment Share on other sites More sharing options...
requinix Posted March 26, 2012 Share Posted March 26, 2012 Any statement which returns a resultset will output false if there is an error with your query else it will return a resource ID. For SELECTs, yes. If that resource ID points towards no data your mysql_fetch functions will error as it expects the resource ID to point at a memory location in the server memory which contains the data. No. mysql_fetch_*() will return false if there is no data to read. It will not error. I'm not being picky - I'm trying to correct statements that are flat-out wrong. Quote Link to comment Share on other sites More sharing options...
cpd Posted March 26, 2012 Share Posted March 26, 2012 Aww I do have to apologise as the fetch functions do return false if there aren't any rows, they do not error; I begun thinking all the fetch functions behaved like the query function. Try going about your posts without being degrading in future. Posting "Oh please." doesn't help anyone especially after your initial post. I am in this situation however, wrong, but only due to assumption. Sorry for misleading people. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.