mysqli_multi_query not getting executed multiple times inside a while loop

Question

In my php code I use a while loop as shown below.

<?php
$connection=mysqli_connect("localhost","root","","entries");
$query1="select * from jobs";
$query1exe=mysqli_query($connection,$query1);
$loopcount=0;
while($query1collector=mysqli_fetch_array($query1exe,MYSQLI_ASSOC)) {
    $query2="insert into learning set name='new_1';
    insert into learning set name='new_2';";
    mysqli_multi_query($connection,$query2);
    $loopcount++;
}
echo($loopcount);
?>

In this code I get the output as 104($loopcount) which means the loop was executed 104 times. But in my table named learning I have only two new entries new_1 and new_2. I expected 208 new entries (new_1 104 times and new_2 104 times). Why didn't I get the result expected.

I am using PHP Version 5.5.11

P.S I want to use the mysqli_multi_query itself as I need to execute a few queries simultaneously.

This is how my learning table look like when I executed my code three times

--------------------------------------------------------
slno     | name       | city       | Phone
--------------------------------------------------------
1        | sandeep    | NULL       | NULL
-------------------------------------------------------
2        | new_1      | NULL       | NULL
-------------------------------------------------------
3        | new_2      | NULL       | NULL
-------------------------------------------------------
4        | new_1      | NULL       | NULL
-------------------------------------------------------
5        | new_2      | NULL       | NULL
------------------------------------------------------
6        | new_1      | NULL       | NULL
------------------------------------------------------
7        | new_2      | NULL       | NULL
-------------------------------------------------------

You don't need a loop, you can do this all in one SQL query. INSERT INTO learning (name) SELECT 'new_1' FROM jobs UNION ALL SELECT 'new_2' FROM jobs — Barmar
– Barmar, Commented Oct 22, 2015 at 17:27
@Barmar no. There isn't. I can see the new 'new_1' and 'new_2' entry each time I execute my code. — Sandeep Thedarkprince C
– Sandeep Thedarkprince C, Commented Oct 22, 2015 at 17:28
@Barmar 'new_1' is not a column name. It is just a text input — Sandeep Thedarkprince C
– Sandeep Thedarkprince C, Commented Oct 22, 2015 at 17:30

fabpico · Accepted Answer · 2015-10-22 17:41:13Z

2

I watched on the official documentation here and found a user comment

if you mix $mysqli->multi_query and $mysqli->query, the latter(s) won't be executed!

Use only mysqli_query, split your multi-queries into this:

mysqli_query($connection,"insert into learning set name='new_1'");
mysqli_query($connection,"insert into learning set name='new_2'");

answered Oct 22, 2015 at 17:41

fabpico

2,9864 gold badges32 silver badges50 bronze badges

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

5 Comments

bksi Over a year ago

The insert statement won't run even in phpmyadmin

Sandeep Thedarkprince C Over a year ago

I have a few queries that are used to extract the value of some variables and I execute my main query based on the values of those variables. Thats the reason why I am interested in multi_query

fabpico Over a year ago

@bksi Possible. I didn't tested it. But i explained what the problem was and offered an workaround.

bksi Over a year ago

Hm how you can know if the query works if the query itself is wrong?

fabpico Over a year ago

@bksi Because the questioner said that the inserting itself works. Citate: But in my table named learning I have only two new entries new_1 and new_2.

Barmar · Accepted Answer · 2015-10-22 19:55:35Z

2

I think you may need to fetch the results of both queries before you can start another multi-query.

while($query1collector=mysqli_fetch_array($query1exe,MYSQLI_ASSOC)) {
    $query2="insert into learning set name='new_1';
    insert into learning set name='new_2';";
    mysqli_multi_query($connection,$query2) or die('insert new_1 error: ' . mysqli_error($connection));
    mysqli_next_result($connection) or die('insert new_2 error: ' . mysqli_error($connection));
    $loopcount++;
}

answered Oct 22, 2015 at 19:55

Barmar

789k57 gold badges555 silver badges669 bronze badges

5 Comments

Sandeep Thedarkprince C Over a year ago

I got this error insert new_1 error: Commands out of sync; you can't run this command now

Barmar Over a year ago

I must be psychic. I posted a comment yesterday saying you were probably getting that error.

Sandeep Thedarkprince C Over a year ago

The query was executed in the first loop and I have two entries as I have said and in the second loop I got this error

Sandeep Thedarkprince C Over a year ago

You were right. isn't there a solution to this problem?

Barmar Over a year ago

I never use multi-query, so I'm not really sure what the right fix is.

Collectives™ on Stack Overflow

mysqli_multi_query not getting executed multiple times inside a while loop

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related