mysql_num_rows isn't returning any rows

Question

So, I have some code. I know that mysql_num_rows is deprecated, but since I've already used it I don't want to switch everything to mysqli_. Anyway it was working on my local server and returning 1 or more results based on the entry. This is a PHP login script that I'm trying to get to work. When I uploaded the script to my hostgator server it didn't work. I also checked the PHP version and it mysql_num_rows() shouldn't be deprecated in version 5.4.xxx.

When I try doing a test query of just SELECT * FROM customers it returns one row, but it's not returning anything when I search for where the user and password equal the posted variables. It's frustrating me, and I could use a second set of eyes to look at this.

<?php
include('mysql_connect.php');
if(isset($_POST['submit'])) {
  if(isset($_POST['cususername']) AND isset($_POST['cuspassword'])) {
    $username = $_POST['cususername'];
    $password = md5($_POST['cuspassword']);
        $query = "SELECT * FROM customers WHERE username = '" . $username . "' AND 
        password = '" . $password . "'";
        $returned_user = mysql_query($query);
        $number_of_users = mysql_num_rows($returned_user);
        if($number_of_users > 0){
            echo "It got this far!";
            $customer_array = mysql_fetch_array($returned_user);
            $_SESSION['user_logged'] = 1;
            $_SESSION['id'] = $customer_array['customer_id'];
            $_SESSION['user_name'] = $customer_array['username'];
        }
  } 
}

?>
<?php 
if(isset($_REQUEST['loggoff'])) {
  unset($_SESSION['user_logged']);
  unset($logged_status);
}

if(isset($_SESSION['user_logged'])) { 
    $logged_status = $_SESSION['user_logged']; 
}

if(isset($logged_status)) {
  if($logged_status == 1) {
      echo "You are logged in as " . $_SESSION['user_name'] . ", Click here to <a href='" . $_SERVER['PHP_SELF'] . "?loggoff=1'>Log off</a>" . "<br>";
  }
}
else {?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" id="customerlogin">
    <input type="text" name="cususername" id="cususername" />
    <input type="password" name="cuspassword" id="cuspassword" />
    <br />
    <input type="submit" name="submit" value="Login" />
</form>
<?php}?>

**STOP** using deprecated `mysql_*` API. Use `mysqli_*` or `PDO` with prepared statements. Also you should check for errors after execute queries. — Jens, Aug 26 '15 at 08:18
@Jens please, read. OP said that he don't want to change now to mysqli_ or PDO, in future projects maybe. — Marcos Pérez Gude, Aug 26 '15 at 08:20
@brocksprogramming what is the `mysql_num_rows` function returning? `null`, `0`, .... ? — Marcos Pérez Gude, Aug 26 '15 at 08:22
debug your query by doing a `print $query;` and running the result in phpmyadmin or something similar. Compare the values in the query with the values in the database. it should give you an idea as to why the query fails. — Alex Andrei, Aug 26 '15 at 08:26
One day will come, when the oldest supported version across the globe will be PHP7. That day, my brothers, is promised to us. That day, we will never see anymore any mysql_* library question. So say we all. — STT LCU, Aug 26 '15 at 08:30
Are you sure your password is encrypted using md5 and stored encrypted password in database ??? — Shailesh Katarmal, Aug 26 '15 at 08:35
Ok, so I did what Alex Andrei said and I printed the SQL statement and ran it in phpmyadmin. It didn't return anything. That's when I realized that the md5 inserted into the customers table when registering didn't match the md5($_POST['cuspassword']). Now I'm not sure what to do. Something is making the passwords not match. — brocksprogramming, Aug 26 '15 at 21:16
When I print the query I get SELECT * FROM customers WHERE username = 'hen123' && password = 'e74837c4f86b1ddb2bc0b728a5c63a03' but the md5 on the post is 'e74837c4f86b1ddb2bc0b728a' — brocksprogramming, Aug 26 '15 at 21:21
The thing that baffles me is that it works perfectly on the localhost. Do you think someone may have changed my code around? I'm kind of paranoid about things like that. — brocksprogramming, Aug 26 '15 at 21:26

score 0 · Answer 1 · answered Aug 26 '15 at 08:36

You should check if you have an mysql_error in your query. Btw. you should mysql_real_escape your POST data before use in a mysql_query

If you would use columns names as array index you must use mysql_fetch_assoc() instead of mysql_fetch_array();

Try this Code:

<?php
include('mysql_connect.php');
if( isset( $_POST['submit'] ) )
{

    if( isset( $_POST['cususername'] ) AND
        isset( $_POST['cuspassword'] ) )
    {
        // Prevent mysql injection
        $username = mysql_real_escape_string( $_POST['cususername'] );
        $password = md5( $_POST['cuspassword'] );

        $query = "SELECT * FROM `customers` WHERE `username` = '" . $username . "' AND `password` = '". $password ."'";

        $result = mysql_query( $query );

        // Check if error
        if( mysql_errno() !== NULL )
        {
            exit(   "An mysql_error has occured: \r\n".
                    "Err-No: " . mysql_errno() . "\r\n".
                    "Err-Msg: " . mysql_error()
                );
        }

        // Everything is okay .. Go on
        $iRows = mysql_num_rows( $result );

        // Check if only one user was found
        if( $iRows === 1 )
        {
            echo "It got this far!";

            $customer = mysql_fetch_assoc( $result );

            // Use boolean value instead of numbers
            $_SESSION['user_logged']    = true;
            $_SESSION['id']             = $customer['customer_id'];
            $_SESSION['user_name']      = $customer['username'];
        }
    }
}

I hope I could help you.

If this code wouldn't work try to output the generated sql statment and try it self in your SQL Admintool (e.g. phpMyAdmin).

Sorry for my bad english :/

Your English is superb, don't worry about it. I will try your answer. I first need to make sure that I can figure out what is causing the md5 post variable to be different than the md5 being entered into the database table. — brocksprogramming, Aug 26 '15 at 21:27

score 0 · Answer 2 · answered Aug 26 '15 at 09:33

0

One Suggestion. Rather Than 'AND'. Use '&&'. Some Codes do not execute using AND in if conditon. Just a suggestion.

if(isset($_POST['cususername']) && isset($_POST['cuspassword']))

answered Aug 26 '15 at 09:33

Nana Partykar

10,556
10
48
77

I did change the AND to '&&' as you suggested. – brocksprogramming Aug 26 '15 at 21:28
I got it working but it wasn't the &&. I posted an answer up but for some reason I don't see it. What happened was the the password was being cut off due to varchar(25) limit on the password field. – brocksprogramming Aug 27 '15 at 20:13

score 0 · Answer 3 · edited Nov 09 '15 at 17:39

Ok guys, and for all those with a similar problem searching. Here is my answer. The password field wasn't matching up with the inputted $_POST. So, I got online and looked up an answer. Come to find out the password was varchar(25), which was cutting off the md5 hash. This was what was causing MySQL_NUM_ROWS() to return 0. Don't worry I will add MySQL injection prevention to my script as suggested by few of you, including Chris Wittor. Also, I know that md5 is an outdated way of encypting the information, but it's better than plain text.

mysql_num_rows isn't returning any rows

3 Answers3