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!

Lawrence Cherone · Accepted Answer · 2015-12-31 13:54:16Z

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

why do you need to specify header('Content-type: application/json'); — Kyle Monti, CommentedApr 17, 2012 at 18:32
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 — Lawrence Cherone, CommentedApr 17, 2012 at 18:50
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"], — Kyle Monti, CommentedApr 17, 2012 at 19:04
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. — Lawrence Cherone, CommentedApr 17, 2012 at 19:11
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? — Kyle Monti, CommentedApr 17, 2012 at 19:51

Marc B · Accepted Answer · 2012-04-17 03:33:42Z

5

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

answered Apr 17, 2012 at 3:33

Marc B

361k43 gold badges431 silver badges505 bronze badges

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

| Show 1 more comment

tinybai · Accepted Answer · 2012-04-17 16:15:37Z

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

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. — Marc B, CommentedApr 17, 2012 at 4:06

Dhiraj · Accepted Answer · 2012-04-17 03:43:38Z

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

you're fetching all rows into a SINGLE variable, leaving only the LAST fetched row to be sent out in the json string. — Marc B, CommentedApr 17, 2012 at 3:37

Collectives™ on Stack Overflow

Pulling data from MySQL into json array

4 Answers 4

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Linked

Related