How to Create a JSON Array from Data Retrieved from a MySQL Database?

JSON Array Construction from MySQL Database

Creating a JSON array from a MySQL database is a common task in web development. The requirement is to extract specific fields from the database and format them into a JSON array. A JSON array is a list of values enclosed in square brackets, where each value can be a string, number, object, or another array.

To create a JSON array, we'll use the json_encode() function in PHP. This function converts a PHP array into a JSON string. However, before converting, we need to fetch the data from the MySQL database and store it in a PHP array.

Here's an example code to fetch data from a MySQL table named "table":

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

This code will fetch all the columns from the "table" and store them in an array called $return_arr. Each row of the table is stored as an associative array within $return_arr. Finally, the json_encode() function is used to convert the PHP array into a JSON string and echoed to the client.

Alternatively, you can also create a JSON array directly by fetching the data and constructing the array:

//Fetching variables
$year = date('Y');
$month = date('m');

$json_array = array(
    array(
    'id' => 111,
    'title' => "Event1",
    'start' => "$year-$month-10",
    'url' => "http://yahoo.com/"
),
    array(
    'id' => 222,
    'title' => "Event2",
    'start' => "$year-$month-20",
    'end' => "$year-$month-22",
    'url' => "http://yahoo.com/"
)
);
    
echo json_encode($json_array);

The above is the detailed content of How to Create a JSON Array from Data Retrieved from a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!