why mysql_num_rows always returns 0 in php

Question

I am using php script to make login page. I use mysql_num_rows() method,
it returns 1 if it matches first row only, it returns 0 when second or third row matches.
Here is My code:

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

    $sql = "SELECT id,username,password FROM user_data WHERE username = '$username' and password = '$password'";
    $result = mysqli_query($sql);
    $count = mysqli_num_rows($result);

Here I also tried mysqli method too, but result are same.
Please Help

Stop using the **deprecated and as of PHP7 removed** mysql_* functions. Migrate to PDO and start using Prepared Statements. — Charlotte Dunois, Apr 17 '16 at 11:24
@CharlotteDunois Don't u guys see `"Here I also tried mysqli method too, but result are same"` line above? — yank, Apr 17 '16 at 11:26
@CharlotteDunois I edit my question, Ok give me the proper solution... Its easy to negative mark, isn't it? — yank, Apr 17 '16 at 11:30
Hash passwords properly using the [Password Hashing API](http://php.net/manual/de/book.password.php) of PHP. If you don't have PHP 5.5 yet, use the [Compatibility Pack](https://github.com/ircmaxell/password_compat) published on GitHub. — Charlotte Dunois, Apr 17 '16 at 11:34
Your code is vulnerable to [SQL-Injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Please start using Prepared, Parameterized Queries. — Charlotte Dunois, Apr 17 '16 at 11:34
We can't answer your question.... we don't know what values you're passing in via $_POST; we don't know what data you have in your database; all we know is that you (apparently) store your passwords in plaintext, and your query doesn't return any records — Mark Baker, Apr 17 '16 at 11:35
Also did you change the database connection code to use `mysqli_`, if not none of the `mysqli_` functions will work. You didnt mention this — RiggsFolly, Apr 17 '16 at 11:46
`$result = mysqli_query($sql);` <<< You didn't connect here and we've no idea which MySQL API you're using here. Edit: Isn't that right @RiggsFolly ? *wink!* or if those POST arrays contain values or not. — Funk Forty Niner, Apr 17 '16 at 12:34
@RST Dont you think Fred-ii- has covered all the possible bases. His answer seems pretty all encompassing to me — RiggsFolly, Apr 17 '16 at 15:12
@RST The reason we pick each other up when we make mistakes is to keep the answers/comments accurate and useful. [Your first comment](http://stackoverflow.com/questions/36675789/why-mysql-num-rows-always-returns-0-in-php?noredirect=1#comment60942347_36675789) would have added errors into the OPs query and wasted his time. _I get picked up, you will get picked up, so as to keep the site clean and accurate. Remember other will find this question and may try all the suggested solutions looking to solve their problem_ — RiggsFolly, Apr 18 '16 at 07:58
@RST Removing quotes around parameters going into `char` / `varchar` columns would have created 2 more errors in that query. I suggest you try it and see if you are in anyway confused. — RiggsFolly, Apr 18 '16 at 08:09

score 0 · Accepted Answer · edited May 23 '17 at 11:52

Seeing your previous question How to insert array of array in phpmyadmin using php

You're using the MySQL_ API to connect with (or most likely, seeing the use of mysql_ functions, and you probably thought that you would slip in a few i's to those MySQLi_ functions along with MySQL_.

Well, you can't.

You need to use the same API from connecting to querying.

However, if you are using MySQLi_ to connect with (which is unknown), you didn't pass the db connection to your query and as the first parameter.

For this line $result = mysqli_query($sql);

Which would look something like:

$result = mysqli_query($connection, $sql);

Also make sure that your POST arrays do contain values.

Consult the manual on connecting with the MySQLi_ API:

http://php.net/manual/en/function.mysqli-connect.php

why mysql_num_rows always returns 0 in php

1 Answers1