CODEWITHSUNDEEP
phpmysqljson
soum
soum

Reputation: 1159

appending data from an external json to a mysql table

I am trying to append data from an external json into another MYSQL table. I am familiar with php but not a pro in any shape or form. So if I am making some silly mistake I wouldnt be surprised at all.

This is what I am doing 

<?php
// Declaring database parameters as constants
define('DB_NAME', 'this has my db name');
define('DB_USER', 'this stores my user name');
define('DB_PASSWORD', 'my password');
define('DB_HOST', 'localhost');

$data_variable = file_get_contents("http://data.cityofnewyork.us/resource/a7hj-s5px.json");

//making database connection
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

//Checking for server connection and printing statements

if(!$link){
    die('could not connect:' . mysql_error());
}

$db_selct = mysql_select_db(DB_NAME, $link);

if(!$db_selct){
    die('Can not use : ' . DB_NAME . ':' .mysql_error());
}

//echo 'connection sucessful';

$data = json_decode($data_variable, true);

//Let me know I can turn this on to show how the array looks like.
//print_r(array_values($data));

foreach($date["noise"] as $new_data){

$var_one = $new_data[incident_zip];

mysql_query("INSERT INTO noise (zip_code) VALUES('$var_one')") or die (mysql_error());
}


?>

What am I doing wrong?

I have found a very similar solution here and pretty much following it but I am probably not doing something right

JSON to MYSQL - is JSON response formatted correctly - looping properly?

Here is the link to the page where I am seeing the error message http://soumghosh.com/otherProjects/phpDataOperation/test1.php

Error
Warning: Invalid argument supplied for foreach() in /home3/soum/public_html/otherProjects/phpDataOperation/test1.php on line 31

This is still at an investigation stage. I might get back to this problem to seek you guys opinion more on the architectural level.

Upvotes: 1

Views: 145

Answers (1)

Deepak
Deepak

Reputation: 6812

You could change the foreach() block to the following to avoid warnings and errors

if(isset($data)) {
    foreach($data as $new_data){
        $var_one = $new_data['incident_zip'];
        mysql_query("INSERT INTO noise (zip_code) VALUES('$var_one')") or die (mysql_error());
    }
} else {
  //log the error
}

Upvotes: 1

Related Questions