6

I'm trying to understand how to convert MySQL results to a JSON format so that I can then use this JSON later on with Javascript to build a HTML table. However my code just produces lot's of null values and I don't yet understand why.

$result = mysqli_query($con, "SELECT * FROM Customers");

$test = json_encode($result);

print $test;

Output:

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

I have, for example, fields such as "CustomerID" and "Name", and even they don't show up in the JSON result.

What am I doing wrong? Thanks

user9993
  • 5,833
  • 11
  • 56
  • 117
  • Possible duplicate - http://stackoverflow.com/questions/13945071/create-json-object-by-php-from-mysql-result – Jay Blanchard Apr 21 '14 at 16:18
  • not sure if you are aware, when using 'json_decode', using a second parameter of 'true' will force the conversion to arrays rather than objects. i.e. $fooArray = json_decode($json, true);. it may save some hassle. – Ryan Vincent Apr 21 '14 at 18:27

1 Answers1

19
$result = mysqli_query($con, "SELECT * FROM Customers");   
while($row = mysqli_fetch_assoc($result))
    $test[] = $row; 
print json_encode($test);
Alexey Palamar
  • 1,440
  • 1
  • 10
  • 16
  • Thanks, that works. It shows the first customer in the database, is there some way I can loop through it to show all the customers? – user9993 Apr 21 '14 at 16:20
  • 1
    Done. Try to use now. – Alexey Palamar Apr 21 '14 at 16:33
  • for my setup I was doing it different this may help someone down the road `$link = mysqli_connect("localhost", "root", "password", "db"); $results = $link->query($sql); while ($row = $results->fetch_assoc()) $resultArray[] = $row; echo "
    " . json_encode($resultArray, JSON_PRETTY_PRINT) . "
    "; `     – CrandellWS Feb 17 '21 at 17:19