Getting mysqli result as array for json_encode() in PHP

Question

I finally convinced myself to switch from PHP mysql to mysqli after upgrading my old PHP version. However, I did not manage to implement the same approach as before:

This is the old approach:

$sth = mysql_query("select * from .....");

$rows = array();

while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}

print json_encode($rows);

This is my mysqli approach:

$prename = "Peter";

$rows = array();

$mysqli = new mysqli($server, $user, $pass, $dbase);

if ($stmt = $mysqli->prepare("select lastname where prename = ? order by prename asc")) {

    /* bind parameters for markers */
    $stmt -> bind_param("s", $prename);

    /* execute query */
    $stmt -> execute();

    /* bind result variables */
    $stmt -> bind_result($lastname);

    /* fetch value */
    $stmt->fetch();

    echo $lastname;

    /* close statement */
    $stmt -> close();
}

/* close connection */
$mysqli -> close();

print json_encode($rows);

How can I add the result of the query to the $rows[] array? The return value has to be a json string that will be parsed by my webapplication. I tried several solutions with $stmt -> fetch_array but none of them worked.

Thank you very much for your help.

Answer 1

I was needed to get JSON with numeric arrays from mysqli query without prepared statements.

<?php 
  header('Content-Type: application/json');
  $conn = include_once("dbopen.php");
  $result = mysqli_query($conn, "CALL sp_readtable");
  $data = array();
  while ($row = mysqli_fetch_row($result)){
      $data[] = $row;
  }
  echo json_encode($data, JSON_NUMERIC_CHECK);
?>

The result is a 2-dimensional true array without the overhead of the keys, like this one below:

[
  ["row1fieldvalue1","row1fieldvalue2"],
  ["row2fieldvalue1","row2fieldvalue2"],
  ...
]

Answer 2

    //index.html (jQuery)
                $.ajax({
                    type : "POST",
                    url : "getResults.php",
                    data : "ID=" + "160",
                    datatype : "json",
                    success : function(result) {
                        console.log(result);
                    },
                        error : function(msg) {
                            console.log("error",msg);
                        }
                });

    //getResults.php
<?php
    try {
        include ('config.php');

        $db = connect();

        $stmt = $db -> prepare('SELECT * FROM test where test2_id = ? group by test4_id,test5_id');

        $p1 = "151";

        $stmt -> bind_param('s', $p1);  //$_POST['ID']);

        $stmt -> execute();

        $result = $stmt -> get_result();

        $returnVAR = array();

        //MYSQLI_NUM = Array items will use a numerical index key.
        //MYSQLI_ASSOC = Array items will use the column name as an index key.
        //MYSQLI_BOTH = [default] Array items will be duplicated, with one having a numerical index key and one having the column name as an index key.
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
            $returnVAR[] = $row;
        }

        //unicode
        header("Content-Type: application/json", true);

        echo json_encode($returnVAR);

    } catch (exception $e) {
        echo json_encode(null);
    }
?>

    //config.php
    <?php

    function connect() {
        $mysql_hostname = "localhost"; 
        $mysql_user = "coin";
        $mysql_password = "P8";
        $mysql_database = "c3in";

        //setup a connection with mySQL
        $mysqli = new mysqli($mysql_hostname, $mysql_user, $mysql_password,$mysql_database);

        /* check connection */
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }

        //enable utf8!
        $mysqli -> query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");

        return $mysqli;
    }

    ?>

Answer 3

Convince yourself to use PDO

$stm = $db->prepare("select lastname where prename = ? order by prename asc");
$stm->execute([$prename]);
echo json_encode($stm->fetchAll());

Answer 4

I ended up using PDO as it seems to much easier than mysqli:

$stmt = $db->prepare("select * from .....");
$stmt->execute(array($lastname));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
print json_encode($rows);

Answer 5

You will find a solution to your question on the php.net mysqli bind_result page here:

http://www.php.net/manual/en/mysqli-stmt.bind-result.php

check out the comment by nieprzeklinaj at gmail dot com

he/she provides a function fetch() that will work as a fetch all for prepared mysqli statements (returning the full result set in an array). It will work with a dynamic number of selected fields.

You can add the fetch() function to your php code (of course you may call it whatever you like).

Then to use it in the code you provided above you would do something like:

$prename = "Peter";

$rows = array();

$mysqli = new mysqli($server, $user, $pass, $dbase);

if ($stmt = $mysqli->prepare("select lastname where prename = ? order by prename asc")) {

     /* bind parameters for markers */
     $stmt -> bind_param("s", $prename);

     /* execute query */
     $stmt -> execute();

     /* call the fetch() function provided by (nieprzeklinaj at gmail dot com) */
     $rows = fetch($stmt);
}

/* close connection */
$mysqli -> close();

print json_encode($rows);

UPDATED

FULL

I created a test table named "comment" and gave it a "prename" field and some other random fields just for demonstration purposes:

<?php
//fetch function from php.net (nieprzeklinaj at gmail dot com)
function fetch($result)
{    
    $array = array();

    if($result instanceof mysqli_stmt)
    {
        $result->store_result();

        $variables = array();
        $data = array();
        $meta = $result->result_metadata();

        while($field = $meta->fetch_field())
            $variables[] = &$data[$field->name]; // pass by reference

        call_user_func_array(array($result, 'bind_result'), $variables);

        $i=0;
        while($result->fetch())
        {
            $array[$i] = array();
            foreach($data as $k=>$v)
                $array[$i][$k] = $v;
            $i++;

            // don't know why, but when I tried $array[] = $data, I got the same one result in all rows
        }
    }
    elseif($result instanceof mysqli_result)
    {
        while($row = $result->fetch_assoc())
            $array[] = $row;
    }

    return $array;
}


$prename = "Peter";

$rows = array();

$server = 'localhost';
$user = 'user';
$pass = 'pass';
$dbase = 'mydatabase';

$mysqli = new mysqli($server, $user, $pass, $dbase);

$prename = "Peter";

$rows = array();

if ($stmt = $mysqli->prepare("select * from comment where prename = ? order by prename asc")) {

     /* bind parameters for markers */
     $stmt -> bind_param("s", $prename);

     /* execute query */
     $stmt -> execute();

     /* call the fetch() function provided by (nieprzeklinaj at gmail dot com) */
     $rows = fetch($stmt);
}
else{
    //print error message
    echo $mysqli->error;
}


/* close connection */
$mysqli -> close();

print json_encode($rows);

output is:

[{"prename":"Peter","comment_id":1,"fullname":"Peter 1","email":"some email"},{"prename":"Peter","comment_id":2,"fullname":"Peter 2","email":"some email"},{"prename":"Peter","comment_id":3,"fullname":"Peter 3","email":"some email"}]

database table info (so you can check the output):

mysql> describe comment;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prename    | varchar(100) | YES  |     | NULL    |       |
| comment_id | int(11)      | YES  |     | NULL    |       |
| fullname   | varchar(150) | YES  |     | NULL    |       |
| email      | varchar(150) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

mysql> select * from comment;
+---------+------------+----------+------------+
| prename | comment_id | fullname | email      |
+---------+------------+----------+------------+
| Peter   |          1 | Peter 1  | some email |
| Peter   |          2 | Peter 2  | some email |
| Peter   |          3 | Peter 3  | some email |
+---------+------------+----------+------------+

Answer 6

You can fill your array like this

/* bind result variables */
$stmt -> bind_result($lastname);

/* fetch value */
while ($stmt->fetch()) {
    array_push($row, $lastname);
}