Check is results exist or not / MySQLi Prepared statement

[aHMAD_SQaLli]

Hello

Im using XAMPP v 1.8.2 /// PHP: 5.4.4 /// phpmyadmin v 3.5.2
I m having trouble with this script it gives this error message

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, object given in C:\xampp\htdocs\mysqli.php on line 43
 

line 43 is if(mysqli_num_rows($stmt) !== 0){

Here is the script

 

$tid = 1;
if ($stmt = mysqli_prepare($link, "SELECT id, username FROM users WHERE id=? ")) {
mysqli_stmt_bind_param($stmt, "s", $tid);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $username);
mysqli_stmt_fetch($stmt);
if(mysqli_num_rows($stmt) !== 0){
while (mysqli_stmt_fetch($stmt)) {
echo "id: <b>[" . $id . "]</b> username: <b>[" . $username. "]</b><br> " ; }
} else { echo "Not Found"; }
mysqli_stmt_close($stmt);}
/* close connection */
mysqli_close($link);

THANKS

[mac_gyver]

when you successfully prepare a query, you get a mysqli statement object. you can only use the properties and methods that are part of a mysqli statement object. see this link for the documentation for those properties and methods -  http://php.net/manual/en/class.mysqli-stmt.php

 

you would need to use the ->num_rows property. you will also need to call the ->store_result() method before you can call the ->num_rows property (all the extra logic needed when using mysqli prepared statements is yet another good reason to use PDO instead.)

 

since your query will/should at most match only one row, it will be more efficient if you just attempt to fetch the row. the ->fetch() method will return a true value if the query matched a row.

[aHMAD_SQaLli]

can you please give me an code example ?

[Ch0cu3r]

mysqli_num_rows expects a mysqli_result object not a mysqli_stmt object (returned by mysqli_prepare).

If are going to use mysqli_num_rows then you need to call mysqli_stmt_get_result before hand. Example

if ($stmt = mysqli_prepare($link, "SELECT id, username FROM users WHERE id=? "))
{
    mysqli_stmt_bind_param($stmt, "s", $tid);
    mysqli_stmt_execute($stmt);
    // get result set from prepared query
    $result = mysqli_stmt_get_result($stmt);
    // pass result to mysqli_num_rows
    if(mysqli_num_rows($result) !== 0)
    {
        // fetch row using mysqli_fetch_assoc
        $row = mysqli_fetch_assoc($result);
        echo "id: <b>[" . $row['id'] . "]</b> username: <b>[" . $row['username']. "]</b><br> ";
    }
    else
    {
        echo "Not Found";
    }
}
else
{
    // prepared query failed
    trigger_error('Unable query users table: ' . mysqli_error($link));
}

But as mac_gyver said there is no need to do the above you can just use mysqli_stmt_fetch as the condition in a if statement to know if the prepared query returned a row

if ($stmt = mysqli_prepare($link, "SELECT id, username FROM users WHERE id=? "))
{
    mysqli_stmt_bind_param($stmt, "s", $tid);
    mysqli_stmt_execute($stmt);
    // the columns returned in the query will be bound to these variables when calling mysqli_stmt_fetch
    mysqli_stmt_bind_result($stmt, $id, $username);
    // mysqli_stmt_fetch returns FALSE if no rows where returned
    if(mysqli_stmt_fetch($stmt))
    {
        echo "id: <b>[" . $id . "]</b> username: <b>[" . $username. "]</b><br> ";
    }
    else
    {
        echo "Not Found";
    }
}
else
{
    // prepared query failed
    trigger_error('Unable query users table: ' . mysqli_error($link));
}

[aHMAD_SQaLli]

Thanks  Ch0cu3r

 

[aHMAD_SQaLli]

 

mysqli_num_rows expects a mysqli_result object not a mysqli_stmt object (returned by mysqli_prepare).

 

If are going to use mysqli_num_rows then you need to call mysqli_stmt_get_result before hand. Example

