Why I get a mysqli_num_rows() error? I've followed the example at the docs

Question

I'm trying out this simple code, made by myself from scratch, so I think I'm missing something here, because I get an error and don't know why.

I'm trying to log in the user. The script looks into the database and if it founds a result (there's only one user) it sets a session for that user.

This is the HTML log in form at login.php:

<form name="loguearse" action="" method="POST">
Usuario: <input type="text" name="usuario"> 
Clave: <input type="text" name="clave">
<input name="recordar" type="checkbox" value="1"/> recordar datos
<input type="submit" name="submit" value="Entrar!"> <a href="salir.php">Salir</a>
<br>
</form>

And here's the php code to handle the session. It's in the same file, right below the html code:

<?php
    if($_SERVER['REQUEST_METHOD']=='POST'){
        $conexion=mysqli_connect('localhost','user','pass','db')
        OR die('Oops! Error '.mysqli_connect_errno());

        $consulta='  SELECT * FROM usuario 
            WHERE username="'.$_POST['usuario'].'" 
            AND pass="'.$_POST['clave'].'"
            ';

        $resultado=mysqli_query($conexion,$consulta);

        mysqli_close($conexion);

        $rows = mysqli_num_rows($resultado);
        if ($rows) {

            $usuarioLogueado=$_SESSION['usuario'];

            if ($_POST['recordar']) {
                setcookie ("usuario",$usuarioLogueado, time()+24*60*60*30);
                setcookie ("clave",$clave, time()+24*60*60*30);
            } else {
                setcookie("usuario","");
                setcookie("clave","");
            }       
        }

        if (isset($usuarioLogueado)) {
            echo 'Hola '.$usuarioLogueado. ' Session Id: '.session_id();
        } else echo 'Nada'; 
    }

I always get "Nada".

This is how I've set up the usuario table:

create table if not exists usuario (
    uid int unsigned not null auto_increment primary key,
    username char(20) not null,
    pass char(20) not null,
    name char(200) not null,
    email char(200) not null
);

And this is the error I get at the logs:

[:error] [pid 2727] [client 127.0.0.1:44577] PHP Warning:  mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/html/demo/login.php on line 42, referer: http://localhost/demo/index.php

In case anyone wonders about index.php (because of what the error says):

<?php
session_start();
include 'login.php';

I know there probably are tons of other errors, these are just my first steps into programming, but I can't understand why mysqli_num_rows() gives me an error, I've constructed it following the docs example...

Because you've closed the mysqli connection before calling mysqli_num_rows(): don't close the connection until you've finished working with the database — Mark Baker, Jul 26 '14 at 18:48
Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, Jul 26 '14 at 18:50
But as you're working with MySQLi, use bind variables to remove the SQL injection hole in your script — Mark Baker, Jul 26 '14 at 18:50
The link that appears besides "duplicated question" has a list of questions in it (like an index, and a very useful one btw), but I can't find my problem there. WHY THE -1? AND WHY set as duplicated WITHOUT EVEN LOOKING if it is indeed a duplicate? I think it's not fair and somewhat abusive. — Rosamunda, Jul 26 '14 at 23:00

raidenace · Accepted Answer · 2014-07-26T19:58:29.180

you are closing the mysql connection before calling mysqli_num_rows() which is not advisable. I had at first thought that was the problem. However I realized that you are closing the connection AFTER the query was executed.

$resultado=mysqli_query($conexion,$consulta);
mysqli_close($conexion);

When the connection is closed, it also frees all previous resources/objects created while that connection was alive. With that assumption, I would imagine that $resultado has been garbage collected by the time you reach:

$rows = mysqli_num_rows($resultado);

However when a variable is garbage collected/unsetted its value should not change to a boolean true or false. This makes me wonder if there is an error with your SQL to begin with.

$resultado=mysqli_query($conexion,$consulta); - If this call failed then mysqli_query() will return a boolean false, which will be stored in $resultado, which can cause the error you saw.

So:

Move mysqli_close() to the end of the execution
If that does not fix it, check if the query returns a resultset or a boolean false.

Why I get a mysqli_num_rows() error? I've followed the example at the docs

1 Answers1