INSERT and SELECT in single query MySQL

Question

How can I do one single query with both an INSERT and SELECT in it and then read the results of selection? I want to insert and then select something, all must be done in one query...

I know it accept multiple commands in single query...But I want to read results of query and I cannot read results. I'm doing this:

$results=mysql_query("
INSERT INTO table1 (field1,field2) VALUES('aaa','bbbb') ON DUPLICATE KEY UPDATE `field1` = 'cccc', `field2`='dddddd'; 
SELECT field3 Form Table3 WHERE field3='eeeee';
",$connection);

while ($rows = mysql_fetch_array($results, MYSQL_NUM))  
echo $rows[0];

[Docs](http://php.net/mysql_query): _"mysql_query() sends a unique query (multiple queries are not supported)..."_ Instead, I think you're wanting a _transaction_. — Wiseguy, Mar 29 '12 at 14:11
From the performance perspective, you will not achieve anything with this. Just call mysql_query two times. Why "all must be done in one query..."? — Aleksandar Vucetic, Mar 29 '12 at 14:12
Why can't you simply do 2 queries? So you can even check result of insert? — kappa, Mar 29 '12 at 14:12
I want to select the same row I inserted in a single query. Is it possible — Toufiq, Feb 15 '22 at 10:04

Pierre-Olivier · Answer 1 · 2012-03-29T14:31:53.593

5

all must be done in one query...

Why do you need to do everything in one query ?

Like Wiseguy said, I think what you are looking for is called a transaction.

Also, It might be a good idea considering updating to PDO, which will give you a more complete toolset like transactions and query parameters.

Anyway, for answering your initial question, no it is not possible.

Update: Here is an example of a transaction in PDO.

try
{
    $pdo->beginTransaction();

    $pdo->query(' ... ');
    $pdo->query(' ... ');
    $pdo->query(' ... ');

    $pdo->commit();
}
catch(Exception $e)
{
    $pdo->rollback();
    die($e->getCode() . ': ' . $e->getMessage());
}

edited Mar 29 '12 at 14:31

answered Mar 29 '12 at 14:19

Pierre-Olivier

3,104
19
37

1

If you're going to use a try-catch (which I like), don't forget to set [PDO::ERRMODE_EXCEPTION](http://www.php.net/manual/en/pdo.setattribute.php) so that it will throw exceptions; I don't think it does by default. [This answer](http://stackoverflow.com/a/2708247/185544) has a nearly identical example with more info about that. – Wiseguy Mar 29 '12 at 14:41
You are right about PDO::ERRMODE_EXCEPTION, I did forget about that ! – Pierre-Olivier Mar 29 '12 at 14:45

score 3 · Answer 2 · answered Mar 29 '12 at 14:15

3

Not possible, and wouldnt recommend doing it either, as kappa points out, if you perform 2 seperate queries you'll be able to check for results etc. which is preferable.

answered Mar 29 '12 at 14:15

Bono

4,757
6
48
77

score 2 · Accepted Answer · edited Sep 22 '14 at 06:37

It is possible to send multiple statements in PHP if you are using the mysqli extension, which is a good idea to use instead of the older mysql extension for a lot of reasons. Here is a modified example from the multiple statements section of the documentation, based on your question:

$mysqli = new mysqli("example.com", "user", "password", "database");

$sql .= "INSERT INTO table1 (field1,field2) VALUES('aaa','bbbb') ON DUPLICATE KEY UPDATE `field1` = 'cccc', `field2`='dddddd';";
$sql .= "SELECT field3 Form Table3 WHERE field3='eeeee';";

$mysqli->multi_query($sql);

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

Notice that the documentation does dedicate airtime to security risks of multiple statements, which everyone is pointing out. The other reason, of course, that it's not always a great idea is if you want the second statement to be affected by the first statement.

score 0 · Answer 4 · edited May 23 '17 at 12:32

0

I wouldn't recommend this either but you can use pdo to do this as shown in this thread: PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

edited May 23 '17 at 12:32

Community

1
1

answered Mar 29 '12 at 14:25

erdeszt

799
5
12

INSERT and SELECT in single query MySQL

4 Answers4

Linked