Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

[cjackson111]

Hello. I have a php script that is pulling records from a database. Everything up until now has been working great. Even now the first page of results show up as they should. (5 records are displayed per page) When I click on "next page" to view more results I get the following errors -- Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource... and Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource... I have not made any changes to the code. It just started acting up like this. The only change on the server has been from php 4.3 to 4.4. Does anyone know what may be happening? I would greatly appreciate anyones guidance on how to correct it.

[pritaeas]

Perhaps you're not running your query right the second time. Hard to say without code.

[cjackson111]

Wouldn't it repeat the same query when looking for the next set of results? It worked perfectly before. Could an upgrade in the server software affect something like this?

[cjackson111]

Sorry, the code of my php block is below. Not sure what is happening. The first page of results return perfectly but I get the error when I click to the next page to view more results.

<?php
//@session_start();
echo '<table width="100%" border="0">';

$HTTP_SESSION_VARS['min_price'] = $min_price;
$HTTP_SESSION_VARS['max_price'] = $max_price;
$HTTP_SESSION_VARS['min_bedrooms'] = $min_bedrooms;
$HTTP_SESSION_VARS['type'] = $type;
$HTTP_SESSION_VARS['elem_school'] = $elem_school;
$HTTP_SESSION_VARS['mid_school'] = $mid_school;
$HTTP_SESSION_VARS['high_school'] = $high_school;
$HTTP_SESSION_VARS['county'] = $county;
$HTTP_SESSION_VARS['zone'] = $zone;
$HTTP_SESSION_VARS['mls'] = $mls;
$dbh=[I]CODE TO CONNECT TO DATABASE[/I]; 

		if ($zone == 0)
		{	
		$sql = "select LO_ADDRESS1, MLSNUM, LISTPRICE, LO_PHONE, AREA, REMARKS, PROPTYPE, SUBDIVISION, YEARBUILT, BEDS, BATHSFULL, BATHSHALF, SQFTTOTAL, GARAGE, BASEMENT, SCHOOLELEM, SCHOOLJUNIOR, SCHOOLHIGH, STREETNUM, STREETNAME, STREETTYPE, AGENTLIST_FULLNAME, LO_OFFICENAME, LO_IDXSUPPRESSADDR_YN from realestate_listing2 where (SCHOOLELEM = '$elem_school' OR SCHOOLJUNIOR = '$mid_school' OR SCHOOLHIGH = '$high_school') && LISTPRICE >= $min_price && LISTPRICE <= $max_price && BEDS >= $min_bedrooms && PROPTYPE = '$type' order by LISTPRICE"; 
        }
		else
		{
		$sql = "select LO_ADDRESS1, MLSNUM, LISTPRICE, LO_PHONE, AREA, REMARKS, PROPTYPE, SUBDIVISION, YEARBUILT, BEDS, BATHSFULL, BATHSHALF, SQFTTOTAL, GARAGE, BASEMENT, SCHOOLELEM, SCHOOLJUNIOR, SCHOOLHIGH, STREETNUM, STREETNAME, STREETTYPE, AGENTLIST_FULLNAME, LO_OFFICENAME, LO_IDXSUPPRESSADDR_YN from realestate_listing2 where LISTPRICE >= $min_price && LISTPRICE <= $max_price && BEDS >= $min_bedrooms && PROPTYPE = '$type' && AREA = '$zone' order by LISTPRICE";
		}
		$query = mysql_query($sql,$dbh); 
        $total_results = mysql_num_rows($query); 
        $limit = "5"; //limit of archived results per page. 
        $total_pages = ceil($total_results / $limit); //total number of pages 
if (empty($page)) 
    { 
        $page = "1"; //default page if none is selected 
    } 
$offset = ($page - 1) * $limit; //starting number for displaying results out of DB 

    if ($zone == 0)
		{
		$query = "select LO_ADDRESS1, MLSNUM, LISTPRICE, LO_PHONE, AREA, REMARKS, PROPTYPE, SUBDIVISION, YEARBUILT, BEDS, BATHSFULL, BATHSHALF, SQFTTOTAL, GARAGE, BASEMENT, SCHOOLELEM, SCHOOLJUNIOR, SCHOOLHIGH, STREETNUM, STREETNAME, STREETTYPE, AGENTLIST_FULLNAME, LO_OFFICENAME, LO_IDXSUPPRESSADDR_YN from realestate_listing2 where (SCHOOLELEM = '$elem_school' OR SCHOOLJUNIOR = '$mid_school' OR SCHOOLHIGH = '$high_school') && LISTPRICE >= $min_price && LISTPRICE <= $max_price && BEDS >= $min_bedrooms && PROPTYPE = '$type' order by LISTPRICE LIMIT $offset, $limit";
		}
		else
		{
		$query = "select LO_ADDRESS1, MLSNUM, LISTPRICE, LO_PHONE, AREA, REMARKS, PROPTYPE, SUBDIVISION, YEARBUILT, BEDS, BATHSFULL, BATHSHALF, SQFTTOTAL, GARAGE, BASEMENT, SCHOOLELEM, SCHOOLJUNIOR, SCHOOLHIGH, STREETNUM, STREETNAME, STREETTYPE, AGENTLIST_FULLNAME, LO_OFFICENAME, LO_IDXSUPPRESSADDR_YN from realestate_listing2 where LISTPRICE >= $min_price && LISTPRICE <= $max_price && BEDS >= $min_bedrooms && PROPTYPE = '$type' && AREA = '$zone' order by LISTPRICE LIMIT $offset, $limit"; 
		}	
	$result = mysql_query($query); 
