funbinod Posted January 22, 2018 Share Posted January 22, 2018 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.. Link to comment Share on other sites More sharing options...
niche Posted January 22, 2018 Share Posted January 22, 2018 (edited) 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 January 22, 2018 by niche Link to comment Share on other sites More sharing options...
Ingolme Posted January 22, 2018 Share Posted January 22, 2018 Does your second query make use of data from the first query? Link to comment Share on other sites More sharing options...
funbinod Posted January 22, 2018 Author Share Posted January 22, 2018 (edited) /////////// 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 January 22, 2018 by funbinod wanted to add some sample code.. Link to comment Share on other sites More sharing options...
Ingolme Posted January 22, 2018 Share Posted January 22, 2018 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now