Am I using multi_query() in php wrong?

Question

I'm trying to get an echo of the column names (except the first column) of a table from my database. I used the code below. In my div I get the echo "Cannot get xValues". So it seems that my query doesn't work from the very start. Note however that it was my first time using multi_query(), so maybe I'm missing something. FYI, if I use msqli_query() (and remove the if statement) and I try to echo all the column names of my table without going through the creation of a temporary table to remove the first column, the code works just fine. I appreciate your assistance on this matter. Thanks

        $query = "CREATE TEMPORARY TABLE temp_tb SELECT * FROM $tableName";
        $query .= "ALTER TABLE temp_tb DROP col1";
        $query .= "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$dbName' AND TABLE_NAME = 'temp_tb'";
        
        if ($mysqli -> multi_query($query)){
           
            while($row = $query->fetch_assoc()){
                $result1[] = $row;
            }
    
            $columnArr = array_column($result1, 'COLUMN_NAME');
     
            foreach($columnArr as $columnNames){
            $xValues = "'".$columnNames."'".",";
            echo $xValues;
            }
            
        } else{
            echo "Cannot get xValues";
        }

Generally, using `multi_query()` at all is a bad approach. That said, each query needs to be separated by a semicolon. — Qirel, Jul 01 '20 at 19:05
`$query` is a string, not the result of performing the query. `$query->fetch_assoc()` makes no sense. — Barmar, Jul 01 '20 at 19:19
Agree with @Qirel. To clarify, you need something like this to concatenate your queries: `$query=";"; $query.=";";`. Just notice the semicolons inside the quotes. — Panwen Wang, Jul 01 '20 at 20:33

Dharman · Answer 1 · 2020-07-01T19:21:58.610

To answer your question: Yes, you are using it wrong. You need to perform a blocking loop after you call multi_query.

You should ask yourself rather this question:

Why am I using multi_query?

To which an answer is that you should not use it at all! In fact you should avoid it like fire.

In your case, the code can be simply written like this:

$mysqli->query("CREATE TEMPORARY TABLE temp_tb SELECT * FROM $tableName");
$mysqli->query("ALTER TABLE temp_tb DROP col1");
$stmt = $mysqli->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'temp_tb'");
$stmt->bind_param('s', $dbName);
$stmt->execute();
$result1 = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

$columnArr = array_column($result1, 'COLUMN_NAME');

foreach($columnArr as $columnNames){
    $xValues = "'".$columnNames."'".",";
    echo $xValues;
}

However, I am afraid that you are trying to solve a completely different problem. I would really encourage you to reconsider whatever problem you are trying to solve, because there is probably a much better way to do it. And as always, I would recommend to use PDO instead of mysqli; it is much simpler.

By the way, indeed I should have explained what my goal is. The end result is to create a heatmap with Plotly under javascript. Right now I'm trying to get the x, y, and z values for the javascript array. My code was attempting to get x values, i.e. the column names of my table. There might have an easier way to do so. And if so, i'd love to know. Thanks :) — Didier Alanoix, Jul 02 '20 at 01:58

Am I using multi_query() in php wrong?

1 Answers1