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!! :(

PHP Notice:  Undefined index: is_fav in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: is_new in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: description in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: is_sold in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: language in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: image in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: contact_no in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: is_pinned in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: user_adv_id in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: premium_tag in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: keywords in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: title in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: is_not_abusive in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: announce_date in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: user_id in /var/www/db/xcall.php on line 22
PHP Notice:  Undefined index: price in /var/www/db/xcall.php on line 22

Array
(
    [data] => Array
        (
            [0] => Array
                (
                    [is_fav] => 0
                    [is_new] => 1
                    [description] =>  blablabla
                    [is_sold] => 0
                    [language] => en
                    [image] => 
                    [contact_no] => +10000000
                    [is_pinned] => 1
                    [user_adv_id] => 234499
                    [premium_tag] => 1
                    [keywords] => 
                    [title] => oblabla 
                    [is_not_abusive] => 0
                    [announce_date] => 2012-03-01 19:10:16
                    [user_id] => 99221
                    [price] => 10
                    [main_image] => Array
                        (
                            [0] => http://example.com/images/12.jpg
                            [1] => http://example.com/images/13.jpg
                        )

                    [resize_image] => Array
                        (
                            [0] => http://example.com/images/resize/12.jpg
                            [1] => http://example.com/images/resize/13.jpg
                        )

                    [type] => user
                )

            [1] => Array
                (
                    [is_fav] => 0
                    [is_new] => 1
                    [description] => blabla vblakdf
                    [is_sold] => 0
                    [language] => en
                    [image] => 
                    [contact_no] => +199443288
                    [is_pinned] => 0
                    [user_adv_id] => 1234238
                    [premium_tag] => 0
                    [keywords] => 
                    [title] =>  groupx for sale
                    [is_not_abusive] => 0
                    [announce_date] => 2012-01-28 00:08:10
                    [user_id] => 123
                    [price] => 000
                    [main_image] => Array
                        (
                            [0] => http://example.com/images/no-pic.png
                        )

                    [resize_image] => Array
                        (
                            [0] => http://example.com/images/resize/no-pic.png
                        )

                    [type] => user
                )

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!

Recommended Answers

All 7 Replies

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:

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

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:

  1. an error message
  2. an empty array
  3. an array like above but with empty values
  4. other?

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

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!

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',
  ),
)

Hi,

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

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

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.