How to extract some columns name and value in sql and print in json using php

Question

I am trying to grab some column names and values stored in a database and output it in json format using php.

I got this output. The output is printing additional stuffs like "0":"Rashel Funny", "1":---------, "2":-------:

[{"0":"Rashel Funny","Name":"Rashel Funny","1":"rashel123","Username":"rashel123","2":"2017-01-14 00:00:00","Created":"2017-01-14 00:00:00"},
{"0":"Anna Beghtsson","Name":"Anna Beghtsson","1":"anna123", "Username":"ann`a123","2":"2017-01-14 00:00:00","Created":"2017-01-14 00:00:00"}]

What can i do in my php code to get the below desired result?

[
{
"Name":"Rashel Funny",
"Username":"rashel123",
"Created":"2017-01-14 00:00:00"
},
{
"Name":"Anna Beghtsson", 
"Username":"anna123",
"Created":"2017-01-14 00:00:00"
}
]

Here is my php code:

$sql = "SELECT Name, Username, Created FROM staffdb ";
$result = mysql_query($sql, $conn);
$tempArray = array();
if ($result) {
    while ($row = mysql_fetch_array($result)) {
        $tempArray[] = $row;
    }

    print json_encode($tempArray);
    header('Content-Type: application/json; charset=UTF-8');
}
mysql_close($conn);

Thanks in advance!

score 2 · Accepted Answer · edited May 23 '17 at 11:53

Replace

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

with

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

This will then only return the associative array and not both the numeric array and the associative array

You also need to send the header before you send the json string.

header('Content-Type: application/json; charset=UTF-8');
print json_encode($tempArray);

Every time you use the mysql_ database extension in new code a Kitten is strangled somewhere in the world it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the PDO or mysqli database extensions and prepared statements. Start here

score 0 · Answer 2 · answered Jan 15 '17 at 16:58

Try this it will work as per your expectation :

$sql = "SELECT Name, Username, Created FROM staffdb ";
$result = mysql_query($sql, $conn);
if ($result) {
  do {
       $tempArray[] = $row;
     }while($row = mysql_fetch_array($result));

  print_r(json_encode($tempArray));
}
mysql_close($conn);

How to extract some columns name and value in sql and print in json using php

2 Answers2