getting the number of results selected from the mysqli class

[skaterdav85]

How do I get the number of results returned from a prepared select statement via mysqli's object oriented style? This is my method in a class of mine, and whenever i echo out the 'affectedRows' property, it always says -1 and I have no idea why

public function checkIfLiked(){		$mysqli = new mysqli($this->host, $this->username, $this->password, $this->db);		$sql = "SELECT postID FROM likes";		$stmt = $mysqli->prepare($sql);		$stmt->execute();		$this->affectedRows = $mysqli->affected_rows;}

[Ingolme]

I found this:

This property contains a -1 value in situations when no errors have occurred. When preparing a SELECT statement (or any other statement which doesn't affect rows), the property will be -1. The property will also be reset to -1 if an INSERT statement is re-prepared as a SELECT statement.

You probably want to try the $mysqli->num_rows property

[skaterdav85]

Well according to the documentation, there is no num_rows property of the mysqli class. I tried it and I got an error saying:Notice: Undefined property: mysqli::$num_rowsLooking through the documentation, I don't see anything that lets you retrieve the number of results selected. I guess I could always loop through my results and increment a counter variable, but you'd think there is another way to do this esp since the procedural style of using the mysql functions offers mysql_num_rows();

[Ingolme]

Actually, it's the STMT part that has it:

$stmt->num_rows

http://www.php.net/manual/en/mysqli-stmt.num-rows.php

[skaterdav85]

Ok that is correct. But what I found out was that you must store the result before calling the num_rows property. If you don't, it will return 0. However, to actually fetch the result set and output its contents, you don't have to store the result. Anyone have any insight as to why and how this makes sense?

[boen_robot]

The topic is now a little old, but since I recently had a similar encounter, and the question is still not answered...When you store the result, PHP fetches the full result set in memory, even if you don't actually traverse over it. That's how PHP knows in advance how many rows there are and can therefore tell you that value. When you're using the result instead of storing it, PHP fetches every row from the server one by one, giving you a truly low memory consumption even for enormous datasets (at the price of more CPU consumption, naturally). For some reason (I'm guessing protocol simplicity), MySQL doesn't tell MySQL clients in advance how many rows they will be downloading, hence PHP can't tell you.

[skaterdav85]

thanks for sharing boen. its good to know what is happening behind the scenes. how did you figure this out?

[boen_robot]

Trial&error, similarly to yourself, plus detailed read at the notes of the mysqli::use_result() and mysqli::store_result(). And for me to need reading those I started after doing mysqli::multi_query() as an optimization attempt (I needed to do 3 or 4 different selects on a single page)... for all the CPU time I saved with that, I more than made up for by the memory consumption of storing each result set (as I too needed to know the number of rows in each select).