separate statements in single MYSQL query

Question

How do I separate statements in a single MYSQL query?

Is it a semicolon?

I don't think it matters but I am writing the statements to be invoked by PHP functions

Edit: the problem is that I didn't seem to understand the UPDATE syntax, that you can execute multiple fields with one UPDATE statement

What kind of statements do you mean? With semicolons, you seperate whole queries. — Jacob, Jul 14 '11 at 17:42
and by statement do you mean a prepared statement or just a standard query execution? — prodigitalson, Jul 14 '11 at 17:43
i guess I misunderstand the definition, is query a single statement by definition? — Timtam, Jul 14 '11 at 17:44
Most PHP MySQL implementations can only perform 1 query at a time. But yes, a semicolon would do for the 1 that does support it. Otherwise, you could look into stored procedures in mysql itself. — Wrikken, Jul 14 '11 at 17:44
that contains two update statements, would that be considered a single query or two queries, if so, what is the difference between a query and a statement, sorry if this is too basic — Timtam, Jul 14 '11 at 17:45
so wrikken you are saying that I have to call a PHP function twice to update 2 fields in my table?? that seems like a poor way to implement the function when you can just pass two statements in a single string to the PHP function — Timtam, Jul 14 '11 at 17:47
No, that would be 1 query, not multiple statements, expressions separated by a comma. At least, depends on what rows you're updating. But if that's your question I'd refer you to the basics [UPDATE syntax](http://dev.mysql.com/doc/refman/5.0/en/update.html) — Wrikken, Jul 14 '11 at 17:47
see this post once http://stackoverflow.com/questions/5580854/how-to-run-multiple-update-queries-in-single-statement-in-php-and-mysql — Rahul, Jul 14 '11 at 17:48
@Chris, Yep yep you posted the same thing what the other post says. — Rahul, Jul 14 '11 at 17:51

Chris Baker · Accepted Answer · 2011-07-14T17:55:22.353

1

Using the mysqli_ functions (docs), you can pass multiple queries in the same string using mysqli_multi_query (docs)

Example from the docs:

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= 'UPDATE City SET name = "Boston" WHERE id = 5';

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>

edited Jul 14 '11 at 17:55

answered Jul 14 '11 at 17:49

Chris Baker

49,926
12
96
115

looks like I need to understand the UPDATE syntax, sorry for the misdirection thanks anyway – Timtam Jul 14 '11 at 17:52
The queries can be `SELECT`, `UPDATE`, `INSERT`, it does not matter. This example uses `SELECT` statements simply to illustrate the concept. I'll tweak the example to show an `UPDATE` if it would help... – Chris Baker Jul 14 '11 at 17:54
what i didn't understand was that, i can update 2 fields (or more) with 1 UPDATE query, which is kinda retarted of me – Timtam Jul 14 '11 at 17:57
This is generally not safe and mostly not enabled by default. – RobotRock Jul 14 '11 at 22:34

score 0 · Answer 2 · answered Jul 14 '11 at 17:43

0

If you're using one PHP call to get data from or send data to the database, it won't work. The MySQL PHP drivers support execution of a query string, not a script. If you want to execute multiple commands with one call, check out stored procedures.

http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

I think you're trying to do this?

<?php

$query  = "SELECT name, subject, message FROM contact;select name from contact;";
$result = mysql_query($query);

?>

It won't work because PHP doesn't know what query you are looking to execute. You can do the following though, but most likely you knew that:

<?php

$query  = "SELECT name, subject, message FROM contact";
$query2  = "SELECT name, message FROM contact;select name from contact;";
$result = mysql_query($query);
$result2 = mysql_query($query2);

?>

Hope this helps,

Jeffrey Kevin Pry

answered Jul 14 '11 at 17:43

Jeffrey Kevin Pry

3,266
3
35
67

I am using UPDATE not select. – Timtam Jul 14 '11 at 17:49
It's a query, but you can't have two in same string in PHP. Even when using a MySQL client like MySQL Workbench, when you type two commands separated by a ;, the program splits them up and sends two different queries. As Chris notes below however, using MySqli does have mysqli_multi_query. That may work for you, but I really would recommend you go the stored procedure route. It makes it easier to change your code because all you have to do is change it on the DB server and your website is automatically implementing it. – Jeffrey Kevin Pry Jul 14 '11 at 17:51
This would, however, be a really overly-simplistic use of stored procedures. I stop short of calling it "misuse", but I will say that this is not really the best example of what stored procedures are for and can do. – Chris Baker Jul 14 '11 at 17:52
Depending on what he is trying to build, stored procedure are almost always the better choice. They are faster and make it easier to prevent messy data transformations with multiple queries outside of the DB. Also he could wrap them into a transaction to make sure both update queries get updated and fail if they weren't. If PHP threw an error it could leave one record updated and the other not. It can be a big headache in the long run. – Jeffrey Kevin Pry Jul 14 '11 at 17:55

separate statements in single MYSQL query

2 Answers2