MYSQLI Prepared Statements

[noelchurchill]

Hi. Any help would be great! Thanks in advance.

I have a prepared statement to fetch several rows of data from mysql. For each row fetched I'd like to run another prepared statement to fetch more data, based on the rows returned in the first prepared statement. I'm sure there is a better way of doing this.

I get the error: Warning: mysqli::prepare() [function.mysqli-prepare]: All data must be fetched before a new statement prepare takes place in...

Here is my code:

$puid = $_GET['puid'];
require('connection.inc.php');
$conn = dbConnect(); //connection to db funtion in connection.inc.php
$sql = "SELECT pictureid, comment FROM pictures WHERE userid = ?";
$result = $conn->prepare($sql);
$result->bind_param("i", $puid);
$result->execute();
$result->bind_result($pictureid, $comment);
while($result->fetch()) {
	$sql = "SELECT (SELECT count(*) FROM choices WHERE chosen = ?) AS chosenCount, (SELECT count(*) FROM choices WHERE chosen = ? OR notchosen = ?) AS chosenTotal";
	$result2 = $conn->prepare($sql);
	$result2->bind_param("iii", $pictureid, $pictureid, $pictureid);
	$result2->execute;
}

[visualAd]

You need to have read ALL the results before executing another query.Have a look at the store_result function; I think this transfers the result set to memory.

Also, I suggest you think about other ways of doing this; such as retrieving the results and then executing the queries or executing a join query.

[noelchurchill]

I'll look into the store_result function. That looks like a good option. How would I loop through the stored result to execute another statement based on the first statements results?

I've thought about a join statement but I can't figure that out because there are many rows of pictures, and then many rows of choices for each picture. Maybe there is an easy way of doing that but I'm not sure.

[penagate]

SELECT
  p.pictureid,
  p.comment,
  (
    SELECT
      COUNT(c1.*)
    FROM choices c1
    WHERE c1.chosen=p.pictureid
  ) AS chosenCount,
  (
    SELECT
      COUNT(c2.*)
    FROM choices c2
    WHERE c2.chosen=p.pictureid OR c2.notchosen=p.pictureid
  ) AS chosenTotal
FROM pictures p

I think this should do more or less what you need. It might not be quite correct.

Also, have you considered storing the counts in the picture table? This might duplicate some data, but would improve efficiency tremendously.


Edit: possible improvement:

SELECT
  p.pictureid,
  p.comment,
  (
    SELECT
      COUNT(c1.*)
    FROM choices c1
    WHERE c1.chosen=p.pictureid
  ) AS chosenCount,
  (
    SELECT
      COUNT(c2.*)
    FROM choices c2
    WHERE c2.notchosen=p.pictureid
  ) + chosenCount AS chosenTotal
FROM pictures p

Edit: another possible improvement would be to only execute the total subquery, and then count the number that are 'chosen' as you enumerate the results in your script.

[noelchurchill]

penagate,

That looks like a good solution. I'll try implementing it when I get home from work later today.

I actually was thinking about storing the counts in the picture table this morning as I was getting ready. But like you said, I was trying to avoid the duplicate data.

Can you expand a little more on your final edit about executing the total subquery and then counting the number that are chosen as I enumerate the results? That's still a little over my head.

Thanks!

[penagate]

Actually, now I think about it again, that last suggestion won't work.

[noelchurchill]

I got the first query to work with a little modification. Here's what I did

$sql = "SELECT
		pictureid, 
		comment, 
		(SELECT COUNT(*) FROM choices WHERE chosen=pictureid) AS chosenCount, 
		(SELECT COUNT(*) FROM choices WHERE chosen=pictureid OR notchosen=pictureid) AS chosenTotal
	FROM
		pictures
	WHERE
		userid = ?";

Now that I've got that solved then I have to decide if I should just store chosen and notchosen counts in the pictures table. Like you said, it would result in duplicate data, but much more efficient. I guess it just depends on how much traffic I get.

Thanks for the help.

[penagate]

Good work.


Caching columns is pretty common practice. However, it does mean more work to maintain data integrity.

If you're using MySQL 5, you can use table triggers (basically a mini stored procedure that can be hooked on to various events like insert or delete) to automatically update the cache columns, thus minimising the amount of code you have to write and lessening the chance of getting the counts out of sync.