mysqli - Looping through SQL query results with PHP. Is there a more efficient way to do this?

I have a PHP script that queries my database, I then loop through the results, convert to JSON and use this to populate some charts using Chart.js

Currently my query only returns 2 columns (month and mitigator_deployed) which is fine, but in reality my query is going to be returning many many columns and then I'd like to select which results I convert to JSON and populate in my charts.

I'm pretty sure that looping through each column one by one and then storing into an array is not the most efficient way to do this!

Here is my current code. (BTW I know I should be using prepared statements!)

    //DB CONNECTION HERE!
    $data_sql = file_get_contents('data/sql.sql');

$data_results = mysqli_query($conn_1, $data_sql);
if($data_results === false) {
    echo mysqli_error($conn_1);
} else {
    $rows = mysqli_fetch_all($data_results, MYSQLI_ASSOC);
}

$month = [];
$value = [];
foreach($rows as $row) {
    
    $month[] = $row['month'];
    $value[] = $row['mitigator_deployed'];
}

$month_data = json_encode($month, JSON_PRETTY_PRINT);
$value_data = json_encode($value, JSON_PRETTY_PRINT);
//REMOVE DOUBLE QUOTES FROM NUMBERS
$newdata = str_replace('"', '', $value_data);

?>
<div><?php pie_chart(1, 500, $newdata, $month_data); ?></div>
Copy code

Ideally I'd like to retrieve everything and then choose which column in my json_decode function, like so;

$column = json_encode($row['my_column'], JSON_PRETTY_PRINT);
Copy code

Is there a more efficient way of doing this?

Thanks

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.