mysql_num_rows returning resource ID

Mochara

The code for my login page is

<?php


//Database Information

$dbhost = "localhost";
$dbname = "no peeking";
$dbuser = "no peeking";
$dbpass = "no peeking";

//Connect to database

mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

//Start the session

session_start();

//Store the information entered in the login form into variables

$username = $_POST['username'];
$password = md5($_POST['password']);

//Search for account in MySQL database

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";

$result = mysql_query($query);
$total = mysql_num_rows($result);

if (mysql_num_rows($result) != 1) {
    echo "$total";
    echo "There is no account with that username/password combination.";
    include 'index.php';
}

else {
    //Store the username in a session
    $_SESSION['username'] = "$username";
    include 'index.php';
}

?>

However what is returned with you log in with a correct username and password is:

0There is no account with that username/password combination.

I can only persume this means that the mysql_num_rows is returning "0" when it should be returning 1. Any idea what the problem is with this code?

See the page for yourself at:

www.sylvanas.net/dead
Username: Testing
Password: testing

devinemke

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysql_query($query) or exit(mysql_error());
if (mysql_num_rows($result)) {echo 'There is no account with that username/password combination.';}
else {$_SESSION['username'] = $username;}
include('index.php');

Mochara

Thanks for the attempt but that's not going to work as that simply accepts any username that's entered and stores it into the session whether it's included in the database or not.

I'm writing a login script here so I need the script when executed to check how many rows there are in the database with the matching username and password and return either 1 (Usernames are unique) or 0.

It was working but now it simply isn't anymore and it's always returning 0. I don't understand why.

Right it's not a problem with the mysql_num_rows function as I thought it was as my query for testing if a username already exists while registering doesn't work either so it has to be a problem with the database. May attempt to delete all tables and perhaps the database itself and try again..

That worked. Stupid database must have corrupted.

NogDog

First check to see if $result is false after executing the query. If so, log the output of mysql_error() and the actual query string ($query) to see if something funky is happening.

Also, you should escape the username before using it in the query (see [man]mysql_real_escape_string[/man]). The md5()'ed password should be OK as is, but you could escape it too for consistency's sake (escape it after you md5 it). You might want to [man]trim/man the $_POST values before doing anything with them, in case the user accidentally gets any leading/trailing spaces in there.