Error with mysqli_num_rows() when doing a query. Plase help!

[Jazztronik]

Could someone please help me find out why I get this error?:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in c:\SERVER\htdocs....

here is the code. It just tries to connect to a database:

<?php
session_start(); 

if (isset($_POST['userid']) && isset($_POST['password'])) { 
        // User sent log in through form

    // Connection to database:
    @$db = mysqli_connect('localhost', 'authenticator', 'passAthenticator', 'authentication'); // authenticator with pass='passAuthenticator' has been granted select privilege on DB 'authentication'. It's not a real user, but just an automatic registrator.

    // Connection error?:
    if (mysqli_connect_errno()) { 
        // Error trying to acess database 
        echo 'There was an error trying to access database. Please try later.<br />'; 
        exit; 
    } 

    // Short names for form superglobal vars: 
    $userid = $_POST['userid']; 
    $password = $_POST['password']; 

    // Query: 
    $myQuery = "select * from authorized_users where name=".$userid." and password=".$password; 
    $result = mysqli_query($db, $myQuery); 
    $num_results = mysqli_num_rows($result); // THIS LINE PRODUCES THE ERROR

    // Did we get any record containing valid userid and password? 
    if ($num_results > 0) { 
        // We got one.
        // Creating session var:
        $_SESSION['valid_user'] = $userid; 
    } 
    // Finish database connection:                 
    mysqli_free_result($result); // THIS LINE PRODUCES ERROR 'CAUSE USES VAR $result TOO
    mysqli_close($db); 
} 

?>

Please I need your help!

[bubblenut]

$result = mysqli_query($db, $myQuery);  

Here is the problem. the documentation states that on failure mysqli_query returns false on failure so we can gather that the query failed. For a quick fix and to see what the error was replace the offending line with.

$result = mysqli_query($db, $myQuery) or die(mysqli_error());

But be sure to replace this with proper error handling before your application goes live. You don't want the inner workings of your site to be exposed to your users as soon as the database goes down.

[Jazztronik]

Thanks for the help bubblenut! But I'm still stuck and need your help or someone else's.

I've written the die clause, and now I can zoom into the error. It seems to be something wrong in the query. This is the message I get:

Unknown column 'Tigervlc' in 'where clause'

This column refers to the field 'name' in table 'authorized_users' of 'authentication' database, and there's a record in the mentioned table with name = 'Tigervlc'. So I can't see where the error is exactly.

This was que query string:
"select * from authorized_users where name=".$userid." and password=".$password." limit 1";

The field names match with the ones in the query string. More suggestions?

[Jazztronik]

I've fixed it! 😃

I did the query the same as in another example:

$myQuery = 'select * from authorized_users'
	."where password='$password' "
	." and name='$userid' limit 1";

[/B]

instead of

$myQuery = "select * from authorized_users where name=".$userid." and password=".$password." limit 1";

But it would be really appreciated if some veteran on PHP and MySQL clarified why the first method works and not the second one. If I'm not wrong the simple quotes ('...') used in the first and valid method in '$password' and '$userid' don't allow PHP to interpret $password as a variable, but as a string.

I don't understand why the 2nd method doesn't work even by doing this:

$myQuery = "select * from `authorized_users` where name=$userid and password=$password limit 1";

as $userid and $password should be interpreted as their values when they are between double quotes ("...")

Does anyone have an explanation to this all?

[laserlight]

Suppose that $userid = 'Jazztronik' and $password = 'phpbuilder'.
Your query should then be:

SELECT * FROM authorized_users
	WHERE name='Jazztronik' AND password='phpbuilder'
	LIMIT 1;

Notice that the strings 'Jazztronik' and 'phpbuilder' are delimited by single quotes. This is needed so that the SQL parser would recognise them as strings, not as say, column names.

In your examples that dont work, you did not delimit these strings by single quotes, hence the query failed. The problem isnt with the PHP, but with the SQL.

[Jazztronik]

I finally made it in other way:

$myQuery = "select * from authorized_users where name='".$userid."' and password='".$password."' limit 1";

Note the single quotes surrounding the vars.

But I still can't understand why the first method on the post above worked. Those single quotes shouldn't let PHP interpret the vars as vars, but as simple strings starting with a dollar character. ??

[Jazztronik]

laserlight wrote:

Suppose that $userid = 'Jazztronik' and $password = 'phpbuilder'.
Your query should then be:

SELECT * FROM authorized_users
	WHERE name='Jazztronik' AND password='phpbuilder'
	LIMIT 1;

Notice that the strings 'Jazztronik' and 'phpbuilder' are delimited by single quotes. This is needed so that the SQL parser would recognise them as strings, not as say, column names.

In your examples that dont work, you did not delimit these strings by single quotes, hence the query failed. The problem isnt with the PHP, but with the SQL.

Thanks laserlight. I noticed the error, but why PHP interprets '$userid' and '$password' if they're quoted between single quotes in this case?:

$myQuery = 'select * from authorized_users' 
    ."where password='$password' " 
    ." and name='$userid' limit 1";  

I mean, with that method, MYSQL would be searching a record with a string '$userid', such as it is, in the field name, and the same would happen to the field password.

[laserlight]

Those single quotes shouldn't let PHP interpret the vars as vars, but as simple strings starting with a dollar character.

No, those single quotes (in the second and third strings of your first example) are nothing more than single quotes to PHP. They do not delimit the PHP string, unlike in the first string in your first example. What does delimit the PHP string are the double quotes, hence the usual variable interpolation is performed.