mysql_num_rows error in PHP with mysql_query

Question

Hi i am too new too php and mysql and i want to count the member number due to the search made by user. However, mysql_num_rows doesnt work.

mysql_num_rows(mysql_query("SELECT * FROM members WHERE $title LIKE '%$_POST[search]%' LIMIT $start,$member_number"));

It says "mysql_num_rows(): supplied argument is not a valid MySQL result resource in ..."

NOTE: $title is a select menu which user choose where to search. LIMIT is, as you know :), number of member which is shown in a page.

And also $start= ($page-1)*$member_number; in order to set the first entry in that page. I think the problem is here but i cant solve it. :(

Answer 1

Your query probably has an error, in which case mysql_query will return false.

For this reason, you should not group commands like this. Do it like this:

$result = mysql_query("...");

if (!$result)
 { echo mysql_error(); die(); } // or some other error handling method
                                // like, a generic error message on a public site

$count = mysql_num_rows($result);

Also, you have a number of SQL injection vulnerabilities in your code. You need to sanitize the incoming $search variable:

$search = mysql_real_escape_string($_POST["search"]);

... mysql_query(".... WHERE $title LIKE '%$search%'");

if $start and $end come from outside, you also need to sanitize those before using them in your LIMIT clause. You can't use mysql_real_escape_string() here, because they are numeric values. Use intval() to make sure they contain only numbers.

Using a dynamic column name is also difficult from a sanitation point of view: You won't be able to apply mysql_real_escape_string() here, either. You should ideally compare against a list of allowed column names to prevent injection.

Answer 2

you have to use GET method in your form, not POST.

mysql_num_rows doesn't make sense here.
If you're using limit, you already know the number*.
If you want to know number, you shouldn't use limit nor request rows but select number itself.

// get your $title safe
$fields = array("name","lastname");
$key = array_search($_GET['title'],$fields));
$title = $fields[$key];

//escape your $search
$search = mysql_real_escape_string($_GET['search']);

$sql = "SELECT count(*) FROM members WHERE $title LIKE '%$search%'";
$res = mysql_query($query) or trigger_error(mysql_error()." in ".$sql); 
$row = mysql_fetch_row($res);

$members_found = $row[0]

in case you need just 5 records to show on the page, no need for mysql_num_rows() again:

// Get LIMIT params
$member_number = 5;
$start = 0;
if (isset($_GET['page'])){
  $start = abs($_GET['page']-1)*$member_number;
}
// get your $title safe
$fields = array("name","lastname");
$key = array_search($_GET['title'],$fields));
$title = $fields[$key];

//escape your $search
$search = mysql_real_escape_string($_GET['search']);

$sql = "SELECT count(*) FROM members 
          WHERE `$title` LIKE '%$search%' 
          LIMIT $start, $member_number";
$res = mysql_query($query) or trigger_error(mysql_error()." in ".$sql);
while($row = mysql_fetch_assoc($res){
  $data[] = $row;
}

Now you have selected rows in $data for the further use.

Answer 3

This kind of error generally indicates there is an error in your SQL query -- so it has not been successful, and mysql_query() doesn't return a valid resource ; which, so, cannot be used as a parameter to mysql_num_rows().

You should echo your SQL query, in order to check if it's build OK.

And/or, if mysql_query() returns false, you could use mysql_error() to get the error message : it'll help you debug your query ;-)

Typically, your code would look a bit like this :

$query = "select ...";  // note : don't forget about escaping your data

$result = mysql_query($query);
if (!$result) {
    trigger_error(mysql_error()." in ".$query); 
} else {
    // use the resultset
}