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;

Answer 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);