dsa89029 2015-11-19 02:56
浏览 438
已采纳

JSON数组到MySQL数据库

I'm working on a project that involves a PHP script that calls an API and gets a JSON array. I then want to put this JSON array into a MySql database. The issue I am running into is that while the script executes without any errors or exceptions in the terminal, my database is not filling with any data.

I am running MySQL Workbench as my MySQL client and have created a schema called "team_data" into which I am attempting to input my JSON array. I have removed my API key for obvious reasons. Any ideas where I am going wrong here?

<?php

$con = mysql_connect("127.0.0.1","XXXXXX","XXXXXX") or die('Could not connect: ' . mysql_error());
    mysql_select_db("test1", $con);

$json = file_get_contents('team_data.json');    
$data = json_decode($json, true);

foreach($data as $row)
{

    $game = $data['nfl_game_id'];
    $team = $data['team'];
    $opponent = $data['opponent'];
    $totfirstdown = $data['totalfirstdown'];
    $totyds = $data['totyds'];
    $pyds = $data['pyds'];
    $ryds = $data['ryds'];
    $pen = $data['pen'];
    $penyds = $data['penyds'];
    $trnovr = $data['trnovr'];
    $pt = $data['pt'];
    $ptyds = $data['ptyds'];
    $ptavg = $data['ptavg'];

    $sql = "INSERT INTO Teams(nfl_game_id, team, opponent, totalfd, totyds, pyds, ryds, pen, penyds, trnovr, pt, ptyds, ptavg);
    VALUES('$game', '$team', '$opponent', '$totfirstdown', '$totyds', '$pyds', '$ryds', '$pen', '$penyds', '$trnovr', '$pt', '$ptyds', '$ptavg')";

    mysql_query($sql,$con);

}



?>
  • 写回答

2条回答 默认 最新

  • dongqiangou5724 2015-11-19 03:29
    关注
    • Error from your comment, after I suggested you check for errors on your query:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; VALUES('', '', '', '', '', '', '', '', '', '', '', '', '')' at line 1

    The error shows you where it starts right syntax to use near '; < right there.

    ... ptyds, ptavg); < see that semi-colon? Remove it. It's an end of statement character.

    However, you're doing foreach($data as $row) but not using $row.

    You need to change all $data['xxx'] to $row['xxx'] which is why your values are empty.

    If there are any characters that MySQL will complain about, then you will need to escape your data. Any which way, it's best that you do.

    As a bonus answer:

    Your present code is open to SQL injection. Use mysqli_* with prepared statements, or PDO with prepared statements.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥30 用arduino开发esp32控制ps2手柄一直报错
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