error on mysql_num_rows

Question

my code is following iam getting a error on "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource..." help me to solve this

<?php
$type = $_REQUEST['type'];

#defauts
$maxRows_p = 10;
$pageNum_p = 0;
if (isset($_GET['pageNum_p'])) {
  $pageNum_p = $_GET['pageNum_p'];
}
$startRow_p = $pageNum_p * $maxRows_p;
$limit = ' LIMIT '.$startRow_p.', '.$maxRows_p;



//BUILD Addition Search Conditions
if(isset($_REQUEST['district']) && ($_REQUEST['district'] != 'All'))
    $search = ' district = '.$_REQUEST['district'];

if(isset($_REQUEST['city']) && ($_REQUEST['city'] != 'All'))
    $search = ' city = '.$_REQUEST['city'];


$search= ' availibility = "0" ';

$searchStr = @implode(' and ',$search);     

$sql = 'select * FROM properties WHERE type= "'.$type.'" and ';
$sql .= $searchStr;

## DEBUGi
//if($debugP) echo 'Zip Code Radius SQL<hr>'.$sql;

//Add column sorting

if($_REQUEST['sort'] != '')
    $sort = ' order by added asc ';
else
    $sort = $_REQUEST['sort'];

### DEBUG
if($debugP) echo 'Advanced Search Sql<hr>'.$sql;

$error['Results'] = 'Sorry no properties found.';



### Finished Building search sql and execting #####
$sql_with_limit = $sql . $sort . $limit;


if($debugP)
    echo "<hr>Property Search with Limit SQL: $sql_with_limit";     

//Perform search
$searchResults = mysql_query($sql.$sql_with_limit); 

### BUILD OUTPUT ####

if (isset($_GET['totalRows_p'])) {
  $totalRows_p = $_GET['totalRows_p'];
} else {
  if($debugP)
      echo "<hr>Property with out limit SQL: $sql $sort";
  $all_p = mysql_query($sql.$sort);
  $totalRows_p = mysql_num_rows($all_p); //$totalRows_p = mysql_num_rows($all_p);
  if($debugP)
      echo "<br>Result Rows $totalRows_p";
}
$totalPages_p = ceil($totalRows_p/$maxRows_p)-1;


if($debugP)
    echo "<hr>Builting Query String for Limit: ";

//Build query string
foreach($_GET as $name => $value){
    if($name != "pageNum_p")
        $queryString_p .= "&$name=$value";
}

if($debugP)
    echo $queryString_p;                    
?>

Pray, you don't get a visit from [Bobby Tables](http://xkcd.com/327/). This code screams SQL injection. Also, consult [`mysql_error()`](http://php.net/manual/en/function.mysql-error.php) after `mysql_query()`. — Linus Kleen, Jun 28 '11 at 11:03
It seems like your trying to use the "$search" variable as an array... but it's actually a variable.... so the implode shouldn't work and that might be causing the problem. replace $search with $search[] — YonoRan, Jun 28 '11 at 11:03

score 0 · Answer 1 · answered Jun 28 '11 at 11:03

0

It probably because that MySQL was unable to process your SQL statement as supplied. Try changing your mysql_query line to the following so that you get some debug information, just try below code for your query

 $all_p = mysql_query($sql.$sort) or die('Query failed: ' . mysql_error() . "<br />\n$sql");

Thanx.

answered Jun 28 '11 at 11:03

Chandresh M

3,808
1
24
48

iam getting the following error Query failed: 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 '' at line 1 select * FROM properties WHERE type= "0" and – nala Jun 28 '11 at 11:20
ok..i think i got it, your query is **select * FROM properties WHERE type= "0" and** but after 'and' you have put $sort variable for **ORDER BY** statement..so for ORDER BY statement you no need to put AND after type="0"..just remove **'and'** then try it again.. – Chandresh M Jun 28 '11 at 11:24
if you want to put search variable after you **where** condition then you need to put **AND** in that string only.not here after **where condition**..thx. – Chandresh M Jun 28 '11 at 11:27
i tried it no errors always show "Showing: 1 to 10 of 99 Listings" when search through the form and also it will not show the results – nala Jun 28 '11 at 12:07
can you provide me the full query which have been executed.? just **echo** your query and send me...i'll try to fine errors if any. – Chandresh M Jun 28 '11 at 12:32

score 0 · Answer 2 · answered Jun 28 '11 at 11:04

0

I think that your problem is here:

     $all_p = mysql_query($sql.$sort);
     $totalRows_p = mysql_num_rows($all_p);

from the message you get $all_p is not a valid resource. This might be due to an error in the query you are passing

have you tried doing this?

     $all_p = mysql_query($sql.$sort);
     if ($all_p !== false){
         $totalRows_p = mysql_num_rows($all_p);
     }

if you see no warning you have a problem with your query and you should debug that. Try to execute you query in mysql and look if any results are returned.

answered Jun 28 '11 at 11:04

Nicola Peluchetti

76,206
31
145
192

i tried by replacing your subjection, but now no any results show – nala Jun 28 '11 at 11:15
That means that your query has an error. Here yo echo your query: `echo "
Property with out limit SQL: $sql $sort";` Have you tried to run your query into mysql? – Nicola Peluchetti Jun 28 '11 at 11:21
If you run your query you have no result in mysql?Can you show your quwery? – Nicola Peluchetti Jun 28 '11 at 12:09

cwallenpoole · Answer 3 · 2011-06-28T11:18:21.643

You're using direct assignment for $search when you want array assignment. This means that when you go to implode it, it probably returns false(??? I've never suppressed warnings on implode. I have no idea what that does.). That is being misread when appended to $sql. You then append $sort and $limit to $sql and then prepend $sql again for good measure before calling mysql_query. You're also not checking to see if $_REQUEST['city'] and $_REQUEST['desitination'] have values.

Your final result (worse case):

select * FROM properties WHERE type= "'.$type.'" and  select * FROM properties WHERE type= "'.$type.'" and order by added asc Limit 0,10

Your final result (best case):

select * FROM properties WHERE type= "'.$type.'" and city = 'Atlanta' select * FROM properties WHERE type= "'.$type.'" and city = 'Atlanta' order by added asc Limit 0,10

Add this at the top:

$search = array();

and then any time you want to append $search:

$search[] = ' district = '.$_REQUEST['district'];

And replace this line $searchResults = mysql_query($sql.$sql_with_limit); with:

$searchResults = mysql_query($sql_with_limit);

BTW you're begging for SQL injection here. Any time you pull a value from $_REQUEST, you should be using mysql_real_escape_string.

error on mysql_num_rows

3 Answers3