[SOLVED] JSON_encode and MySQL

[hoopplaya4]

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!

[PFMaBiSmAd]

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

 

[hoopplaya4]

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.

[PFMaBiSmAd]

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.

[PFMaBiSmAd]

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];
}

 

[hoopplaya4]

Hey PFMaBiSmAd,

 

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