Pulling data from MySQL into json array

Question

I'm trying to pull data from my database using json in php. I have a few elements I need to specific then to post them on a page.

I want to "fetch" the data from mysql and return it to a json_encode. How can I do this using the SELECT method. Some had used PDO methods and other have used mysql_assoc, which confuses me.

For instance,

I have rows of: 'id' , 'title' , 'start', 'backgroundColor'...etc. along with a default value for all of them. ($array[] = "someValue = default")

I want it to export like so:

array(
 'id' => 1,
 'title' => "someTitle",
 'start' => "2012-04-16",
 'backgroundColor' => "blue",
 'someValue' = > "default",
 ...
 ), ....
 ));

If anyone could help me with this with the best detail, I'd be awesome!

Answer 1

If you wanted to do this with PDO then here is an example:

<?php 
$dbh = new PDO("mysql:host=localhost;dbname=DBNAME", $username, $password);
$sql = "SELECT `id`, `title`, `time`, `start`, `backgroundColor` 
 FROM my_table";
$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
//To output as-is json data result
//header('Content-type: application/json');
//echo json_encode($result);
//Or if you need to edit/manipulate the result before output
$return = [];
foreach ($result as $row) {
 $return[] = [ 
 'id' => $row['id'],
 'title' => $row['title'],
 'start' => $row['start'].' '.$row['time'],
 'backgroundColor' => $row['backgroundColor']
 ];
}
$dbh = null;
header('Content-type: application/json');
echo json_encode($return);
?>

• Kyle Monti: why do you need to specify header('Content-type: application/json')
• Lawrence Cherone: If your going to access the output with jQuery ajax() or such the you need to specify header content type to match dataType.api.jquery.com/jQuery.ajax (DataType Section) also Its always good practice to set content type on anything other then text/html
• Kyle Monti: Okay. and this method will easily fetch all the data. What if I wanted to added two datas to one with a space? "data" => $row["start"]." ".$row["time"],
• Lawrence Cherone: I have update my answer. If you want to manipulate the data before you assign values into an array then you need to loop it, there are enough examples to give yo an idea of what todo now.
• Kyle Monti: this actually makes is extremely easier to use with you writing out like this. All I need to do is set the variables for my password and username and add the remaining array fetches. But if I wanted to leave the items that are blank, not null. Could I specify, "FROM my_table where length(column) > 0" or how exactly would I word that?

Answer 2

You don't "fetch to a json array".

You fetch your database results into a PHP array, then convert that php array, AFTER THE FETCHING IS COMPLETED, to a json string.

e.g.

$data = array();
while ($row = mysql_fetch_assoc($results)) {
 $data[] = $row;
}
echo json_encode($data);

• Steven: If you wanted to use PDO you could skip the while: $data = $queryHandle->fetchAll()
• Kyle Monti: so the $data[] would be something like ` $data['id'] = $row['id'];` ?
• Marc B: no. $data would be an array of arrays, each sub-array being the result of one fetch operation.
• Kyle Monti: @Marc B so this would be the starting, then therefore after, you would specify each array like i said?
• Marc B: it'd be data[7]['id'] to fetch the 8th row's id field.

Answer 3

You can get the result from mysql,then format it to json

 $array = array();
 while($row = mysqli_fetch_array($result))
 {
 array_push($array,$row);
 }
 $json_array = json_encode($array);

• Marc B: Of what use would this be? $row would contain only the LAST row fecthed. multiple independently encoded strings cannot be concatenated into a single json string afterwards either.

Answer 4

Please check for SELECT methods here

In general it would look like this

$data = array(); // result variable
$i=0
$query = "SELECT id,title,start,backgroundColor FROM my_table"; // query with SELECT
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){ // iterate over results
 $data['item'][$i]['id'] = $row['id']; // rest similarly 
 ...
 ...
 $i++; 
}
header('Content-type: application/json'); // display result JSON format
echo json_encode(array(
 'success' => true,
 'data' => $data // this is your data variable
));

• Marc B: you're fetching all rows into a SINGLE variable, leaving only the LAST fetched row to be sent out in the json string.