0

I have the following two prepared statements addressing the same database table (they follow directly upon each other) and I wonder if it's possible to combine/merge these in one statement. (the connection data are defined in $conn1, session_id is the primary key of that table)

if($p1 = $conn1->prepare("INSERT INTO data1 (session_id) VALUES (?) ON DUPLICATE KEY UPDATE current_time = ?")) {
   $p1->bind_param("ss", $sessionid, $current_time);
   $p1->execute();
   $p1->close();
}

if($p2 = $conn1->prepare("SELECT xxx1, xxx2, xxx3 FROM data1 WHERE session_id = ?")) {
  $p2->bind_param("s", $sessionid);
  $p2->execute();
  $p2->bind_result($xxx1, $xxx2, $xxx3);
  $p2->fetch();
}

In plain words: When a new session is initialized, I want the session ID to be saved in the data1 table. If the session ID already exists, I want the current_time value to be updated and some other values (xxx1, xxx2, xxx3) to be fetched from data1.

The way I wrote it above it works, but I'd prefer to have only one query. From what I read it might be possible if it were only a simple INSERT followed by a SELECT, but not with the ON DUPLICATE KEY UPDATE involved. But I'd be happy to learn that it can be done in one query...

Shadow
  • 33,525
  • 10
  • 51
  • 64
Johannes
  • 64,305
  • 18
  • 73
  • 130
  • No, it isnot possible to combine an insert and a select into one statement. Obviously, you can create a stored procedure that combines the 2 statement and you only call the stored procedure from your application. – Shadow Jan 07 '17 at 23:54
  • This is only possible in PostgreSQL, using the "returning" keyword. – Crouching Kitten Jan 07 '17 at 23:55
  • The question has nothing to do with php, mysqli, or with prepared statements. This is purely a mysql question (can you combine an insert with a select) – Shadow Jan 08 '17 at 00:01
  • @Shadow The included (but not posted) `$conn1` contains this line: `$conn1 = new mysqli("my_host", "db_user", "db_pass", "db_name");`, so YES, it has to do with mysqli – Johannes Jan 08 '17 at 00:05
  • thanks for the comments, guys! – Johannes Jan 08 '17 at 00:09
  • 2
    It is irrelevant whether you use mysqli, pdo, odbc, or a driver written by yourself. These just pass the sql query to the mysql server, which in turn executes it. Therefore from this question point of view, it does not matter that your code uses mysqli. – Shadow Jan 08 '17 at 00:12

0 Answers0