How can I solve this mysql_num_rows error

Question

This is the code I am working with

<?php
$link = mysql_connect('localhost', 'root', '')
    OR die(mysql_error());

          $user =  $_POST['username'];
      $password = $_POST['password'];

$login = sprintf("SELECT * FROM imagehosting WHERE username='%s' AND password='%s'  ",
            mysql_real_escape_string($user, $link),
            mysql_real_escape_string($password, $link));

            $rowcount = mysql_num_rows($login);
            echo "<br /><br />" . $rowcount; 
?>

This is the error I am getting

Warning: mysql_num_rows() expects parameter 1 to be resource

I understand that I should use mysqli, but the php.net page used mysql, so I am just trying to learn how to use the $printf and $mysql_num_row.

I am not 100% sure what I am doing with this $sprintf, so I'm sorry if the question is too basic.

`mysql_` is going to be deprecated. ` mysql_real_escape_string` isn't safe to use. Furthermore, the `mysql_real_escape_string` second argument is optional once the connection instance is present. For the end, learn to use PDO and about data sanitization http://anuary.com/54/input-sanitization-and-escaping-for-database-and-stdout-using-php — Gajus, Aug 02 '12 at 12:41

rid · Answer 1 · 2011-05-31T23:49:14.567

mysql_num_rows() expects parameter 1 to be resource, but you gave it a string. You should make a query and obtain the resource returned by mysql_query().

For example:

$resource = mysql_query($login);
$rows = mysql_num_rows($resource);

To use the mysql_* functions, you would do something like this:

mysql_connect('host', 'user', 'password'); // connect to server
mysql_select_db('database name'); // select the database you'll be working with

$result = mysql_query('SELECT test FROM test_table'); // make a query
while ($row = mysql_fetch_assoc($result)) { // go through the obtained results
    echo $row['test']; // use the obtained results
}

mysql_close(); // close the database connection

If you want to use sprintf() to compose the query, I suggest you create the string and store it in an appropriately named variable, to make things easier to follow:

$query = sprintf("SELECT test FROM test_table WHERE a = '%s' AND b = '%s'",
    mysql_real_escape_string($a),
    mysql_real_escape_string($b));

Then send the query to mysql_query() to perform it:

$result = mysql_query($query);

Also note that the $link parameter is optional. If all you ever do in that script is work with a single database connection, you may omit it to make the code clearer.

As a side note, it's bad practice to obtain the number of rows returned by a query with mysql_num_rows() if you don't need the data as well. If all you need to find out is how many rows there are, consider using SELECT COUNT(*) AS total ... instead, then get the value of total with mysql_fetch_assoc().

And last but not least, do consider using PDO instead of the mysql_* functions, as it will likely help you in the long run. Check out this tutorial to find out how to use it.

score 2 · Answer 2 · answered May 31 '11 at 23:31

You can't obtain the number of rows in a recordset from just a string. You have to select a database, and actually execute the query.

The manual page for mysql_num_rows (which of course you read before using the function!) has a handy example of exactly what you need to do.

Here's their example:

<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>

This — with modifications to specific parameter values — is the minimum required to make this DB interface work.

score 1 · Answer 3 · answered May 31 '11 at 23:30

1

I think you forgot to tell the server which database to work on. Can you try this one:

<?php
$link = mysql_connect('localhost', 'root', '') 
    OR die(mysql_error());

$db = mysql_select_db('mydb', $link)

$user =  $_POST['username'];
$password = $_POST['password'];

// ...and so on, and so forth
?>

answered May 31 '11 at 23:30

Jhourlad Estrella

3,545
4
37
66

1

He still needs to actually execute the query, too. – Lightness Races in Orbit May 31 '11 at 23:31
Yeah, I asked same thing... he don't need the `$db` anyway, just the `mysql_select_db('db_name',$link)` :P – RRStoyanov May 31 '11 at 23:32
Return values come quite handy sometimes especially when you're coding while waiting for the roast beef to come out of the oven or when the Lakers are on. It makes it easy for me to understand what the codes are doing by just reading the variable names on the left of the equation. – Jhourlad Estrella Jun 01 '11 at 01:01

RRStoyanov · Accepted Answer · 2011-06-01T07:30:08.830

-1

Try this...

I add you select DB, query execution and correct num_rows select.

<?php
$link = mysql_connect('localhost', 'root', '') OR die(mysql_error());
mysql_select_db('your_db',$link);

$user =  $_POST['username'];
$password = $_POST['password'];

$login = sprintf("SELECT * FROM imagehosting WHERE username='%s' AND password='%s'  ",
            mysql_real_escape_string($user, $link),
            mysql_real_escape_string($password, $link));
$query=mysql_query($login) OR die(mysql_error());

            $rowcount = mysql_num_rows($query);
            echo "<br /><br />" . $rowcount; 
?>

edited Jun 01 '11 at 07:30

answered May 31 '11 at 23:34

RRStoyanov

1,162
1
13
23

if you query fail you'll never get the output of `mysql_num_rows()` so first check if your query run as excepted with `if(!$query){ die('There was an error in query '. mysql_error()); }` if the query don't execute as expected your script will die, else will continue... – afarazit May 31 '11 at 23:56

How can I solve this mysql_num_rows error

4 Answers4