phpmysqlimysqli-multi-query

Unable to get a multi line query executed


I have a multi line-command query that I want to execute. The query includes creating temporary tables and returning a table by using them. The code I tried:

<?php

$mysqli = new mysqli("localhost", ...);

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

$query  = "CREATE TEMPORARY TABLE IF NOT EXISTS TT1 AS (Select * from `T1` order by `id` desc);";
$query .= "CREATE TEMPORARY TABLE IF NOT EXISTS TT2 AS (SELECT @n := @n + 1 `id`, `c1`, `c2`, `c3` FROM TT1, (SELECT @n := 0) m );";
$query .= "(Select `id`, `c3` from `TT2` limit 1) union (Select `id`, `c3` from `TT2` where `id`%25=0 ORDER BY `id` asc) union (Select `id`, `c3` from `TT2` where (`id`-1)%25=0 ORDER BY `id` asc) union (Select `id`, `c3` from `TT2` order by `id` desc limit 1) order by `c3` desc;";


/* 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();
        }else{
            echo "bad2";
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }else{
            echo "bad3";
        }
    } while ($mysqli->next_result());
}else{
    echo "bad1";
}

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

?>

I am dealing with:

  1. The program does not do well with handling temporary tables,
  2. The program exits beacuse of error exactly after parsing the first query,
  3. I dont know how to actually get the info of the last query.

I should mention that the queries work exactly as intentioned in phpMyAdmin console.

Edit

As for debugging I added some echoes to find the flow of the program. The program outputs:

goodbad1

Solution

  • Split it up into 3 separate queries and execute it one after another. Never use multi_query()!

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli("localhost", ...);
    $mysqli->set_charset('utf8mb4'); // always set the charset
    
    $query = "CREATE TEMPORARY TABLE IF NOT EXISTS TT1 AS (Select * from `T1` order by `id` desc);";
    $mysqli->query($query);
    
    $query = "CREATE TEMPORARY TABLE IF NOT EXISTS TT2 AS (SELECT @n := @n + 1 `id`, `c1`, `c2`, `c3` FROM TT1, (SELECT @n := 0) m );";
    $mysqli->query($query);
    
    $query = "(Select `id`, `c3` from `TT2` limit 1) union (Select `id`, `c3` from `TT2` where `id`%25=0 ORDER BY `id` asc) union (Select `id`, `c3` from `TT2` where (`id`-1)%25=0 ORDER BY `id` asc) union (Select `id`, `c3` from `TT2` order by `id` desc limit 1) order by `c3` desc;";
    $stmt = $mysqli->prepare($query);
    $stmt->execute();
    $result = $stmt->get_result();
    
    foreach ($result as $row) {
        // Do something
        echo $row['id'];
        echo $row['c3'];
    }