skaterdav85 Posted August 17, 2010 Share Posted August 17, 2010 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;} Link to comment Share on other sites More sharing options...
Ingolme Posted August 17, 2010 Share Posted August 17, 2010 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 Link to comment Share on other sites More sharing options...
skaterdav85 Posted August 17, 2010 Author Share Posted August 17, 2010 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(); Link to comment Share on other sites More sharing options...
Ingolme Posted August 17, 2010 Share Posted August 17, 2010 Actually, it's the STMT part that has it: $stmt->num_rows http://www.php.net/manual/en/mysqli-stmt.num-rows.php Link to comment Share on other sites More sharing options...
skaterdav85 Posted August 18, 2010 Author Share Posted August 18, 2010 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? Link to comment Share on other sites More sharing options...
boen_robot Posted December 14, 2010 Share Posted December 14, 2010 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. Link to comment Share on other sites More sharing options...
skaterdav85 Posted December 14, 2010 Author Share Posted December 14, 2010 thanks for sharing boen. its good to know what is happening behind the scenes. how did you figure this out? Link to comment Share on other sites More sharing options...
boen_robot Posted December 14, 2010 Share Posted December 14, 2010 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). Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.