num_rows() always returns 0 when using prepared statements

Question

I've tried looking at every one else's stack overflow with exact error in title, but I still can't figure this out.

I'm using http://andrew-greig.net/portfolio/better_mysqli.htm for mysqli extend because it does make binding items as arrays much easier. I been trying to move over from native mysql to mysqli and I've always had this same issue with tring to find out if there is a result or not.

Here is my current code

$searchSQL = $mysqli->select("SELECT * FROM coreUsers WHERE Username = ? AND Password = ?", $row, array($username, $password));
var_dump($searchSQL);

while($searchSQL->fetch())
    {
        
        echo "<br/>---FOUND Valid LOGIN!!</br>";

        exit;
    }

The output I get is..

    object(mysqli_stmt)#2 (10) { ["affected_rows"]=> int(-1) ["insert_id"]=> 
int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(19) 
["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } 
["sqlstate"]=> string(5) "00000" ["id"]=> int(1) } 
    ---FOUND Valid LOGIN!!

so it found the record, if I output the record information it does echo out Username and Password just fine from the row response, but num_rows is always 0. For this I can never get

$rowCount = $searchSQL->num_rows();

to work when trying to see if there are actual results before doing the while loop.

----EDIT----

I have no idea why this is marked as a duplicate. The post added refers to "boolean" and that isn't my error. Mine is object given.

if ($searchSQL === false) {
echo "invalid";
} else {
echo "valid";
}

this code doesn't work (from the post you marked as duplicate) because my response always will return true if you actually would look at the code I'm using from the extend.

fyi: `num_rows` is a property, and if you're worried with mysqli not having that execute array style, why not switch to PDO, no more library and other stuff — Kevin, Aug 31 '16 at 23:31
not sure what you mean. I'm just wanting to be able if a result was actually returned and there are rows available before attempting a while loop. Because the only other way is to set a counter before the while loop and see if it actually changes before determining if it actually had rows which is cheesy to me. — eqiz, Sep 01 '16 at 00:12
@Ghost also I don't want to use PDO because in bigger projects that has a ton of users PDO is actually 6.7% slower on prepared statements then mysqli. It doesn't make sense for me to switch over to PDO just over 1 thing i'm overlooking — eqiz, Sep 01 '16 at 00:23
take note that prepared statements has their own num rows, the usual is, you get the num rows from the connection, prepared statement has its own, http://php.net/manual/en/mysqli-stmt.num-rows.php, next time take your time creating a good question, your title is just so bad that its easy to mark it as duplicate, your question title doesn't give justice to what you're really trying to explain. anyways, good luck — Kevin, Sep 01 '16 at 00:32
@Ghost thank you for pointing that out. I was missing 1 line of code. I had to post in original post since this is still locked as duplicate. — eqiz, Sep 01 '16 at 01:17
reopened the question, you can add that edit to an answer, you can self answer — Kevin, Sep 01 '16 at 01:19
Maybe consider using the result of the query to determine if you have a match instead of the number of rows, for example ~ http://stackoverflow.com/a/27350331/283366 — Phil, Sep 01 '16 at 01:26
@Fred-ii- the title is just misleading, the problem is about `num_rows` not working for mysqli prepared statements, i haven't checked the complete answers inside the dup question but i think it still doesn't have the entry wherein `num_rows` doesn't work after prepared statement execution. the question content is entirely different now from the time it was dup closed. its resolved by the OP anyways, nothing wrong with what you did earlier, would have done the same thing, the OP should have created a clear question, it just got cleared after a series of comments — Kevin, Sep 01 '16 at 01:40
@Ghost Then the OP should probably delete the question, since there is too much confusion around it and its certainty. It (the question) will probably not serve anyone here, and having people looking through external resources (which may not exist at a later date) to look at code we don't even know matches what they're really using. — Funk Forty Niner, Sep 01 '16 at 01:44
@Fred-ii- You do realize that you are claiming the 2 linked versions above clearly shows the proper use of num_rows, however because I'm using outside code neither one of those even comes close to the correct answer which I have listed in the above post. I had to store the result before accessing the parameter its that simple and neither one of the 2 you linked above (sep 1 at 1:31) even comes close to that answer. I tried the responses you link and they didn't fix my issue as I had mentioned in the original post wanting to know what the problem was and not how to completely re-write code. — eqiz, Sep 08 '16 at 05:26

score 0 · Accepted Answer · answered Sep 08 '16 at 12:58

0

As mentioned by @Ghost below the issue I was missing was 1 line of code.

 $stmt->store_result();
 $rowCount = $stmt->num_rows;

This has to be called BEFORE I can access num_rows() on a prepared statement.

answered Sep 08 '16 at 12:58

eqiz

1,521
5
29
51

2

By the way, do yourself a favor: consider to use PDO instead of that monster you are using at the moment. – Your Common Sense Sep 08 '16 at 13:05

num_rows() always returns 0 when using prepared statements

1 Answers1