Converting an array of items from SQL query into JSON using PHP

Question

I have used the code below to extract the data from my database and convert it into an array. My problem is that the echo json_encode function does not work and when running this code (without the print_r function) I am left with a blank page.

$query = "SELECT * From table";

$resultarray = array();

if ($result = mysqli_query($connection, $query)) {

     while ($row = mysqli_fetch_row($result)) {

         $resultarray[] = $row;    
     }

     print_r($resultarray); // This line shows that the array is works but the code below does not convert to JSON.

     echo json_encode($resultarray);
}

I have used the print_r function to make sure that I have created an array within my code. I have gone around in circles for hours and I do not understand what I am doing wrong. If I use the print_r function and view the page source I get the following:

 Array
(
[0] => Array
    (
        [0] => 5
        [1] => Name 1
        [2] => Description 1
        [3] => Location 1

    )

[1] => Array
    (
        [0] => 6
        [1] => Name 2
        [2] => Description 2
        [3] => Location 2

    )

[2] => Array
    (
        [0] => 45
        [1] => Name 3
        [2] => Description 3
        [3] => Location 3

    )

Thanks.

Have you figured out You're missing a quote to close the string at the first line? — NoImaginationGuy, May 16 '16 at 11:46
This [LINK](http://stackoverflow.com/a/19366999/4018240) might be helpful for you... — Manjeet Barnala, May 16 '16 at 12:05
Thanks @ManjeetBarnala Problem solved. I looked into this and solved it by adding the following: mysqli_set_charset($connection,"utf8"); — Brett, May 16 '16 at 12:58

score 0 · Answer 1 · edited May 16 '16 at 12:21

0

You forgot to add double quotation on this line:

$query = "SELECT * From table;

and because of that internal server error has occurred.

first line code should be like this

$query = "SELECT * From table";

edited May 16 '16 at 12:21

Nikhil Vaghela

2,088
2
15
30

answered May 16 '16 at 11:53

user6123296

1
5

Good spot, but that is not the issue. I edited the $query when pasting it in stack overflow to make the code clearer but missed the ending ". The code that I am running has this and it query is executed in the database. I'm having issues converting my array into JSON. – Brett May 16 '16 at 11:56
Please check above answer – user6123296 May 16 '16 at 12:20

score 0 · Answer 2 · answered May 16 '16 at 12:07

$con = mysqli_connect("localhost", "username", "password", "dbname");
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql ="SELECT * FROM `table_name`";
if ($result = mysqli_query($con, $sql)) {
    while ($row = mysqli_fetch_row($result)) {
       $resultarray[] = $row;
    }
    echo json_encode($resultarray);
}
mysqli_close($con);

score 0 · Answer 3 · answered May 16 '16 at 12:20

$conn = mysqli_connect("localhost", "root", "root","db_rest");
$query = "SELECT * From users";
$resultarray = array();
if ($result = mysqli_query($conn, $query)) {
while ($row = mysqli_fetch_row($result)) {
$resultarray[] = $row;
}
echo json_encode($resultarray);
}

Converting an array of items from SQL query into JSON using PHP

3 Answers3