How to build a JSON array from mysql database

Question

Okay I have been racking my brain trying to build a JSON array from mysql. The array MUST be in the following format. I am using fullcalendar and want to make the events on the calendar dynamic. Below is the code that builds the array, but currently it does not get the information from mysql

$year = date('Y');
$month = date('m');

echo json_encode(array(

    //Each array below must be pulled from database
        //1st record
        array(
        'id' => 111,
        'title' => "Event1",
        'start' => "$year-$month-10",
        'url' => "http://yahoo.com/"
    ),

         //2nd record
         array(
        'id' => 222,
        'title' => "Event2",
        'start' => "$year-$month-20",
        'end' => "$year-$month-22",
        'url' => "http://yahoo.com/"
    )

));

jk. · Accepted Answer · 2016-03-18 15:01:34Z

66

Is something like this what you want to do?

$return_arr = array();

$fetch = mysql_query("SELECT * FROM table"); 

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    $row_array['id'] = $row['id'];
    $row_array['col1'] = $row['col1'];
    $row_array['col2'] = $row['col2'];

    array_push($return_arr,$row_array);
}

echo json_encode($return_arr);

It returns a json string in this format:

[{"id":"1","col1":"col1_value","col2":"col2_value"},{"id":"2","col1":"col1_value","col2":"col2_value"}]

OR something like this:

$year = date('Y');
$month = date('m');

$json_array = array(

//Each array below must be pulled from database
    //1st record
    array(
    'id' => 111,
    'title' => "Event1",
    'start' => "$year-$month-10",
    'url' => "http://yahoo.com/"
),

     //2nd record
     array(
    'id' => 222,
    'title' => "Event2",
    'start' => "$year-$month-20",
    'end' => "$year-$month-22",
    'url' => "http://yahoo.com/"
)

);

echo json_encode($json_array);

edited Mar 18, 2016 at 15:01

answered Jun 8, 2011 at 16:20

jk.

14.4k4 gold badges45 silver badges58 bronze badges

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

16 Comments

Kevin Nelson Over a year ago

+1 beat me to it. If all the DB columns are the right names, you should be able to drop the assignment and just push $row to the array.

Wrikken Over a year ago

Why are you creating $row_array? Why not SELECT id, title, '$year-month-10' as start,url FROM table, and do a direct $return_arr[] = $row;?

jk. Over a year ago

@Kevin, Yes, you are correct. I actually quickly pulled this from an autocomplete example where I had to assign the array values different names. So +1 right back at you!

Wrikken Over a year ago

Yeah, one doesn't get an alert of another comment pending while writing one :)

Stan Forrest Over a year ago

I am still not getting it. The array must show exactly as above otherwise the information does not show up on the calendar.

|

Wrikken · Accepted Answer · 2011-06-08 16:29:06Z

11

The PDO solution, just for a better implementation then mysql_*:

$array = $pdo->query("SELECT id, title, '$year-month-10' as start,url 
  FROM table")->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($array);

Nice feature is also that it will leave integers as integers as opposed to strings.

answered Jun 8, 2011 at 16:29

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

3 Comments

Wrikken Over a year ago

PDO is the defacto standard to connect to various databases in PHP nowadays, one of which is MySQL. Still using the mysql-extension in IMHO about as bad as still making hew HTML 3.2 pages. If your project really asks for it, go ahead, but new code should have nothing do with it, and if not PDO, at least use mysqli (note: the 'i' stands for improved ).

Stan Forrest Over a year ago

I have to use mysql as the others are not available to me.

jk. Over a year ago

I have been playing around with PDO. Thanks for the push to actually update some of my code. Already have it in place and running on some older code. Thank you.

AndroLogiciels · Accepted Answer · 2015-04-29 15:54:57Z

3

Just an update for Mysqli users :

$base= mysqli_connect($dbhost,  $dbuser, $dbpass, $dbbase);

if (mysqli_connect_errno()) 
  die('Could not connect: ' . mysql_error());

$return_arr = array();

if ($result = mysqli_query( $base, $sql )){
    while ($row = mysqli_fetch_assoc($result)) {
    $row_array['id'] = $row['id'];
    $row_array['col1'] = $row['col1'];
    $row_array['col2'] = $row['col2'];

    array_push($return_arr,$row_array);
   }
 }

mysqli_close($base);

echo json_encode($return_arr);

answered Apr 29, 2015 at 15:54

AndroLogiciels

1968 bronze badges

1 Comment

marciokoko Over a year ago

I tried both the pdo and the sqli and I cant get the array to log properly. You are using echo json_encode($array); which doesnt work for me but Echo $json->encode($arr); does work for me...why? I use mysql_query and create a mysql_fetch_object.

Bijender Singh Shekhawat · Accepted Answer · 2016-07-23 09:02:05Z

1

Use this

$array = array();
$subArray=array();
$sql_results = mysql_query('SELECT * FROM `location`');

while($row = mysql_fetch_array($sql_results))
{
    $subArray[location_id]=$row['location'];  //location_id is key and $row['location'] is value which come fron database.
    $subArray[x]=$row['x'];
    $subArray[y]=$row['y'];


 $array[] =  $subArray ;
}
echo'{"ProductsData":'.json_encode($array).'}';

answered Jul 23, 2016 at 9:02

Bijender Singh Shekhawat

4,5822 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to build a JSON array from mysql database

4 Answers 4

16 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

16 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related