Something strange is happening here, normally i wouldn't have an issue, or maybe i am just tired. But MySQL won't accept my syntax to insert JSON into a table.
foreach ($newArray as $k => $v) {
$query = "SELECT `id` FROM `achievements` WHERE `achieveID`=".$v['ID']." LIMIT 1";
if ($result = mysqli_query($default, $query)) {
$row_cnt = mysqli_num_rows($result);
$row_cnt > 0 ? $parse = false : $parse = true;
mysqli_free_result($result);
}
$v['required'] == 'None' ? $req = $v['required'] : $req = json_encode($v['required'], JSON_FORCE_OBJECT);
if($parse) {
$result = mysqli_query($default, "INSERT INTO `achievements` VALUES (NULL, '".$v['cat']."', '".$v['ID']."', '".$v['title']."', '".$v['type']."', '".$v['factionD']."', '".$v['factionE']."', '".$v['text']."', '".$v['doneText']."', '".$v['points']."', '".$v['number']."', ".$req.", '".$v['rewards']."')") or die ('Unable to execute query. '. mysqli_error($default));
}
}
My code inserts everything else in fine, and does other rows but when it gets to a row that required json data (ie when $v['required'] does not equal to none) it does not insert it and gives me an error.
Unable to execute 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 '"0":"Into the Undertow","1":"Darkwater Abyss","2":"Primal Powers and Triton's Dr' at line 1
The JSON (from the json encode)
{"0":"Into the Undertow","1":"Darkwater Abyss","2":"Primal Powers and Triton's Dread","3":"Stirring Interstellar Waves"}
It seems like the double quotes are causing the issues?
EDIT
Ive re-wrote my code! Thanks for the tips!
$insert = $default->prepare("INSERT INTO achievements (catID, achieveID, achieveName, achieveType, factionDominion, factionExiles, achieveText, achieveCText, achievePoints, achieveNum, achieveReq, achieveRew) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$insert->bind_param("iissiissiiss", $cat, $id, $title, $type, $factionD, $factionE, $text, $doneText, $points, $number, $required, $rewards);
foreach ($newArray as $k => $v) {
$query = "SELECT id FROM achievements WHERE achieveID=".$v['ID']." LIMIT 1";
if ($result = $default->query($query)) {
$row_cnt = $result->num_rows;
$row_cnt > 0 ? $parse = false : $parse = true;
}
$v['required'] == 'None' ? $req = $v['required'] : $req = json_encode($v['required'], JSON_FORCE_OBJECT);
if($parse) {
$cat = $v['cat'];
$id = $v['ID'];
$title = $v['title'];
$type = $v['type'];
$factionD = $v['factionD'];
$factionE = $v['factionE'];
$text = $v['text'];
$doneText = $v['doneText'];
$points = $v['points'];
$number = $v['number'];
$required = $req;
$rewards = $v['rewards'];
$insert->execute();
}
}
$default->close();
And its working perfectly, it was really fast too, it added over 2000 rows in a few seconds! I am pretty sure there is a cleaner way to write it but first time using prepare!
