Retrieving JSON Data from JSON Datatype in Mysql with PHP

[Gilead]

I'm looking for the right syntax to pull json data from a mysql database with php.

$sql = " SELECT oilchange FROM vehicledata";      We select the json column from the table or do we do each individual field? 
$result = mysqli_query($ms, $sql);

if(mysqli_num_rows($result)>0)
{
 while($row =mysqli_fetch_assoc($result))
 {
  echo " ID:".$row['ID'] . "|Service Date:".$row['ServiceDate']. "|Service Location:".$row['ServiceLocation']. "|Labor:".$row['Labor']. "|Mileage:".$row['Mileage']. "|Oil Brand:".$row['OilBrand']. "|Oil Price:".$row['OilPrice']. "|Filter Brand:".$row['FilterBrand']. "|Filter Price:".$row['FilterPrice']. "|Purchase Place:".$row['PurchaseLocation'],";";  This is where I'm having the most problems. The ; and the | are delimiters so I can put the data into a usable format.
 }

What am I missing?
Thanks!
}

[Steven29]

$sql = " SELECT oilchange FROM vehicledata";
$result = mysqli_query($ms, $sql);

if(mysqli_num_rows($result)>0)
{
while($row =mysqli_fetch_assoc($result))
{
$data=unserialize ($row['oilchange']);
echo " ID:".$data['ID'] ....
}
}

[Gilead]

Thanks Steven29. I'll implement it.

[Gilead]

It's giving me an error Notice: unserialize(): Error at offset 0 of 256 bytes. So somehow the length is wrong. Any thoughts?

[NickMNS]

There is likely something in the object that your are trying to serialize as JSON that is not serializable. In other words there are characters that JSON doesn't like.

[Gilead]

Odd, because it inserted just fine.


$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);
$json= json_encode([$id => $arr]);
echo 'Here is our new JSON';
print_r($json);


$sql= "INSERT INTO vehicle (OilChange) VALUES ('$json')";
$result=mysqli_query($ms, $sql);
if(!$result)
{
echo'Oil Change Not Inserted! '. mysqli_error($ms);
}
else
{
echo"Oil Change Inserted!";
}

[NickMNS]

What format is $servicedate in? is it a string?

[Gilead]

yes, a string.

[NickMNS]

Then I don't know. Maybe someone with more mySql experience will be able to help.

[Gilead]

Thanks for trying Nick!