//This is the start of the normal results... 

    while ($row = mysql_fetch_array($result)) 
        { 
		
            // display your results as you see fit here. 
			echo '<tr><td>';
			$mls = $row["MLSNUM"];
			
			echo("<b><font face=times new roman size=2 COLOR=#ff0000>MLS #: </font></b>");
			echo "<a href='viewexpandedframe.php?mls=$mls'>".$row["MLSNUM"]."</a>";
			echo("<b><font face=times new roman size=2 COLOR=#003366>\tList Price: </font></b>$".number_format($row["LISTPRICE"], 0)."<br>");
			echo("<b><font face=times new roman size=2 COLOR=#003366>Year Built: </font></b>".$row["YEARBUILT"]."   ");
			echo("<b><font face=times new roman size=2 COLOR=#003366>\tSubdivision: </font></b>".$row["SUBDIVISION"]."<br>");
			if ( $row["LO_IDXSUPPRESSADDR_YN"] == 'Y' ) {
   			echo "<b><font face=times new roman size=2 COLOR=#003366>Address: </font></b> Not Available";
			} 
			else {
			echo("<b><font face=times new roman size=2 COLOR=#003366>Address: </font></b>".$row["STREETNUM"]);
			echo(" \t".$row["STREETNAME"]);
			echo(" \t".$row["STREETTYPE"]);
			}
			echo("<b><font face=times new roman size=2 COLOR=#003366>\tArea: </font></b>".$row["AREA"]."<br><br>");
			echo("<b><font face=times new roman size=2 COLOR=#003366>Property Type: </font></b>".$row["PROPTYPE"]."   ");
			echo("<b><font face=times new roman size=2 COLOR=#003366>\tBedrooms: </font></b>".$row["BEDS"]."<br>");
			echo("<b><font face=times new roman size=2 COLOR=#003366>Full Baths: </font></b>".$row["BATHSFULL"]."   ");
			echo("<b><font face=times new roman size=2 COLOR=#003366>\tHalf Baths: </font></b>".$row["BATHSHALF"]."<br>");
			echo("<b><font face=times new roman size=2 COLOR=#003366>Fin. Sq. Ft.: </font></b>".$row["SQFTTOTAL"]."<br>");
			echo("<b><font face=times new roman size=2 COLOR=#003366>Garage: </font></b>".$row["GARAGE"]."   ");
			echo("<b><font face=times new roman size=2 COLOR=#003366>\tBasement: </font></b>".$row["BASEMENT"]."<br><br>");

			echo "<img src=\"line2.gif\">";
			echo ("<br><br>");
			echo '<td width="300" valign="top"><div align="left">'; //column 2
			
			
			if (file_exists("$row[MLSNUM]_1.jpg")) { 
    echo '<img src="'.$row['MLSNUM'].'_1.jpg" width="200" height="120"><br>'; 
} else { 
    echo "<img src=\"none_sm.gif\"><br>"; 
}
 
echo "<img src=\"images/icon_CVRMLSlogo.gif\">\t\t\t";
echo "<a href='viewexpandedframe.php?mls=$mls'><img src=\"images/information.gif\"></a><br>";
			echo '<tr><td>';
			}
			
        mysql_close(); 


// This is the Previous/Next Navigation 
echo "<font face=Verdana size=1>"; 
echo "Pages:($total_pages)&nbsp;&nbsp;"; // total pages 
if ($page != 1) 
{ 
echo "<a href=$PHP_SELF?page=1><< First</a>&nbsp;&nbsp;&nbsp;"; // First Page Link 
$prevpage = $page - 1; 
echo "&nbsp;<a href=$PHP_SELF?page=$prevpage><<</a>&nbsp;"; // Previous Page Link 
} 
        if ($page == $total_pages) 
            { 
                  $to = $total_pages; 
            } 
        elseif ($page == $total_pages-1) 
            { 
                  $to = $page+1; 
            } 
        elseif ($page == $total_pages-2) 
            { 
                  $to = $page+2; 
            } 
        else 
            { 
                  $to = $page+3; 
            } 
        if ($page == 1 || $page == 2 || $page == 3) 
            { 
                  $from = 1; 
            } 
        else 
            { 
                  $from = $page-3; 
            } 
             
for ($i = $from; $i <= $to; $i++) 

    { 
    if ($i == $total_results) $to=$total_results; 
    if ($i != $page) 
        { 
        echo "<a href=$PHP_SELF?showold=yes&page=$i>$i</a>"; 
        } 
    else 
        { 
        echo "<b><font face=Verdana size=2>[$i]</font></b>"; 
        } 
    if ($i != $total_pages) 
        echo "&nbsp;"; 
    } 
if ($page != $total_pages) 
{ 
$nextpage = $page + 1; 
echo "&nbsp;<a href=$PHP_SELF?page=$nextpage>>></a>&nbsp;"; // Next Page Link 
echo "&nbsp;&nbsp;&nbsp;<a href=$PHP_SELF?page=$total_pages>Last >></a><br>"; // Last Page Link 
} 
echo "</font>"; 

echo '</tr></td>';
// This is the end of the Previous/Next Navigation 
  ?>

[pritaeas]

An upgrade could possibly cause it. Try running:

$result = mysql_query($query) or die(mysql_error());

to see if the query fails.

[cjackson111]

Ok, I just tried that. The first page still shows up fine, just not the second page.

Now I receive the following error.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/richmond/public_html/homesearchtest99.php on line 101
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&& LISTPRICE <= && BEDS >= && PROPTYPE = '' order by LISTPRICE LIMIT 5, 5' at line 1

[pritaeas]

&& LISTPRICE <= && BEDS >= && PROPTYPE = ''

listprice and beds is apparantly not set correctly.

[cjackson111]

It seems like the variables are not being passed to the next page. How can I make sure they values get passed?

[pritaeas]

pass then just like you pass page throught the GET method