Run multiple PHP MySQL queries

Question

I have 9 MySQL queries to execute in PHP, and I am trying to execute them like this:

 if(mysqli_query($con, $q1)){
    ?><p>Query 1 complete</p><?
 }
 if(mysqli_query($con, $q2)){
    ?><p>Query 2 complete</p><?
 }
 if(mysqli_query($con, $q3)){
    ?><p>Query 3 complete</p><?
 }
 if(mysqli_query($con, $q4)){
    ?><p>Query 4 complete</p><?
 }
 if(mysqli_query($con, $q5)){
    ?><p>Query 5 complete</p><?
 }
 if(mysqli_query($con, $q6)){
    ?><p>Query 6 complete</p><?
 }
 if(mysqli_query($con, $q7)){
    ?><p>Query 7 complete</p><?
 }
 if(mysqli_query($con, $q8)){
    ?><p>Query 8 complete</p><?
 }
 if(mysqli_query($con, $q9)){
    ?><p>Query 9 complete</p><?
 }

But for some reason, only the first one is being executed, and showing up in the DB. The rest are not completing. Is there something I am missing about executing multiple queries, or is there a syntax mistake I am not seeing?

And here is $q2, because it doesn't seem to want to get past that:

$q2 = "INSERT INTO outboundapps 
        (appid, 
         fk_outboundappkey, 
         name, 
         browser, 
         fk_urls, 
         fk_routes, 
         virtualplatform, 
         autoanswer, 
         fk_get) 
VALUES   ($last + 2, 
         $last + 2, 
         $oname, 
         $otype, 
         $last + 2, 
         $last + 2, 
         $ovirtualplatform, 
         1, 
         $last + 2)";

does your code before the first if start with <?php ? <? shortcut does not work by default. You need to configure it. — karthikr
– karthikr, Commented Oct 16, 2013 at 15:39
Are all the conditions met that will potentially show all queries? — Funk Forty Niner
– Funk Forty Niner, Commented Oct 16, 2013 at 15:40

Bill Karwin · Accepted Answer · 2013-10-16 23:38:34Z

Unknown column 'test' in 'field list'

This can happen if you fail to delimit string literals in single-quotes.

Take these two statements for example:

1. INSERT INTO mytable (col1) VALUES (test);

2. INSERT INTO mytable (col1) VALUES ('test');

The difference is that the test in query 1 is assumed to be a column identifier, whereas the test in query 2 is a string literal.

I know it seems to make no sense to use a column identifier in the VALUES clause for a new row -- how could that column have any value, if the row hasn't been inserted yet? In fact if you were to name columns that exist in this table, the INSERT works, but the column values are NULL for a new row.

INSERT INTO mytable (col1) VALUES (col1); -- no error, but inserts only a NULL

In your example query you have:

$q2 = "INSERT INTO outboundapps 
        (appid, 
         fk_outboundappkey, 
         name, 
         browser, 
         fk_urls, 
         fk_routes, 
         virtualplatform, 
         autoanswer, 
         fk_get) 
VALUES   ($last + 2, 
         $last + 2, 
         $oname, 
         $otype, 
         $last + 2, 
         $last + 2, 
         $ovirtualplatform, 
         1, 
         $last + 2)";

To help debug, you could echo $q2 and see what the SQL really looks like before you execute it. I expect it'll be something like this:

INSERT INTO outboundapps 
        (appid, 
         fk_outboundappkey, 
         name, 
         browser, 
         fk_urls, 
         fk_routes, 
         virtualplatform, 
         autoanswer, 
         fk_get) 
VALUES   (125, 
         125, 
         test, 
         Firefox, 
         125, 
         125, 
         test, 
         1, 
         125)

See the test without quotes in that query? That's why it's complaining that you named an unknown column test.

tip: it's better to use prepared statements when you want to pass application variables to a query, for the reason that you don't have to worry about quotes around the parameters:

$q2 = "INSERT INTO outboundapps 
        (appid, 
         fk_outboundappkey, 
         name, 
         browser, 
         fk_urls, 
         fk_routes, 
         virtualplatform, 
         autoanswer, 
         fk_get) 
VALUES   (?, ?, ?, ?, ?, ?, ?, 1, ?)";

$stmt = mysqli_prepare($q2) or trigger_error(mysqli_error(), E_USER_ERROR);
$stmt->bind_param("iissiisi", $last2, $last2, $oname, $otype, $last2, 
    $last2, $ovirtualplatform, $last2);
$stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);

This is exactly the kind of answer I was looking for, thank you so much for the INCREDIBLY constructive and helpful response!

jmallord · Accepted Answer · 2014-07-18 08:37:56Z

1

NEWBIE with PHP...I had the same issue, trying to run consecutive queries, simple ones, just basic select and update; what I did was close the database and reopen between each call: $dbh->close();

$dbh = db::GetInstance(); this was in a tutorial for SINGLETON db, seems to work ok

answered Jul 18, 2014 at 8:37

jmallord

111 bronze badge

Comments

Your Common Sense · Accepted Answer · 2013-10-16 15:59:36Z

0

Is there something I am missing about executing multiple queries?

NO.

there is apparently nothing special in running multiple queries in mysqli in general.
As a matter of fact, almost every PHP script does such multiple query execution, this way or another.

is there a syntax mistake I am not seeing?

Ask your computer, not humans. A program intended to be run by computer - so, the only way to tell if syntax is wrong or there is another issue is to run a program.

only the first one is being executed

If some query didn't run, there was an error.
Mysqli doesn't report mysql errors by default, you have to set it explicitly:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

of course, general PHP error reporting have to be turned on as well.

Without error message it is no use to guess.

answered Oct 16, 2013 at 15:59

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

5 Comments

Wingdom Over a year ago

The error mysqli_error() is giving me is Unknown column 'test' in 'field list'

Wingdom Over a year ago

But nowhere am I telling it to add to column 'test'. I added the query to my initial post, and you can see which columns I am trying to add to.

Wingdom Over a year ago

Check the Insert Into statement above. Nowhere does it say Insert Into (test), and none of the values in Insert Into are even variables, so its not like I got a variable declaration wrong somewhere.

Wingdom Over a year ago

You are the one going around being rude and ignoring crucial pieces of information in the problem. You are looking at one piece of the picture, and assuming it is the solution to the whole thing. NOWHERE in the Insert statement am I telling it to insert into 'test'. If you won't even look at the information I am providing, you are in no place to offer help. If you don't have anything constructive to say to a polite ask for help, just don't say anything at all.

Wingdom Over a year ago

I was trying to have a conversation to find the solution, but as soon as I checked your first suggestion, and it wasn't the answer, you just started trolling instead of saying you don't know, working through it with me, or just letting it go. Now I would appreciate it if you don't have anything else constructive to say, would you please stop down voting my question, and trolling in my comments so I can get this problem resolved.

Collectives™ on Stack Overflow

Run multiple PHP MySQL queries

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related