PHP code to encode DB data in JSON not working

Question

I'm currently stuck with some PHP code. I want to access a table in my database and retrieve the data in a JSON format. Therefore, I tried the following code :

<?php

$con = mysqli_connect("......","username","pwd","DBName");

if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "SELECT * FROM users";

if ($result = mysql_query($con, $sql))
{

    $resultArray = array();
    $tempArray = array();    

    while($row = $result->fetch_object())
    {
        $tempArray = $row;
        array_push($resultArray, $tempArray);
    }

    echo json_encode($resultArray);
}

mysqli_close($con);
?>

However, it's getting me an empty page. It worked once but only with a special number of row in the table, so not very efficient as you might guess.

Does anybody have an idea why i'm getting those weird results?

EDIT 1 :

I Just tried to add this to my code :

echo json_encode($resultArray);
echo json_last_error();

And it's returning me 5. It seems to be an error from the data encoding in my table. Therefore I added that code :

$tempArray = array_map('utf8_encode', $row)
array_push($resultArray, $tempArray);

And I got the following output : [null,null,null]0 (The zero comes from the echo json_last_error();)

So here I am, can anybody help me with this ?

You can't mix SQL API's - change mysql_query() to mysqli_query() and swap the order of its arguments around — scrowler
– scrowler, Commented Aug 30, 2015 at 21:45

Alon Eitan · Accepted Answer · 2015-08-30 22:09:56Z

1

I would start by changing if ($result = mysql_query($con, $sql)) to if ($result = mysqli_query($con, $sql)) because they are different database extensions

Another thing would be to change while($row = $result->fetch_object()) to while ($row = mysqli_fetch_object($result)) { (Procedural style vs. Object oriented style)

If you still see blank screen, try adding error_reporting(E_ALL); at the top of your script, and you'll be able to know exactly where the bug is

edited Aug 30, 2015 at 22:09

answered Aug 30, 2015 at 21:46

Alon Eitan

12k8 gold badges51 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MJ23 Over a year ago

Thank's Alon and Robbie for the quick answer ! I changed it to be like : if ($result = mysqli_query($con, $sql)) but it didn't worked, even when I swapped the order of the arguments !

Alon Eitan Over a year ago

@MJ23 - Added another suggestion to this answer

scrowler Over a year ago

The order of mysqli_query's arguments is opposite to mysql_query

Alon Eitan Over a year ago

@RobbieAverill - From the documentation: mixed mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )

scrowler Over a year ago

Nice @Alon - I was looking at the OOP line with query first.

|

Syakur Rahman · Accepted Answer · 2015-08-30 22:17:34Z

0

<?php

$con = mysqli_connect("......","username","pwd","DBName") 
       or die("Failed to connect to MySQL: " . mysqli_connect_error());

$sql = "SELECT * FROM users";
$query = mysqli_query($con, $sql) or die ("Failed to execute query")
if ($result = $query)
{
    $resultArray = array();
    while($row = $result->fetch_object())
    {
        array_push($resultArray, $row);
    }
    $result->close()

    echo json_encode($resultArray);
}

mysqli_close($con);
?>

answered Aug 30, 2015 at 22:17

Syakur Rahman

2,10232 silver badges41 bronze badges

3 Comments

MJ23 Over a year ago

I tried your code and it's not working either... In fact I already tried different codes that should have the same effect but I get the same blank screen every time !

Syakur Rahman Over a year ago

Do you have display_errors on and error_reporting set to E_ALL (0). Try to post your phpinfo, so we know whats your server configuration

MJ23 Over a year ago

I added a error_reporting(E_ALL); in my script but I didn't get any output... Which things should I go through in my phpinfo ?

CharlesEF · Accepted Answer · 2015-08-31 01:11:43Z

0

This code works for me, try it out:

<?php
$con = mysqli_connect("......","username","pwd","DBName");

if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "SELECT * FROM users";

if ($result = mysqli_query($con, $sql))
{

    while($row = $result->fetch_object())
    {
        $resultArray[] = $row;
    }

    echo json_encode($resultArray);
}

mysqli_close($con);
?>

EDIT 1:

As a test replace this code:

 while($row = $result->fetch_object())
 {
  $resultArray[] = $row;
 }
 echo json_encode($resultArray);

with this code:

 while($row = $result->fetch_assoc())
 {
  print_r($row);
 }

What output do you get?

edited Aug 31, 2015 at 1:11

answered Aug 30, 2015 at 23:41

CharlesEF

6282 gold badges15 silver badges26 bronze badges

6 Comments

MJ23 Over a year ago

Hi @CharlesEF ! I tested your code and I'm still getting this blank page... The problem seems to come from the DB ? I double checked the name of the table and the name of the DB, even if an error would have occur if it was the problem..

CharlesEF Over a year ago

What version of PHP do you have? The code runs fine on my IIS (Windows) system.

MJ23 Over a year ago

Well I put the code you gave above and got the following output : Array ( [id] => 1 [firstName] => Jean [lastName] => Pierre [email] => [email protected] [mdp] => jp [photo] => ��J,,��2��C %# , #&')*)-0-(0%()(��C (((((((((((((((((((((((((((((((((((((((((((((((((((��,,"�[date] => 2015-08-27 09:49:31 ) For photo it was a long blob so I cut it short

CharlesEF Over a year ago

This means the connection to the database works and data is being returned. That means the problem must be with the json_encode command. That command requires PHP version 5.2.0 or higher. What PHP version do you have installed? Also, it appears that your table contains some blob columns. Is this correct?

MJ23 Over a year ago

My version of PHP is 5.5.24 and with php -m I saw that I got json and mysqli that are well installed ! And indeed, I've got a blob in one of my columns, I precised it in the last comment at the end :)

|

MJ23 · Accepted Answer · 2015-09-03 14:09:01Z

I finally found a solution ! That was indeed an encoding problem, the json_encode() function accepts only strings encoded in utf8. I changed the interclassement of my table to utf8_general_ci and I modified my code as follows :

<?php
//Create Database connection

$db = mysql_connect(".....","username","pwd");

if (!$db) {

    die('Could not connect to db: ' . mysql_error());

}


//Select the Database

mysql_select_db("DBName",$db);



//Replace * in the query with the column names.

$result = mysql_query("SELECT * FROM users", $db); 


//Create an array

$json_response = array();


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

    $row_array['id'] = $row['id'];
    $row_array['name'] = utf8_encode($row['name']);
    $row_array['lastName'] = utf8_encode($row['lastName']);

    //push the values in the array

    array_push($json_response,$row_array);

}

echo json_encode($json_response);
//Close the database connection

fclose($db);

?>

And I got the expected output.

Collectives™ on Stack Overflow

PHP code to encode DB data in JSON not working

4 Answers 4

9 Comments

3 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

3 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related