php使用mysqli生成json数组

Question

I am learning php/mysql for a website I am building. I had everything working but it was incredibly vulnerable and insecure with little error trapping. I have begun trying to secure my code and the first thing I am trying to do is better handle errors and implement prepared statements to avoid sql injection.

Unfortunately I have got stuck at my first hurdle, I originally created my json array like this:

$myArray=array();
$tempArray = array();

while ( $row = $results->fetch_assoc())
{   
    $tempArray[0] = $row['unix_timestamp(end_date)'];
    $tempArray[0] *= 1000;
    $tempArray[1] = $row['bid'];
    array_push($myArray, $tempArray);
}
echo json_encode ($myArray, JSON_NUMERIC_CHECK);

That worked greate producing a json I could then use with highcharts on my website.

My question is this, I have updated my code to include prepared statements utilising Prepare and bind_param which works (tested by simply echoing the result) however I am really struggling to get the errors into an array as above:

 while ( $row = $select_stmt->fetch())
{   
    printf("%s %s
", $col1, $col2);
    //$tempArray[0] = $col2;
    //$tempArray[0] *= 1000;
    //$tempArray[1] = $col1;
   //array_push($myArray, $tempArray); 
}
//echo json_encode ($tempArray, JSON_NUMERIC_CHECK);

The above prints the text out but whenever I try and use fetch_assoc are another alternative (i've done a lot of googling) I cannot get it to work. I always receive the same sorts of errors "Call to undefined method..." How do I replicate what I had working and use mysqli with bind_result to produce an array?

I would appreciate understanding exactly where I am going wrong as I believe I am missing a concept which is leading me down the wrong paths when looking for a solution.

EDIT - I have checked and I am using the mysqlnd driver

UPDATED Code - I think this is closer - utilising the get_result method instead of "bind_result" before calling fetch_assoc again.

$result = $select_stmt->get_result();
$myArray=array();
$tempArray = array();

 while ( $row = $result->fetch_assoc())
{
    $tempArray[0] = $row['end_date'];
    $tempArray[0] *= 1000;
    $tempArray[1] = $row['bid'];
    array_push($myArray, $tempArray);
}
echo json_encode ($tempArray, JSON_NUMERIC_CHECK);

Unfortunately it only retrieves the first value and not the subsequent 10's or 100's or 1000's of rows

SOLUTION - correct array in the json_encode and use of the get_result function

$result = $select_stmt->get_result();

$myArray=array();
$tempArray = array();

while ( $row = $result->fetch_assoc())
{
    foreach ($row as $r){
        $tempArray[0] = $row['end_date'];
        $tempArray[0] *= 1000;
        $tempArray[1] = $row['bid'];
        array_push($myArray, $tempArray);
    }
}
echo json_encode ($myArray, JSON_NUMERIC_CHECK);

Answer 1

At the end of the code, use $myArray instead of $tempArray