I pulled the json into the php object and started putting data from that object into the database. I have 20 users in json and everyone succeded to go into the database except one which surname is O'Carolan. I think that error is in that single quote, smth about that. I read everything about sql injection and tried everything i found here on stackoverflow with the similar errors and still doesnt work. I tried with the PDO also and prepared statements and still doesnt work. Here I always get an error: 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 ' 'male')' at line 2. Also, when printing out the users from json it prints them properly and everything is ok there, just that 3rd user O'carolan wont go into to database. My json is at http://dev.30hills.com/data.json and my code is:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "30hills";
// Create connection
$conn = new mysqli($servername, $username, $password, $db);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$json_string = 'http://dev.30hills.com/data.json';
$jsondata = file_get_contents($json_string);
$obj = json_decode($jsondata, false);
$elementCount = count($obj);
for ($x = 0; $x < $elementCount; $x++) {
$id = $obj[$x]->id;
$firstname = $obj[$x]->firstName;
$surname = $obj[$x]->surname;
//if (preg_match('/'.$special_chars.'/', $surname)){
// $surname = str_replace("'","",$surname);
//}
$age = $obj[$x]->age;
$gender = $obj[$x]->gender;
echo $id;
echo " ";
echo $firstname;
echo " ";
echo $surname;
echo "<br>";
mysqli_query($conn, "INSERT INTO user (`id`, `firstName`, `surname`, `age`, `gender`)
VALUES($id, '$firstname', '" . $surname . "', $age, '$gender')")
or die(mysqli_error($conn));
?>