Insert json data into mysql table

Question

jmishal 0 Newbie Poster

8 Years Ago

Hi,
I know parsing json data has been discussed lots but what I want is probably a little simpler
I need a little editing to the bleow php script to work as convert json data and push it into MySQL table since the script work as reading the json data only! I'm not much familiar with php coding.

Any help is appreciated in advance.

<?

$item_string = '{"para":{"Psize":"1","Doffset":"now","Did":"33","lang":"en","p":1,"tkn":"cls","subctegry":"14"},"req":"adv\/get_adv"}';
$ch = curl_init('http://example.com/webd/');
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_POSTFIELDS, $item_string);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
    'Content-Type: application/json',
    'Content-Length: ' . strlen($item_string))
);

$result = curl_exec($ch);
$item = json_decode($result, true);



 //Database Connection
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("sale") or die(mysql_error());

mysql_query("INSERT INTO wdwd VALUES ('".$item['is_fav']."','".$item['is_new']."','".$item['description']."','".$item['is_sold']."','".$item['language']."','".$item['image']."','".$item['contact_no']."','".$item['is_pinned']."','".$item['user_adv_id']."','".$item['premium_tag']."','".$item['keywords']."','".$item['title']."','".$item['is_not_abusive']."','".$item['announce_date']."','".$item['user_id']."','".$item['price']."','".$item['pinned_ads']."','".$item['total_pages']."','".$item['current_page']."','".$item['total_ads_count']."')");
     mysql_close();
     echo "Data Berhasil Disimpan...";

     //print out

 #header('Content-Type: text/plain; charset=utf-8');
print_r($item);

?>

