How to store API data (array) into database with php

Question

I am having error trying to store data into a database. I need help on how best to fix this my code, so that my array content can be stored in a my database. When I tried to echo the content, it worked not to store the data in my database is the problem. Here is my code:

foreach ($data['players'] as $myp) {
//    echo $myp["name"]."<br>";
    $name = $myp['name'];
    $posi = $myp['position'];
    $nation = $myp['nationality'];
    $market = $myp['marketValue'];
}

//insert into mysql table
mysql_select_db($db, $conn); //database and connection 

$date = date('Y-m-d H:m:s'); //date stamp formatting
//sql query that insert the user info into the database
$sql =  "INSERT INTO staffdb ( Name, Position, Nationality, Market, Created)       
        VALUES($name, $posi, $nation, $market, $date)";
echo $name . "<br>";
//if the connection is sucessful, display regards message
if (mysql_query($sql, $conn)) {
    echo "Thank you <br/>";
} else {   //if the connection is not established
    die('Error: ' . mysql_error());
}

Here is the error I was getting: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Costa, Centre Forward, Spain, 45,000,000 €, 2017-03-31 23:03:04)' at line 2

In the error it printed the right value which i would like to be instead stored in the database.

Kindly correct my SQL part so that I can get my foreach data into my database.

Thanks alot!

score 0 · Answer 1 · answered Mar 31 '17 at 21:52

0

You need quotes around your values. In addition you should use PDO and bind those values.

$sql =  "INSERT INTO staffdb ( Name, Position, Nationality, Market, Created)       
        VALUES('$name', '$posi', '$nation', '$market', '$date')";

answered Mar 31 '17 at 21:52

Matt

5,315
1
30
57

Thanks solved... – Addey Apr 01 '17 at 21:05

score 0 · Answer 2 · answered Mar 31 '17 at 21:52

0

You need single quotes around your field names:

$sql =  "INSERT INTO staffdb ( Name, Position, Nationality, Market, Created)       
    VALUES('$name', '$posi', '$nation', '$market', '$date')";

You should read up on SQL injection as your code is very vulnerable.

Also, you should move the closing } tag from your foreach loop so it comes at the end of your code. That way each item in your array will be added to your database.

answered Mar 31 '17 at 21:52

Chris

4,672
13
52
93

Thanks solved... – Addey Apr 01 '17 at 21:06
Hi, thanks. I moved the closing } as you said but only the first 4 elements that are add. Kan you help me on how to get every element stored? Also after i moved the mysqli_select_db to either above or below the foreach, it gave error: Thank you Thank you Thank you Thank you Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Golo Kanté', 'Defensive Midfield', 'France', '30,000,000 €', '2017-04-0' at line 2. Am pretty new in php, kan you help me thanks.. – Addey Apr 02 '17 at 05:56

Gedweb · Answer 3 · 2017-03-31T22:05:06.017

0

Just wrap values in query into quotes:

$sql = "INSERT INTO staffdb ( `Name`, `Position`, `Nationality`, `Market`, `Created`)
      VALUES('$name', '$posi', '$nation', '$market', '$date')";

Very recommend, how it's possible, move into new mysqli or PDO extension.

edited Mar 31 '17 at 22:05

answered Mar 31 '17 at 21:58

Gedweb

697
1
6
22

Thanks solved... – Addey Apr 01 '17 at 21:06

Henk · Accepted Answer · 2017-04-06T14:47:32.883

you need quotes around your values, plus you might want to use mysqli (as far as i know mysql_query etc. is deprecated. Also with your code only the last player is inserted.. don't know if that's what you want?

in your foreach you could do something like $q .= "('$name', '$posi', '$nation', '$market', '$date'),";

And then after the foreach loop removing the last comma $q = rtrim($q, ",");

And then the sql query: $sql = "INSERT INTO staffdb ( Name, Position, Nationality, Market, Created)VALUES".$q;

EDIT: something like this:

$values = "";
$date = date('Y-m-d H:m:s');
foreach ($data['players'] as $myp) {
//    echo $myp["name"]."<br>";
    $name = $myp['name'];
    $posi = $myp['position'];
    $nation = $myp['nationality'];
    $market = $myp['marketValue'];

    $values .= "('$name', '$posi', '$nation', '$market', '$date'),";
}
//remove last comma
$values = substr($values, 0,-1);

//mysqli connection
$conn = mysqli_connect("host", "user", "password", "database");

//sql query that insert the user info into the database
$sql =  "INSERT INTO staffdb ( Name, Position, Nationality, Market, Created)       
        VALUES".$values;
//if the connection is sucessful, display regards message
mysqli_query($conn, $sql) or die('Error: ' . mysqli_error($conn));
echo "Thank you <br/>";

You are very correct, the sql only insert the last object into the db. That not what I want, I want all to be inserted. Any idea thanks? — Addey, Apr 01 '17 at 21:04
@Addey i gave my ideas already but i've updated my post with basically a copy paste answer. — Henk, Apr 06 '17 at 14:46

How to store API data (array) into database with php

4 Answers4