1

I am executing multiple SQL insert commands and one select query in a single request to the database server but I get back a result for each command as well.

Simplified example:

$query = 'INSERT INTO table1 (column1) VALUES (123);' .
         'SET @id = LAST_INSERT_ID();' .
         'INSERT INTO table2 (column1, column2) VALUES (@id, 456);' .
         'INSERT INTO table3 (column1, column2) VALUES (@id, 789);' .
         'SELECT @id';

$conn = new mysqli('server', 'user', 'pass', 'name');

if ($conn->multi_query($query)) {
    do {
        if ($result = $conn->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
    } while ($conn->next_result());
}

This results in three empty results and the fourth is the one I want.

Is it possible to not return results for the non-queries (the INSERTs and SET) and only get one result (for the SELECT) so I don't have to go through each to get what I want?

NB. Not wanting to use a stored procedure for this due to the complexity and variability of the dynamically generated INSERTs

johna
  • 10,540
  • 14
  • 47
  • 72
  • just dont use multi_query, its a night mere to debug and really has no benefit over running each query individually –  Apr 08 '18 at 23:09
  • @smith, wouldn't there be some benefit making a single round trip to the DB if there was 5-10 inserts? – johna Apr 08 '18 at 23:13
  • not really, feel free to benchmark it, you will find the difference minute –  Apr 08 '18 at 23:14
  • Of course you can use a stored procedure for one call! – Parfait Apr 09 '18 at 00:53
  • @Parfait, some of the inserts are a bit beyond a stored procedure because there is a variable number of items that needed to be inserted depending on certain factors. – johna Apr 09 '18 at 05:19
  • Your example here shows nothing that cannot be done in stored proc. You can still pass PHP values as parameters to a MySQL stored proc. – Parfait Apr 09 '18 at 14:05
  • @Parfait, my example is only a simplification. The reason I am not using a stored procedure is that the number of inserts varies and it I don't believe it is practical to move the logic that determines these inserts from php into a stored procedure. – johna May 03 '18 at 23:04
  • Unless you are building dynamic queries, I still think stored procs are doable here. Here it shows already build SQL statements. In your stored proc, you can write ALL possible inserts with conditions by `if` logic from input parameters. Post actual use case for more help. – Parfait May 04 '18 at 14:41

0 Answers0