Insert Json data into mysql database with json datatype using php

Question

I have form data coming in from a UNITY3d WWWForm to my php file. I made a column with the json file type, now I want to store that string in the database. I also want the id field to be the key for the array of the rest of the array.

include('dbconfig.php');

$id= $_POST['ID'];
$servicedate=$_POST['ServiceDate'];
$servicelocation=$_POST['ServiceLocation'];
$mileage=$_POST['Mileage'];
$labor=$_POST['Labor'];
$oilbrand=$_POST['OilBrand'];
$oilprice=$_POST['OilPrice'];
$filterbrand=$_POST['FilterBrand'];
$filterprice=$_POST['FilterPrice'];
$oilfilterpurchaselocation=$_POST['PurchasePlace'];


$arr = array('Service Date' => $servicedate, 
'Service Location' => $servicelocation,                                                                          
'Mileage' => $mileage, 
'Labor' => $labor, 
'Oil Brand' =>  $oilbrand,
'Oil Price' => $oilprice, 
'Filter Brand' => $filterbrand ,
'Filter   Price' => $filterprice ,
'Purchase Place' =>$oilfilterpurchaselocation);
$json= json_encode($id => $arr); //Is this part right?
echo 'Here is our new JSON';
echo  $json;

$var= $db->prepare("INSERT into json VALUES(?,?,?,?,?)";
foreach($data as $row)
{
  $var->bindParam(1, $row['id']);
  $var->bindParam(2, $row['']);
  $var->bindParam(3, $row['']);
  $var->execute();
}

Is this more of what I need? Thanks!

`$json= json_encode($id => $arr); //Is this part right?` - no, it's not right. You have to supply an array to `json_encode()`. This would be correct: ``$json= json_encode([$id => $arr]);` — GrumpyCrouton, Oct 16 '18 at 18:39
Since there's only one set of values per JSON string, there's no need to index by ID. You probably just want to add `'ID' => $id` to your array and then use `json_encode($arr)`. — Alex Howansky, Oct 16 '18 at 18:41
Also, your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Oct 16 '18 at 18:42
I wanted to have the id be the key and the array to be the value as an added layer of protection so that the data will not be mixed up with any other entry in the table. — Ken Gordon, Oct 16 '18 at 19:11

score 0 · Answer 1 · answered Oct 16 '18 at 18:53

0

Your all data pass in array $arr, you can simple use this code.

$json= json_encode($arr);

don't use echo check your json data in print_r($json);

answered Oct 16 '18 at 18:53

D.priye

32
7

score 0 · Accepted Answer · edited Oct 29 '18 at 21:37

I looked at the code yesterday and I had forgotten to add the quotes in the value part of the insert. When I ran it, I got a different error, so I knew I was making progress! As it turned out and someone let me know if this is right, I had to change all the columns to have a default value. When I changed them all to NULL, I was able to insert the json data. If anyone else is struggling with this issue, here is the insert command that you need.

 $sql= "INSERT INTO column_name (name of parameter) VALUES ('$json')";
 $result = mysqli_query($ms, $sql);

 if(!$result)
 {
     echo' Item Not Inserted! '. mysqli_error($ms);
 }
 else
 {
     echo"Item Inserted!";
 }

Make sure you do a json_encode to make a json object that can be placed in the database.

Insert Json data into mysql database with json datatype using php

2 Answers2