Jump to content

Archived

This topic is now archived and is closed to further replies.

hoopplaya4

[SOLVED] JSON_encode and MySQL

Recommended Posts

Hi All,

 

I'm currently querying my database to encode into json array.

 

If I hard-code the JSON array (without using any database), this is what it should look like:

 

<?php
echo json_encode(array(

	array(

		'id' => 201,
		'title' => "My Title",
		'start' => 1244941200000,  //notice there are no "quotes" around numbers.
	        'end' => 1244947500000,  //notice there are no "quotes" around numbers.
		'details' => "Details",
		'location' => "Location"
	),

));

 

What I am currently using to grab the data from the DB and encode in a json array is:

 

<?php
$sql = "SELECT * FROM event";
require("connection.php");
$result = mysql_db_query($DBname,$sql,$link) or die(mysql_error()); 

$rows = array();
while($r = mysql_fetch_assoc($result)) {
    $rows[] = $r;
}
print json_encode($rows);

 

Now, this works fine, however, it is printing my 'start' and 'end' fields as strings (as if they had quotes around them).  How would I output it without the "quotes"?  Or should I go about this a different way?

 

Thanks very much!

Share this post


Link to post
Share on other sites

You did not mention that the 'id' was a problem? What are the column types in the database?

 

Share this post


Link to post
Share on other sites

Sorry, id is an issue as well.  In the database, "id", "start", and "end" are all INT (15) column types.

 

I did notice this on the PHP: json_encode (http://usphp.com/manual/en/function.json-encode.php#86513) page.

 

A note about json_encode automatically quoting numbers:

 

It appears that the json_encode function pays attention to the data type of the value. Let me explain what we came across:

 

We have found that when retrieving data from our database, there are occasions when numbers appear as strings to json_encode which results in double quotes around the values.

 

This can lead to problems within javascript functions expecting the values to be numeric.

 

This was discovered when were were retrieving fields from the database which contained serialized arrays. After unserializing them and sending them through the json_encode function the numeric values in the original array were now being treated as strings and showing up with double quotes around them.

 

The fix: Prior to encoding the array, send it to a function which checks for numeric types and casts accordingly. Encoding from then on worked as expected.

 

So, perhaps I need some help writing a function that recognizes the "id" "start" and "end" fields prior to encoding.  Just not sure where I'd start with that.

Share this post


Link to post
Share on other sites

All the mysql_fetch_xxxxx() functions state they return an array of strings.

 

The most straight forward solution would be for you to make a list of the field names that you want to be int (rather than checking the value, because there might be cases where the value is numeric but you want it to remain a string.) Then you will need to cast/convert those fields in each row that you want to be integers. I don't know if you can do this in place in the array or if you will need to loop through each element of the array.

Share this post


Link to post
Share on other sites

You can convert in place (in the array) and for the values you show in the example, the following works -

 

// define the fields to be int (float actually because the values shown exceed a 32bit int)
$int_fields = array('id','start','end');
....

// in your loop -
foreach($int_fields as $value){
$r[$value] = (float)$r[$value];
}

 

Share this post


Link to post
Share on other sites

Hey PFMaBiSmAd,

 

That conversion you gave me worked perfect.  Thanks for bringing that to my attention!

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.