How to insert particular element of json data in mysql database

Question

How to insert all the "story" (only) from the json formated data in the MySQL database using PHP. I would like to insert all the story in a single field. Please help.

array (
  0 => 
  array (
    'story' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh 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' => 'akhilesh shared a link.',
    'created_time' => 
    array (
      'date' => '2016-02-07 14:54:39.000000',
      'timezone_type' => 1,
      'timezone' => '+00:00',
    ),
    'id' => '10154521329397892_10154533459592892',
  ),
)

$total_posts = array(); $array = json_encode($total_posts, true); $my_arr = json_decode($array, true); echo "<pre>"; $data = var_export($my_arr); echo "</pre>"; $story = $data; foreach( $my_arr as $row ) $story .= " {$row[story]}"; $story = trim( $story); $stmt = $db->prepare("INSERT INTO users1 (name, token, message) VALUES (?, ?, ?)"); $stmt->bind_param("sss",$name, $accessToken, $story); if ( !$stmt ) { printf('errno: %d, error: %s', $db->errno, $db->error); die; } else { $stmt->execute(); echo "New record created successfully !!"; } $stmt->close(); $db->close();

The above code give me blank filed in database.

After EDIT is it possible to store 'id' and 'story' in one field, like id:123 story:'xyzabc' should be stored as 123 tab xyzabc newline in one field. So finally I can get all the story and corresponding story-id in one field where story and story-id are separated with tab

$story = $data;
    foreach( $my_arr as $row ) 

            $story .= " $row[message]\n";

    $message_id = $data;
    foreach( $my_arr as $row ) 

            $message_id .= " $row[id]\n";

The above one is giving all the id's of the fetched data, but I would like to have the id of story only

Why you mix procedural and oo styles in db queries? What driver use you: mysql, mysqli or pdo? Also you have to bind or escape your query. — fusion3k
– fusion3k, Commented Feb 13, 2016 at 13:13
I don't think that $db->query() is valid mysql call! If you are starting now with php, please don't use mysql: it's derecated and completely removed in php v. 7! So, when you will upgrade php, you will have to rewrite all your code! Use mysqli or PDO instead. You also have to bind yr query, because yr string is complex and can contains chars in conflict with mysql syntax (i.e. single quotation). See a basic PDO tutorial — fusion3k
– fusion3k, Commented Feb 13, 2016 at 13:43
@fusion3k, Sorry its mysqli, i referred from w3schools.com/php/php_mysql_insert.asp — user1328007
– user1328007, Commented Feb 13, 2016 at 14:05
In the same tutorial, see at Prepared Statements chapter, and let me know if they help you — fusion3k
– fusion3k, Commented Feb 13, 2016 at 14:10

Community · Accepted Answer · 2023-11-21 15:10:29Z

0

$story = '';
foreach( $array as $row ) $story .= " {$row[story]}";
$story = trim( $story);

$story now contains the full story that you can add to database.

Edit (after question edit)

It seems you are coding randomly. Pay attention at your own work:

$total_posts = array();
$my_arr = json_decode($array, true);

echo "<pre>";
$data = var_export($my_arr);
echo "</pre>";

$stmt = $db->prepare("INSERT INTO users1 ((name, token, message) VALUES (?, ?, ?)");

In the mysql query you open 3 parenthesis, but close only two of them: you can open only one parenthesis after user1.
Use if( ! $stmt = $db->prepare(...) ) die("{$db->error}"); to retrieve prepare error.

$stmt->bind_param($name, $accessToken, $lastname, $data);

What variables you want pass to bind_parameter? You want insert values in three fields and then pass four parameters? Or $name contains types of binding? I doubt that this is so. Read careful the bind_param syntax and then change your bind in a similar way: $stmt->bind_param('sss', $name, $accessToken, $data);. “Similar way” means that you don't have to copy-and-paste, but understand the syntax and use it depending of your context.

$result_insert = mysql_query($sql);

What is this mysql_query? A nostalgic decoration? Remove it.

if ($db->query($stmt) === TRUE) {

You have really read the “Prepared statements” W3 tutorial? Where in this tutorial there are the ->query() method? Nowhere. With prepared statements you have to use $stmt->execute() (without arguments).

echo "New record created successfully !!";
} else {
    echo "Error: " . $stmt . "<br>" . $db->error;

You can't print out in this way an Object of class mysqli_stmt ($stmt): simply remove it and echo only $db->error

}

$stmt->close();
$db->close()

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Feb 13, 2016 at 12:31

fusion3k

11.7k4 gold badges27 silver badges47 bronze badges

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

21 Comments

user1328007 Over a year ago

Hi, I did the same but nothing happen in database, $total_posts = array(); $array = json_encode($total_posts, true); $my_arr = json_decode($array, true); echo "<pre>"; $data = var_export($my_arr); echo "</pre>"; $story = $data; foreach( $array as $row ) { $story .= " {$row[story]}"; } $story = trim( $story); $sql = "INSERT INTO users1 (message) VALUES ('{$story}')"; $result_insert = mysql_query($sql); if ($db->query($sql) === TRUE) { echo "New record created successfully !!"; } else { echo "Error: " . $sql . "<br>" . $db->error; } $db->close();

fusion3k Over a year ago

1) check the final $story content before adding it to db; 2) edit your question showing your db insertion code. We are not soothsayers...

fusion3k Over a year ago

@user1328007 edit your question adding code, please! Not in the comment.

fusion3k Over a year ago

@user1328007 at first sight it seems ok. but if you don't have result there are a problem. Sorry but I don't know your db structure, your complete array, the content of variables etc... you have to double-check all, step by step, to isolate the problem. It's a hard job, but it's the programmer's common job... also you can try to print out the query before executing it and manually paste it into phpmyadimin to see if it works. But all are only speculations, you have to find the error by yourself, I suspect.

user1328007 Over a year ago

will this solve the issue to store the multiple "story" in a single database field?

|

Collectives™ on Stack Overflow

How to insert particular element of json data in mysql database

1 Answer 1

Edit (after question edit)

21 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit (after question edit)

21 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related