Jump to content
funbinod

how can i make next query inside a while loop of a prepared statement?

Recommended Posts

hello all!

i was trying to run another query inside a while loop of a prepared statement.

	$qs = $_POST['queryString']."%";
	$ledq = $mysqli->prepare("SELECT name, fullid aid, ob FROM account WHERE cid='$cid' AND name LIKE ? AND ac_id IN ('$q') ORDER BY name LIMIT 20");
	if (!$ledq) {
		echo "Error: " . $mysqli->error;
	} else {
		$ledq->bind_param('s', $qs);
		$ledq->execute();
		$ledq->bind_result($name, $aid, $ob);
		$ledq->store_result();
		while ($ledq->fetch()) {
			$balq = $mysqli->query("SELECT SUM(debit) debit, SUM(credit) credit FROM ..................");
			...............
			...............
			...............
		}
		$ledq->free_result();
		$mysqli->close();
	}

when i use free result outside the while loop it gives out of sync error. if i use free result inside the while loop and before the next query, it returns only one row.

i also tried more_result before executing the next query. but it only worked while testing locally. when i upload it to my server and try, it gives nothing.

please someone guide me what is the best solution!

 

thank you in advance..

Share this post


Link to post
Share on other sites

https://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why

see first answer

EDIT:

suggest you use a sub query or simply use two different queries 

Edited by niche

Share this post


Link to post
Share on other sites

Does your second query make use of data from the first query?

Share this post


Link to post
Share on other sites
/////////// this is a part of the second query /////////////
			SELECT SUM(debit) debit, SUM(credit) credit FROM (
				SELECT NULL ob, SUM(debit) debit, SUM(credit) credit FROM journal WHERE aid='$aid' AND cid='$cid' AND ay='$ay'
				UNION
				SELECT NULL ob, SUM(fee) debit, NULL credit FROM invoice WHERE stuid='$aid' AND cid='$cid' AND ay='$ay'
				UNION
				.............
				.............
				.............
			) as balance

/////////// in this second query i use $aid from the previous one...
2 hours ago, Ingolme said:

Does your second query make use of data from the first query?

yes.the second query searches data from multiple tables and calculate them according to the data derived from first prepared statement.

Edited by funbinod
wanted to add some sample code..

Share this post


Link to post
Share on other sites

The fact that it's returning one one row may have to do with how you're using the data. The rest of the code inside the loop is important, you may be overwriting variables or data.

If you declare the second query as a prepared statement outside of the loop your code will be more efficient. You only need to declare it once and you can use it many times.

$balq = $mysqli->prepare('SELECT SUM(debit) debit, SUM(credit) credit FROM x WHERE a = ? AND b = ?');
$balq->bind_param('ss', $field1, $field2);
while ($ledq->fetch()) {
  $field1 = 'Data from first query';
  $field2 = 'Data from first query';
  $balq->execute();
  $balq->bind_result($debit, $credit);
  // Your code here
  echo $debit . ' ' . $credit;
  //////
  $balq->free_result();
}
$ledq->free_result();
$mysqli->close();

You omitted a whole lot of information from your code, so a most of this is just examples. If this is returning you just one row then either you're using the loop wrong or your queries really only have one row to return.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×