php - 使用 PDO 将 JSON 解码数据插入 mysql

Question

数据库结构:

编码成JSON:

我使用以下代码将一些 JSON 编码的动漫信息存储在文本文件中:

        $info = array(
            'mal_id' => $id,
            'image' => $src,
            'title' => $title,
            'alt_title_eng' => $alt_eng,
            'alt_title_syn' => $alt_syn,
            'type' => $type,
            'status' => $status,
            'episodes' => $ep,
            'genres' => $genres,
            'rank' => $rank,
            'synopsis' => $syno,
            'modified' => date("Y-m-d H:i:s"),
            'user' => 'system'
            );
    file_put_contents("scrap/anime-$id.txt", json_encode($info));

文本文件的内容是:

{"mal_id":18679,"image":"http:\/\/localhost\/images\/anime\/5\/54379.jpg","title":"Kill la Kill","alt_title_eng":" KILL la KILL","alt_title_syn":" KLK","type":" TV","status":" Currently Airing","episodes":25,"genres":"Action, Comedy, School","rank":421,"synopsis":"The story is set on a high school that the student council president Satsuki Kiryuuin rules by force....","user":"system"}

解码JSON并插入数据库

我正在尝试对内容进行 json_decode 并将值插入 mysql。我没有成功的代码是这样的:

$id= '18679';
$TABLE_PREFIX = 'exp_';
$dbh = new PDO('mysql:host='.$dbhost.';dbname='.$database.';charset=utf8', $dbuser, $dbpass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


$JSON = json_decode(file_get_contents("scrap/anime-$id.txt"));  
$stmt = $dbh->prepare("INSERT INTO {$TABLE_PREFIX}animedb
(mal_id, image, title, alt_title_eng, alt_title_syn, type, status, episodes, genres, rank, synopsis, modified, user)
    values(:mal_id, :image, :title, :alt_title_eng, :alt_title_syn, :type, :status, :episodes, :genres, :rank, :synopsis, :modified, :user)
    on duplicate key update title= :title, image= :image, alt_title_eng= :alt_title_eng, alt_title_syn= :alt_title_syn, status= :status, episodes= :episodes, genres= :genres, rank= :rank, synopsis= :synopsis, modified= :modified, user= :user");

$user = 'system';
$modified = 'NOW()';
$stmt->bindParam(':mal_id', $id);  
$stmt->bindParam(':image', $JSON->image, PDO::PARAM_STR);
$stmt->bindParam(':title', $JSON->title, PDO::PARAM_STR);      
$stmt->bindParam(':alt_title_eng', $JSON->alt_title_eng, PDO::PARAM_STR);     
$stmt->bindParam(':alt_title_syn', $JSON->alt_title_syn, PDO::PARAM_STR);     
$stmt->bindParam(':type', $JSON->type, PDO::PARAM_STR);     
$stmt->bindParam(':status', $JSON->status, PDO::PARAM_STR);     
$stmt->bindParam(':episodes', $JSON->episodes);     
$stmt->bindParam(':genres', $JSON->genres, PDO::PARAM_STR);     
$stmt->bindParam(':rank', $JSON->rank);     
$stmt->bindParam(':synopsis', $JSON->synopsis, PDO::PARAM_STR);     
$stmt->bindParam(':modified', $modified, PDO::PARAM_STR);     
$stmt->bindParam(':user', $user, PDO::PARAM_STR);     
$stmt->execute() ;

问题:

使用 try/catch，我只得到这个错误:

SQLSTATE[HY093]: Invalid parameter number

我已搜索此错误消息的解决方案，但找不到适合我的问题的解决方案。

Answer 1

您的 SQL 查询是否经过测试？否则，尝试

... on duplicate key update title= values(title), ...

代替

... on duplicate key update title= :title, ...