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    gt  111           title    gt   Event1            start    gt    year- month-10            url    gt   http   yahoo com                      2nd record          array           id    gt  222           title    gt   Event2            start    gt    year- month-20            end    gt    year- month-22            url    gt   http   yahoo com

User · Accepted Answer

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);

User · Answer

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

User · Answer

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

User · Answer

The PDO solution  just for a better implementation then mysql      array    pdo- gt query  SELECT id  title    year-month-10  as start url    FROM table  - gt fetchAll PDO  FETCH ASSOC   echo json encode  array     Nice feature is also that it will leave integers as integers as opposed to strings

[php] How to build a JSON array from mysql database

Examples related to php

Examples related to mysql

Examples related to json