if ($stmt = mysqli_prepare($link, "SELECT id, username FROM users WHERE id=? "))
{
    mysqli_stmt_bind_param($stmt, "s", $tid);
    mysqli_stmt_execute($stmt);
    // get result set from prepared query
    $result = mysqli_stmt_get_result($stmt);
    // pass result to mysqli_num_rows
    if(mysqli_num_rows($result) !== 0)
    {
        // fetch row using mysqli_fetch_assoc
        $row = mysqli_fetch_assoc($result);
        echo "id: <b>[" . $row['id'] . "]</b> username: <b>[" . $row['username']. "]</b><br> ";
    }
    else
    {
        echo "Not Found";
    }
}
else
{
    // prepared query failed
    trigger_error('Unable query users table: ' . mysqli_error($link));
}

But as mac_gyver said there is no need to do the above you can just use mysqli_stmt_fetch as the condition in a if statement to know if the prepared query returned a row

if ($stmt = mysqli_prepare($link, "SELECT id, username FROM users WHERE id=? "))
{
    mysqli_stmt_bind_param($stmt, "s", $tid);
    mysqli_stmt_execute($stmt);
    // the columns returned in the query will be bound to these variables when calling mysqli_stmt_fetch
    mysqli_stmt_bind_result($stmt, $id, $username);
    // mysqli_stmt_fetch returns FALSE if no rows where returned
    if(mysqli_stmt_fetch($stmt))
    {
        echo "id: <b>[" . $id . "]</b> username: <b>[" . $username. "]</b><br> ";
    }
    else
    {
        echo "Not Found";
    }
}
else
{
    // prepared query failed
    trigger_error('Unable query users table: ' . mysqli_error($link));
}

 

thanks but your code ignore the first row

[mac_gyver]

you must be specific, by showing what data you expected, what data you got, and what the actual code is that you are using.

[aHMAD_SQaLli]

ok, I tried using another query

SELECT users.username, topics.topic_body FROM `users` LEFT JOIN topics ON topics.topic_author = users.id WHERE topics.id=? order by topic_edit_time ASC

I have 5 row in it but it display just 4 and ignoring the first

 

however the first code works great

 

EDIT :::

here is the code

if ($stmt = mysqli_prepare($link, "SELECT users.username, topics.topic_body FROM `users` LEFT JOIN topics ON topics.topic_author = users.id WHERE topics.id=? order by topic_edit_time ASC"))
{
    mysqli_stmt_bind_param($stmt, "s", $tid);
    mysqli_stmt_execute($stmt);
    /* the columns returned in the query will be bound to these variables when calling mysqli_stmt_fetch */
    mysqli_stmt_bind_result($stmt, $username, $replay);
    /* mysqli_stmt_fetch returns FALSE if no rows where returned */
    if(mysqli_stmt_fetch($stmt))
    {
		while (mysqli_stmt_fetch($stmt)) {
			 echo 'author: <b>[' . $username . ']</b> replay: <b>[' . $replay. ']</b> ';
		}
       
    }
    else
    {
        echo "Not Found";
    }
}
else
{
    /* prepared query failed */
    trigger_error('Unable query users table: ' . mysqli_error($link));
}

Edited August 9, 2015 by aHMAD_SQaLli

[mac_gyver]

ok, I tried using another query

 

 

the methods we have shown in this thread are for the type of query you posted at the start of this thread, that is testing if there is a user row matching the id and to fetch that row if there is, i.e. a query that will match at most one row.

 

if you are running a query that can return any number of matching rows, you need to write the code differently. you would need to test the number of rows, then loop over the result set if there are rows.

 

as an alternative, since you should be separating your database dependent code from any html markup, would be to simply fetch all the rows, even if there are none, into a php array. then use that array any place you need to access the data. you can find how many rows the query matched by using count() on the array. you can loop over the array using a foreach loop to process the rows.