php - 如何在mysql数据库中插入json数据的特定元素

Question

如何使用 PHP 从 MySQL 数据库中的 json 格式数据插入所有“故事”(仅)。我想将所有故事插入一个字段中。请帮忙。

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();

上面的代码给我数据库中的空白字段。

编辑后是否可以将 'id' 和 'story' 存储在一个字段中，例如 id:123 Story:'xyzabc' 应存储为 123 tab xyzabc 换行符。所以最后我可以在一个字段中获取所有故事和相应的故事ID，其中故事和故事ID用制表符分隔

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

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

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

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

上面给出了所获取数据的所有 id，但我只想拥有故事的 id

Answer 1

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

$story 现在包含可以添加到数据库的完整故事。

编辑(问题编辑后)

看来您正在随机编码。注意自己的工作:

$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 (?, ?, ?)");

在 mysql 查询中，您打开 3 个括号，但仅关闭其中两个:您只能在 user1 之后打开一个括号。
使用 if( ! $stmt = $db->prepare(...) ) die("{$db->e​​rror}"); 检索 prepare 错误。

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

您想要传递给 bind_parameter 哪些变量？您想要在三个字段中插入值然后传递四个参数？或者 $name 包含绑定(bind)类型？我怀疑事实是否如此。仔细阅读bind_param syntax然后以类似的方式更改绑定(bind):$stmt->bind_param('sss', $name, $accessToken, $data);。 “类似方式”意味着您不必复制粘贴，而是理解语法并根据您的上下文使用它。

$result_insert = mysql_query($sql);

这个mysql_query是什么？ 怀旧装饰？删除它。

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

你真的读过“准备语句”W3教程吗？本教程中哪里有 ->query() 方法？无处。对于准备好的语句，您必须使用 $stmt->execute() (不带参数)。

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

您无法以这种方式打印出类 mysqli_stmt ($stmt) 的对象:只需将其删除并仅回显 $db->e​​rror

}

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