commands are out of sync.. you cant run this command now in while loop

Question

 `$sql = "call geodist(".$_SESSION['This'].",500)";` //get the ids near you

$result=mysqli_query($GLOBALS['link'],$sql) or die(mysqli_error($GLOBALS['link']));

while($row=mysqli_fetch_assoc($result)) // insert them into nerby table
{

$sql = "INSERT INTO `nearby`(`userid`, `strangerid`) VALUES (".$_SESSION['This'].",".$row['id'].")";
mysqli_close($GLOBALS['link']);                                                      
mysqli_query($GLOBALS['link'],$sql) 
    or die("akash".mysqli_error($GLOBALS['link'])); 
}

when i remove the '//*' statements i get out of sync error.... keeping those two lines help me run my code but code becomes slow since for every loop iteration the database connection is closed and reopened. pls tel me an alternate.... i will really be grateful to you

You could store the result of your procedure in a temporary table and use this temporary table for a single `INSERT` statement. If you do so, there's no need for loops at all. The accepted answer of http://stackoverflow.com/questions/17944871/how-to-use-table-output-from-stored-mysql-procedure shows how it could be done. — VMai, May 12 '14 at 10:26
You can answer your own question and show how you solved this problem with a different approach. — VMai, May 12 '14 at 12:35
I've read in the [FAQ](http://stackoverflow.com/help/self-answer) that as a new user you've got to wait for a while. — VMai, May 12 '14 at 12:52

score 0 · Answer 1 · answered May 12 '14 at 10:45

What happens is you have data in the buffer between mysql and PHP when you try to fill it with new data (the query inside the loop).
What you need to do is

either fetch all data from the buffer before you use it for other query
See how u set this mysqli connection to fetch data unbuffered.

Read more here http://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.concepts.buffering.html

score 0 · Answer 2 · answered May 13 '14 at 14:13

DROP TEMPORARY TABLE IF EXISTS near;
CREATE TEMPORARY TABLE near

i added these 2 lines in my geodist() procedure. it creates a temporary table named "near" and stores the result set in it. now this table could be used as follows

$sql = "call geodist(".$_SESSION['This'].",".$GLOBALS['RADIUS'].")";            // get the ids near you
         $result=mysqli_query($GLOBALS['link'],$sql) or die(mysqli_error($GLOBALS['link']));


 $sql = "SELECT m.`id`\n"    
        . " FROM `members` m , `already_assigned` a\n"    
        . " WHERE a.`id`=m.`id` and m.`id` <> ".$_SESSION['This']."\n"    
        . " and m.`gender`=".$_SESSION['wants']." and m.`interested_in`=".$_SESSION['gender']." and a.`status`='free'\n"   
        . " and m.`id` IN (SELECT * from `near`)\n"  /*see this line*/    
        . " ORDER BY RAND( ) \n"   
        . " LIMIT 1";

you can see that now i have removed the while loop... now its not required

score -1 · Answer 3 · answered May 12 '14 at 10:17

-1

How 'bout not closing the database connection until you're actually done with it?

You should only ever establish ONE connection to any given database server (so basically, one connection in your script unless you're working with a complex multi-DB-server setup)

answered May 12 '14 at 10:17

Niet the Dark Absol

320,036
81
464
592

2

if i dont close the connection then i am getting "OUT OF SYNC ERROR...... CANT RUN THIS COMMAND NOW" error – Akash Chauhan May 12 '14 at 10:20
@user3627904 Closing connection isn't usually necessary, as **non-persistent open links** are automatically closed at the end of the script's execution – Shankar Narayana Damodaran May 12 '14 at 10:34
i think u not getting my point.... keeping those two lines help my code run.. if removed, shows error – Akash Chauhan May 12 '14 at 10:41

commands are out of sync.. you cant run this command now in while loop

3 Answers3