PHP/MySQL: simultaneous queries inside WHILE loop?

Question

I'm trying to execute a query inside a WHILE loop running on mysqli_fetch_array. The query gives the following error:

Error: Commands out of sync; you can't run this command now

I found out that you can't have simultaneous queries. Here's my code

$query "SELECT * FROM table";
$exec = mysqli_query($con, $query);

while($loop = mysqli_fetch_array($exec)){

$data = $loop['data'];

$sim = "SELECT * FROM table2 WHERE col1 = '$data'";
$execsim = mysqli_query($con, $sim);
$getsim = mysqli_fetch_array($execsim);
$somedata = $getsim['somedata'];

//insert $somedata and $data into table3

}

After some research, I see that store_result() could be useful here. Excuse my ignorance as this is new to me. There aren't any examples/solutions that I can find that use this with a WHILE loop scenario (does that make a difference?) - most are in object oriented style too.

What is the most effective way around this problem?

There's a reason I need to use the select in the loop, I will update — nulled
– nulled, Commented Jun 16, 2016 at 22:30
Rather than executing another select query based on the result of the first one, you can JOIN the two tables together with one query. — Don't Panic
– Don't Panic, Commented Jun 16, 2016 at 22:32
I'd be curious to that; I'd think SELECT * FROM table2 WHERE col1 IN (SELECT data FROM table) would get all the entries from table2 where col1 matches data. At that point, you'd be able to insert table2.somedata and table2.col1 into table3. — Chris Forrence
– Chris Forrence, Commented Jun 16, 2016 at 22:33
Could I use it like so: SELECT * FROM table2 WHERE col1 IN (SELECT * FROM table WHERE col2 = 'something') then run the WHILE loop from that query? — nulled
– nulled, Commented Jun 16, 2016 at 22:38
This pattern appears to be vulnerable to SQL Injection. Appears to be no guarantee that $data won't contain SQL text. — spencer7593
– spencer7593, Commented Jun 16, 2016 at 22:40

spencer7593 · Accepted Answer · 2016-06-16 22:39:27Z

3

The most effective workaround for this is to avoid processing row-by-agonizing-row (RBAR), and just perform the operation on the set, and be done with it.

INSERT INTO table3 (somedata, col1)
SELECT t2.somedata
     , t2.col1
  FROM table2 t2
  JOIN table1 t1
    ON t1.data = t2.col1
 ORDER BY t2.somedata, t2.col1

If this doesn't satisfy the requirements, then some requirements have been omitted from the specification, and we can only guess what you are actually trying to achieve.

As another option, avoid the two queries, and just use a join operation, as shown in the INSERT ... SELECT statement above. (Just start with the SELECT).

No indication given in the specification as to the reason for an excruciating loop within a loop, to process rows individually.

answered Jun 16, 2016 at 22:39

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nulled Over a year ago

I guess I wasn't specific enough in my explanation, as there was slightly more to it. Using the query above and closing/opening the connection before running another query inside the WHILE loop did the trick. Thanks

Collectives™ on Stack Overflow

PHP/MySQL: simultaneous queries inside WHILE loop?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related