How to retrive php data from mysql and convert it to json?

Question

My mind got stuck when i try to develope this: i have a table in my database called "article" whit two column, "name" and "price". How can i extract all rows from my table and echo all column in JSON? i really can't understand how to convert result in JSON. My mind it's stuck like never before. i need to echo something like this:

{"items": {
    "items":[
        {"name": "firstitemname", 
         "price": "5"
        },
        {"name": "secondone", 
         "years": "3"
        }],
}}

Please help me fixing my buggy code!

<?php

$query = mysql_query("SELECT * FROM itemlist");
$nameitem = array();
$itemprice = array();
while($row = mysql_fetch_array($query)){
array_push($nameitem , $row['nome']);
array_push($itemprice, $row['pix']);
}

?>

iambriansreed · Accepted Answer · 2011-07-19 19:23:41Z

4

You would simply edit your PHP as follows.

<?php

$query = mysql_query("SELECT * FROM itemlist");
$items = array();
while($row = mysql_fetch_array($query)){
$items[] = array('name' => $row['nome'], 'price' => $row['pix']);
}

echo json_encode(array('items'=>$items));

?>

http://php.net/manual/en/function.json-encode.php

JSON is super easy to deal with in PHP.

edited Jul 19, 2011 at 19:23

answered Jul 19, 2011 at 19:17

iambriansreed

22.3k6 gold badges66 silver badges81 bronze badges

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

1 Comment

iambriansreed Over a year ago

Let me know if it works as expected. Also, I spelled name the same way you did, not sure if that is valid.

somomomo · Accepted Answer · 2011-07-19 19:23:06Z

2

If you're using PHP 5.2 or greater, you can use the json_encode function to do exactly what you're trying to do: http://www.php.net/manual/en/function.json-encode.php

For your code, you should be able to do something like this:

$query = mysql_query("SELECT * FROM itemlist");
$json_output = array();
while($row = mysql_fetch_assoc($query)){
    $json_output[] = json_encode($row);
}

Here, $json_output will contain an array of strings with the json encoded string of each row as each array element. You can output these as you please.

answered Jul 19, 2011 at 19:23

somomomo

2951 gold badge3 silver badges10 bronze badges

Comments

Pal Murugan · Accepted Answer · 2012-05-09 10:54:34Z

2

<?php
$result = mysql_query("select * from item_list");
$rows = array();
while($r = mysql_fetch_assoc($result))
{
$rows[] = $r;
}
print json_encode($rows);
?>

answered May 9, 2012 at 10:54

Pal Murugan

211 bronze badge

Comments

Masoud Siahkali · Accepted Answer · 2016-12-10 13:06:59Z

Convert Data table to json with following code :

echo(json_encode($array));

for example ( select data from mysql and convert to json ):

 public function SELECT($tableName,$conditions){

      $connection = mysqli_connect($hostname, $userName, $password,$dbName);
      try {

        if (!$connection)
            die("Connection failed: " . $connection->connect_error);
        else
        {
            $qry = "";
            if(!$this->IsNullOrEmptyString($conditions))
               $qry = "SELECT * FROM `".$tableName."` WHERE ".$conditions;
            else
               $qry = "SELECT * FROM `".$tableName."`";

            $result = mysqli_query( $connection, $qry);
            if($result) {
                $emparray = array();
                while($row =mysqli_fetch_assoc($result))
                    $emparray[] = $row;

                echo(json_encode($emparray));           
            }
            else
                echo(mysqli_error($connection));       
            } 
            mysqli_close($connection); 
      } catch(Exception $ex) {
          mysqli_close($connection);
          echo($ex->getMessage());
      }  
 }

Collectives™ on Stack Overflow

How to retrive php data from mysql and convert it to json?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related