php - mySQLi array directly to json array

Question

Is it possible to get mysqli to spit out an array directly that I then json_encode with php (to retrieve with jquery) ?

I mean.. avoid making a while loop

I have this:

    $sql = 'SELECT id, name FROM thetable';
    $stmt = $conn->prepare($sql);

    if ($stmt) {

        $stmt->execute();
        $stmt->store_result();

        if ($stmt->num_rows > 0) {
            $stmt->bind_result($sql_id, $sql_name);

            $json = array();

            while($row = $stmt->fetch()){
                $json[] = $sql_id.'=>'.$sql_name;
            }

            echo json_encode($json);

        }

    }

(this is just a simplyfied short version of my code)

Answer 1

If your query does not have any parameters, you might as well avoid using the prepared statement. Something like this should suffice

header('Content-type: application/json');
echo json_encode(
    $conn->query('SELECT id, name FROM thetable')
         ->fetch_all(MYSQLI_ASSOC)
);
exit;

If you do need the statement, use mysqli_stmt::get_result

$stmt = $conn->prepare($sql);
// $stmt->bind_param(...);
$stmt->execute();
$result = $stmt->get_result();

header('Content-type: application/json');
echo json_encode($result->fetch_all(MYSQLI_ASSOC));
exit;