How to create json from mysqli_multi_query resultsets?

Question

I'm trying to display data to bootstrap datatable (4 columns) using php and ajax from mysqli_multi_query().

I could not get the 2nd query working with UNION or INNER JOIN. I'm no programmer and managed to, almost, get the correct result, but for some reason the 1st query returns "null" value.

How can I change that?

Here is a log sample and my code:

PHP Notice: Undefined offset: 2 on line 26 PHP Notice: Undefined offset: 3 on line 27 {"sEcho":1,"iTotalRecords":1,"iTotalDisplayRecords":1,"aaData":{"CommonInverterData":[{"Date":null,"Generated":null,"Export":"0.9921563111569116","Import":"1.8864974578334937"}

/* Database connection start */ 
include ('db.php');

$conn = mysqli_connect($hn, $un, $pw, $db) or die("Connection failed: " . mysqli_connect_error());

/* Database connection end */

$sql = "SELECT m.`date`, `day_energy`
        FROM `CommonInverterData`
        JOIN ( SELECT `date`, MAX(`time`) 'maxtime'
        FROM `CommonInverterData`
        GROUP BY `date`) m
        ON m.maxtime = `CommonInverterData`.`time`
        AND m.`date` = `CommonInverterData`.`date`;";

$sql .= "SELECT ABS(SUM((CASE WHEN `P_Grid`<0 THEN `P_Grid` ELSE 0 END) / 60000 )) as 'Export', SUM((CASE WHEN `P_Grid`>=0 THEN `P_Grid` ELSE 0 END) / 60000 ) as 'Import' FROM `PowerFlowRealtimeData` GROUP BY `date`;";

if (mysqli_multi_query($conn, $sql) or die(mysqli_error($conn))) {
    do {
        if ($result=mysqli_store_result($conn)) {
            $data = array();
            while( $rows = mysqli_fetch_row($result) ) {
                $data[] = array(
                    'Date' => $rows[2],
                    'Generated' => $rows[3],
                    'Export' => $rows[0],
                    'Import' => $rows[1],
                );
            } mysqli_free_result($result);
        }
    } while (mysqli_next_result($conn));
}
mysqli_close($conn);

$return = array(
    "sEcho" => 1,
    "iTotalRecords" => count($data),
    "iTotalDisplayRecords" => count($data),
    "aaData"=>$data);

echo json_encode($return);

Answer 1

Do not mess mess with mysqli_multi_query().

Run one query, collect results into array.
Run the other query, collect results into array.
combine the results in one simple loop.
PROBLEM SOLVED. With simplest tools available.

Whereas the most sensible solution would be using the actual JOIN. For whish you should have been asking an SQL question.

Answer 2

1. You are not checking for mysqli_more_results().

2. You are trying to access 4 elements in each result set, despite only two being offered from each query.

I've blended your snippet with a generalized code block that I built a while ago that helps people to understand and debug mysqli_multi_query(). If there is still something wrong after implementing this, this code block should help you to isolate the cause.

$sql=["SELECT m.`date`, `day_energy`
        FROM `CommonInverterData`
        JOIN ( SELECT `date`, MAX(`time`) 'maxtime'
        FROM `CommonInverterData`
        GROUP BY `date`) m
        ON m.maxtime = `CommonInverterData`.`time`
        AND m.`date` = `CommonInverterData`.`date`",
      "SELECT ABS(SUM((CASE WHEN `P_Grid`<0 THEN `P_Grid` ELSE 0 END) / 60000 )) as 'Export',
        SUM((CASE WHEN `P_Grid`>=0 THEN `P_Grid` ELSE 0 END) / 60000 ) as 'Import'
        FROM `PowerFlowRealtimeData` GROUP BY `date`"
    ];

if(mysqli_multi_query($conn,implode(';',$sql))){
    do{
        // echo "<br><br>",key($sql),": ",current($sql);  // display key:value @ pointer
        if($result=mysqli_store_result($conn)){   // if a result set
            while($rows=mysqli_fetch_row($result)){
                // echo "<br>Cols: {$rows[0]}, {$rows[1]}";
                if(key($sql)===0){  (first query)
                    $tmp1[]=['Date'=>$rows[0],'Generated'=>$rows[1]];
                }else{  // key($sql)===1 (second query)
                    $tmp2[]=['Export'=>$rows[0],'Import'=>$rows[1]];
                }
            }
            mysqli_free_result($result);
        }
        // echo "<br>Rows = ",mysqli_affected_rows($conn); // acts like num_rows on SELECTs
    } while(next($sql) && mysqli_more_results($conn) && mysqli_next_result($conn));
}
if($mysqli_error=mysqli_error($conn)){
    echo "<br><br>",key($sql),": ",current($sql),"Syntax Error:<br>$mysqli_error";  // display array pointer key:value
}
mysqli_close($conn);

$data=array_replace_recursive($tmp1,$tmp2);  // blend the temporary arrays together
$return = array(
    "sEcho" => 1,
    "iTotalRecords" => count($data),
    "iTotalDisplayRecords" => count($data),
    "aaData"=>$data);

echo json_encode($return);

The truth is: mysqli_multi_query() is not best practice for this case. If this was my task/project, I'd be trying to merge the queries into a single call (and avoid calling mysqli_multi_query() entirely).

Here is a link where I advise about what can be done with a UNION query: https://stackoverflow.com/a/34758622/2943403 If you go this route, just remember you will still be producing 2 columns per row (not 4 columns).

As YourCommonSense suggests JOIN is going to be best practice as you'll be able to produce 4 columns per row and simplify your resultset processing. To repeat YCS, this merits a new question with separate/additional details about your database tables and desired output. If you want to receive a high quality response on how to write the query, be sure to include a sqlfiddle of all tables involved and explain the table relationships.