PHP/MySQL mysql_num_rows not returning values

Question

I'm new to PHP and programming in general, but am working on doing a login. I've got the signup page completed, and my database populates the records fine. However, when this code gets output it says I have 0 rows from the mysql_num_rows($result);... when, it should be coming back successfully showing 1 row when I input the correct username/password. Whether I put in a successful user/pass combo or not, it outputs the same.

I appreciate any help you can provide, code is listed below:

$SQL = "SELECT * FROM account WHERE username = $username AND password = md5($password)";
            $result = mysql_query($SQL);
            $num_rows = mysql_num_rows($result);
            echo $result;
            echo $num_rows;

            // CLOSE CONNECTION
            mysql_close($db_handle);

            // COMPARE $num_rows TO SEE IF A SUCCESSFUL LOGIN, THEN DIRECT TO MEMBERS PAGE

            if ($result) {
                if ($num_rows > 0) {
                    session_start();
                    $_SESSION['login'] = "1";
                    header ("Location: page1.php");
                }   
                    else {
                        $error_message = "Login failed.  Please try again.";
                        echo $num_rows;

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — John Conde
– John Conde, Commented Jan 31, 2013 at 1:53
Both $username and $password should be quoted strings. Hopefully those have at least been sanitized with mysql_real_escape_string() if they come from input. — Michael Berkowski
– Michael Berkowski, Commented Jan 31, 2013 at 1:53
Turn on error reporting, and you will likely see mysql_num_rows() expects parameter 1 to be resource, boolean given. Do: error_reporting(E_ALL); ini_set('display_errors', 1); — Michael Berkowski
– Michael Berkowski, Commented Jan 31, 2013 at 1:54
$username = htmlspecialchars($username); $password = htmlspecialchars($password); And: $username = quote_smart($username, $db_handle); $password = quote_smart($password, $db_handle); quote_smart handles the real_escape_string. — cSharpNewbie
– cSharpNewbie, Commented Jan 31, 2013 at 1:55
@JakeGould why do you say that won't be production code? It looks like something that would be used in production to me. Not to mention learning obsolete technology isn't the smart way to go. Learn that stuff you're supposed to be using. Not the stuff you're supposed to be forgetting. — John Conde
– John Conde, Commented Jan 31, 2013 at 1:59

Mr. Radical · Accepted Answer · 2013-01-31 08:40:38Z

1

EDIT: Complete rewrite

Try this:

<?php



$host = "host";
$user = "user";
$password = "password";
$database = "database";


$username = 'jack'; /* Insert $_Post [''] here with username variable you pass. You could sanitize and validate with for example filter_var (), clean (), etc */
$password_user = 'password from jack'; // same here.

$link = mysqli_connect($host, $user, $password, $database);
        IF (!$link){
        echo ("Unable to connect to database!");
        }

        ELSE{
$query = "SELECT * FROM account WHERE username ='$username' AND password = md5('$password_user')";
            $result = mysqli_query($link, $query);
            $num_rows = mysqli_num_rows($result);
            $row = mysqli_fetch_array($result, MYSQLI_BOTH);

            // COMPARE $num_rows TO SEE IF A SUCCESSFUL LOGIN, THEN DIRECT TO MEMBERS PAGE

            if ($row) {
                    session_start();
                    $_SESSION['login'] = "1"; // pleae not that 1 is converted into a string value
                    $_SESSION['username'] = $username; // added username, just to test.
                    header ("Location: page1.php");
                }   
                    else {
                        $error_message = "Login failed.  Please try again.";
                        echo $error_message;
                    }
            // CLOSE CONNECTION
            mysqli_close($link);            
        }
?>

Sample data:

CREATE TABLE account (
  id INT auto_increment primary key,
  username VARCHAR(30),
  password VARCHAR(50)
  );


INSERT INTO account(username, password)
VALUES 
("bob", md5('password from bob')), 
("jack", md5('password from jack')), 
('joe', md5('password from joe'));

SQL FIDDLE DEMO

Sample page1

<?php
session_start();
$login = $_SESSION['login'];
$username = $_SESSION['username'];

echo '<h1>It WORKS, <i>'.$username.'</i>!!!</h1>';


?>

Important to note is that I have used the MYSQLI library instead of the MYSQL library. If you have more than one column in you table you should select your output per column. For example, $result['id'].

I found that you didn't escape variable in and out in you SQL statement. I have to note that I didn't debug the part below COMPARE $num_rows TO SEE IF A SUCCESSFUL LOGIN, THEN DIRECT TO MEMBERS. I think you can manage that on your own.

W.R.T. the santization and validation you have to do some more work. I don't know how you data is past via the user login in form. Let say you will use POST. In that case you can start at the top of you page with first retrieving all the posted variable using $_POST. Then filter them to make sure you code in is not open for SQL injection. E.g. $username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);

edited Jan 31, 2013 at 8:40

answered Jan 31, 2013 at 2:05

Mr. Radical

1,8381 gold badge20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

cSharpNewbie Over a year ago

Radical: I am passing it through POST, and I am sanitizing it. I used your pasted code and I'm still getting: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\fun\login\login.php on line 53

cSharpNewbie Over a year ago

Thanks for helping though, I'd be lost without this.

Daedalus Over a year ago

The "the MYSQL library instead of the MYSQL library"? What?

Daedalus Over a year ago

@Mr.Radical Not if I don't know what library you intended, no, I can't. I first assumed PDO, but there are several libraries out there.

cSharpNewbie Over a year ago

$num_rows = mysqli_num_rows($result);

|

Collectives™ on Stack Overflow

PHP/MySQL mysql_num_rows not returning values

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related