2

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

}



?>
davejal
  • 6,009
  • 10
  • 39
  • 82
Jake
  • 23
  • 2

2 Answers2

2
  • 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.

Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • If prepared statements are too much for you, I'd at least switch to mysqli and sanitize user input. Read more: http://stackoverflow.com/a/33793719/1564919 – jaggedsoft Nov 19 '15 at 03:47
  • That did the trick, thanks. Now I am running into one more issue (forgive me I'm completely new to MySQL). How can I create a table in SQL to enter this JSON data into? – Jake Nov 19 '15 at 03:54
  • Thanks for the help, was able to figure it out. – Jake Nov 19 '15 at 03:59
0

First let's see whether your command successfully retrieved the JSON data.

var_dump($data);

Let's place that right after the line where we json_decode the data.

If the JSON data looks good in our array, then the next thing for us to check would be the SQL (maybe there are required columns that aren't receiving values or other constraint, etc.)

John Doe
  • 905
  • 8
  • 9
  • Thanks for your response. After putting that line in my script, the JSON array looks good. See below for the first element in the array: array(28) { [0]=> array(13) { ["nfl_game_id"]=> int(2009100406) ["team"]=> string(2) "NE" ["opponent"]=> string(3) "BAL" ["totfd"]=> int(25) ["totyds"]=> int(319) ["pyds"]=> int(234) ["ryds"]=> int(85) ["pen"]=> int(5) ["penyds"]=> int(41) ["trnovr"]=> int(1) ["pt"]=> int(3) ["ptyds"]=> int(115) ["ptavg"]=> int(25) } – Jake Nov 19 '15 at 03:19
  • Ok, let's next check the SQL. Let's do an echo of the $query variable just after it's set so we can see its contents. Then we might try pasting that SQL statement directly into the MySQL editor. It may be that MySQL has some constraints that cause it to reject what looks like a valid statement to us. – John Doe Nov 19 '15 at 03:41