and here i got ERROR with the output .. and also i've check the database rows it's empty!! :(

and here my database table

mysql> desc wdwd;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | YES  |     | NULL    |       |
| is_fav          | varchar(20) | YES  |     | NULL    |       |
| is_new          | varchar(20) | YES  |     | NULL    |       |
| description     | varchar(20) | YES  |     | NULL    |       |
| is_sold         | varchar(20) | YES  |     | NULL    |       |
| language        | varchar(20) | YES  |     | NULL    |       |
| image           | varchar(20) | YES  |     | NULL    |       |
| contact_no      | varchar(20) | YES  |     | NULL    |       |
| is_pinned       | varchar(20) | YES  |     | NULL    |       |
| user_adv_id     | varchar(20) | YES  |     | NULL    |       |
| premium_tag     | varchar(20) | YES  |     | NULL    |       |
| keywords        | varchar(20) | YES  |     | NULL    |       |
| title           | varchar(20) | YES  |     | NULL    |       |
| is_not_abusive  | varchar(20) | YES  |     | NULL    |       |
| announce_date   | varchar(20) | YES  |     | NULL    |       |
| user_id         | varchar(20) | YES  |     | NULL    |       |
| price           | varchar(20) | YES  |     | NULL    |       |
| main_image      | varchar(20) | YES  |     | NULL    |       |
| resize_image    | varchar(20) | YES  |     | NULL    |       |
| type            | varchar(20) | YES  |     | NULL    |       |
| pinned_ads      | varchar(20) | YES  |     | NULL    |       |
| total_pages     | varchar(20) | YES  |     | NULL    |       |
| current_page    | varchar(20) | YES  |     | NULL    |       |
| total_ads_count | varchar(20) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

any help on this would be appreciated-

Thanks!

json mysql php

Edited 8 Years Ago by jmishal

3 Contributors
7 Replies
3K Views
2 Months Discussion Span
Latest Post 8 Years Ago Latest Post by cereal

cereal 1,524 Nearly a Senior Poster

8 Years Ago

Hi, you can access the key by doing:

$item['data'][0]['is_fav']

For the first array, then repeat the same for $item['data'][1].... You should, also, use prepared statements with MySQLi or PDO, the MySQL API has been removed from the latest PHP release.

To increase performance you can also create a batch insert. See:

https://www.daniweb.com/programming/web-development/code/495371/insert-multiple-records-with-pdo-prepared-statement

Edited 8 Years Ago by cereal

cereal 1,524 Nearly a Senior Poster

8 Years Ago

Could you show $result as json string? That way I can test it and be sure about my suggestion.

still the ERROR thier and the rows are empty

What happens if the curl request returns an empty result set, you will get:

an error message
an empty array
an array like above but with empty values
other?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jmishal 0 Newbie Poster · Answer 1 · 2015-11-28T16:12:56+00:00

thank you for your efort , as per your advice i've added

$item['data'][0]['is_fav']

For the first array but i can see the first line of the error has been disappeared
PHP Notice: Undefined index: is_fav in /var/www/db/xcall.php on line 22
but still the ERROR thier and the rows are empty

jmishal 0 Newbie Poster · Answer 2 · 2015-11-28T17:07:03+00:00

jmishal 0 Newbie Poster

8 Years Ago

ok I've attached you the real php file that I've using it.

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

call_2.php.zip (0.87 KB)

Edited 8 Years Ago by jmishal

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 3 · 2015-11-30T00:46:59+00:00

Ok, I see that the data index size depends on the pagesize value, if you don't set it you get 10 results, while, if the request is wrong you get an error array like below:

Array
(
    [error] => Array
        (
            [code] => -3
            [message] => no record found
        )

)

Also you cannot rely on response headers as they always return 200 OK, so the only way to know if request was fine is to check the error index is not set, only after this you can loop the data index and build your insert queries, so you should start with array_key_exists():

$item = json_decode($result, true);

if(array_key_exists('error', $item))
{
    # log the error, show an error message or something else
}

else
{
    # execute the insert queries here
}

Docs: http://php.net/array-key-exists
Note: to loop the data index you can do something like this:

foreach($item['data'] as $row)
{
    echo $row['is_fav'];
}

So you don't have to know how many results there are in the data index. If this is not what you were searching for then, please, explain it better, bye!

anupamjamatia 0 Newbie Poster · Answer 4 · 2016-02-13T14:44:10+00:00

Is it possible to insert only "story" filed of json data in the mysql database.
Please help me out.

array (
  0 => 
  array (
    'story' => 'Anupam Jamatia shared Filmydrama\'s video.',
    'created_time' => 
    array (
      'date' => '2016-02-12 14:05:15.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154545385862892',
  ),
  1 => 
  array (
    'story' => 'Anupam Jamatia shared Kya Yehi Hain Acche Din?\'s video.',
    'created_time' => 
    array (
      'date' => '2016-02-12 03:34:32.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154544563382892',
  ),
  2 => 
  array (
    'story' => 'Anupam Jamatia shared a link.',
    'created_time' => 
    array (
      'date' => '2016-02-12 03:28:09.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154544555572892',
  ),
  3 => 
  array (
    'message' => 'R.I.P Jaihind',
    'story' => 'Anupam Jamatia shared The Hindu\'s post.',
    'created_time' => 
    array (
      'date' => '2016-02-11 07:46:59.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154542597202892',
  ),
  4 => 
  array (
    'story' => 'Anupam Jamatia posted from Change.org.',
    'created_time' => 
    array (
      'date' => '2016-02-11 05:09:08.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154542373792892',
  ),
  5 => 
  array (
    'message' => 'Johnson & Johnson Finally Admits: Their Baby Products Contain Cancer-Causing Chemicals | ',
    'created_time' => 
    array (
      'date' => '2016-02-11 01:38:33.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154542027992892',
  ),
  6 => 
  array (
    'story' => 'Anupam Jamatia shared a link.',
    'created_time' => 
    array (
      'date' => '2016-02-09 17:16:07.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154538723082892',
  ),
  7 => 
  array (
    'story' => 'Anupam Jamatia shared The Guardian\'s video.',
    'created_time' => 
    array (
      'date' => '2016-02-09 01:45:30.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154537304507892',
  ),
  8 => 
  array (
    'story' => 'Anupam Jamatia shared The Frustrated Engineer\'s video.',
    'created_time' => 
    array (
      'date' => '2016-02-08 01:18:59.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154534663442892',
  ),
  9 => 
  array (
    'story' => 'Anupam Jamatia shared 24 Ghanta\'s post.',
    'created_time' => 
    array (
      'date' => '2016-02-07 15:03:28.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154533473037892',
  ),
  10 => 
  array (
    'story' => 'Anupam Jamatia shared a link.',
    'created_time' => 
    array (
      'date' => '2016-02-07 14:54:39.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154533459592892',
  ),
)

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 5 · 2016-02-14T20:37:24+00:00

Hi,

it should be possible, try by decoding the json data to array and use array_column(), see the examples in the documentation:

http://php.net/array-column

If you need more help, open a new thread, bye!