Trouble inserting JSON object into MySQL table in PHP

Question

I am calling a Rest API in my PHP code which returns JSON. I want to save some parts of that into my database but for some reason, nothing is being stored. Here is what the JSON should look like:

{
"url": "http://myURL.com/fujifilm-mx1700.jpg",
"mimeType": "image/jpeg",
"width": 640,
"height": 480,
"byteSize": 100227,
"exif": {"YResolution": "72/1", "Tag0xa217": "2", "ResolutionUnit": "Inch", "Compression": "6", "Copyright": "", "Tag0xa300": "[ 3 ]", "Make": "FUJIFILM", "Flash": "no", "DateTime": "2000:09:02 14:30:10", "MaxApertureValue": "3.3", "YCbCrPositioning": "2", "XResolution": "72/1", "JPEGInterchangeFormatLength": "4354", "ExposureBiasValue": "0.0", "ExposureProgram": "Program Normal", "ShutterSpeedValue": "1/169", "ColorSpace": "1", "Tag0xa20e": "1087/1", "Tag0xa20f": "1087/1", "ExifImageWidth": "640", "DateTimeDigitized": "2000:09:02 14:30:10", "DateTimeOriginal": "2000:09:02 14:30:10", "BrightnessValue": "76/10", "CompressedBitsPerPixel": "2/1", "Interoperability_IFD_Pointer": "708", "FNumber": "7", "ApertureValue": "5.6", "Tag0xa210": "3", "FocalLength": "9.9", "Tag0xa000": "[ 48,49,48,48 ]", "ComponentsConfiguration": "[ 1,2,3,0 ]", "ExifImageHeight": "480", "ISOSpeedRatings": "125", "Model": "MX-1700ZOOM", "Software": "Digital Camera MX-1700ZOOM Ver1.00", "Orientation": "1", "Tag0xa301": "[ 1 ]", "JPEGInterchangeFormat": "856", "MeteringMode": "5", "ExifVersion": "[ 48,50,49,48 ]"}
}

And here is my PHP

$exif = file_get_contents('http://img2json.appspot.com/go/?url=http://myURL.com/$fileName');
$response = json_decode($response, true);

foreach($response['exif'] as $item) {

$iso = $item['ISOSpeedRatings'];
$shut = $item['ShutterSpeedValue'];

$sql="INSERT INTO EXIF (uniqueID, ISO, shutterSpeed)
VALUES('$id','$iso','$shut')";  

    if (!mysql_query($sql,$link)) {
    die('Error: ' . mysql_error());
    }
}

I want to store ISOSpeedRatings and ShutterSpeedValue in my database, but it they keep coming up empty.

I suspect its not working because I am not properly retrieving the data I want from the JSON, but I am not too sure what to do. Also, I think its worth noting that I am using AppFog so I had to make sure I had php_value allow_url_fopen 1 in my .htaccess, since I have no access to the php.ini file. Any help would be appreciated.

try var_dump($response); to see if the response is proper and how it's setup — CaptainCarl
– CaptainCarl, Commented Nov 9, 2013 at 8:11
This code is never actually seen, but called from another page to store data periodically. Where would I look for the result of the var_dump? — Amru E.
– Amru E., Commented Nov 9, 2013 at 8:15

willy · Accepted Answer · 2013-11-09 09:13:00Z

1

PHP string interpolation (the thing that makes it replace variable names with their values in a string) only works (as far as I know) in a double-quoted string. If I'm reading it right, and my brain still works this late at night, your problem is here:

    $exif = file_get_contents('http://img2json.appspot.com/go/?url=http://myURL.com/$fileName');

Because you are literally requesting the file '$filename' rather than whatever is actually in that variable. Replace the single-quotes with double-quotes and see if that fixes it.

Update: next problem:

    $response = json_decode($response, true);

You need to decode the $exif variable.

edited Nov 9, 2013 at 9:13

answered Nov 9, 2013 at 8:24

willy

1,50011 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Amru E. Over a year ago

I believe you are correct, so I fixed that. My table is still coming up empty though. I don't know why but I'm not very confident in the foreach loop I have (partially because there is only one instance of 'exif' that I'm looking for), and also the fact that I'm using file_get_contents.

willy Over a year ago

Are there supposed to be multiple of the objects you listed returned in the response, or just one? Because with that response what you'll be doing is looping through each key value of an associative array containing only the exif data. Just take the loop out altogether and use $response['exif']['ISOSpeedRatings'] etc..

Amru E. Over a year ago

Just one. Basically this file is called upon image upload to store the image, and to grab its exif data.

willy Over a year ago

I might've edited my last comment after you read it (late at night, brain not working, forgot a sentence). Just get rid of your loop and work directly on the $response['exif'] variable.

Amru E. Over a year ago

Trying that now. For some reason things broke and now my previous data that was being stored into a different table stopped working. I'll post back in a moment.

|

iCode · Accepted Answer · 2013-11-09 08:14:35Z

0

Try using urlencode() to prepare your urlstring.

answered Nov 9, 2013 at 8:14

iCode

1,4661 gold badge15 silver badges26 bronze badges

10 Comments

Amru E. Over a year ago

I did

$myUrl = urlencode('http://img2json.appspot.com/go/?url=http://myUrl.com/$fileName'); $exif = file_get_contents('$myUrl');

and I didn't notice a difference. The table is still empty.

iCode Over a year ago

try doing it like this: $exif = file_get_contents($myUrl);

Amru E. Over a year ago

No cigar. I'll use urlencode() but I think that perhaps the issue stems from something else.

iCode Over a year ago

Try it like this

$myUrl = urlencode('http://img2json.appspot.com/go/?url=http://myUrl.com/'.$fileName); $exif = file_get_contents($myUrl);

Amru E. Over a year ago

Nothing unfortunately

|

Collectives™ on Stack Overflow

Trouble inserting JSON object into MySQL table in PHP

2 Answers 2

10 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest