user6388032 user6388032 - 4 months ago 11
MySQL Question

How to set object property to null in Mysql

I am uploading a list of Objects to MySQL.
Some of the objects do not contain the same number of variables. For example

Objectt1 {
property1
property 2
}

Objectt2 {
property1
property2
property3
}

Objectt3 {
property1
}


My problem is that Object3 in mysql is being given a property2 and property3 instead of NULL. the value is being taken from the Object2. How can I make object 3's propery2 and property3 null? The php code is as follows: (I think I understand why it does this, because the variable is isset already in a previous run of the loop. But I don't know how to fix it.)

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);


if($_SERVER["REQUEST_METHOD"] == "POST") {
require 'connection.php';
uploadstart();
}

function uploadstart() {
global $connect;

$json = $_POST["objectlist"];

//Remove Slashes
if (get_magic_quotes_gpc()){
$json = stripslashes($json);
}

$createworkoutquery = $connect->prepare("INSERT INTO objectTable
(id, prop1, prop2, prop3)
VALUES (?, ?, ?, ?)");

$createworkoutquery->bind_param("ssss", $ID, $prop1, $prop2, $prop3) ;
//convert json object to php associative array
$data = json_decode($json, true);

//Util arrays to create response JSON
$a=array();
$b=array();

// loop through the array
foreach ($data as $row) {
// get the list details
$ID = $row["id"];
$prop1 = $row["prop1"];

if (isset($row["prpop2"])) {
$prop2 = $row["prop2"];
}

if (isset($row["prop3"])) {
$prop3 = $row["prop3"];
}


// execute insert query
$result = $createworkoutquery->execute();

//if insert successful.. plug yes and woid else plug no
if($result){
$b["id"] = $ID;
$b["status"] = 'yes';
array_push($a,$b);
} else {
$b["id"] = $ID;
$b["status"] = 'no';
array_push($a,$b);
}
}

echo json_encode($a);

//close connection
mysqli_close($connect);

}
?>

Answer

Assign null to the missing properties on each iteration so the previous value doesn't get bound to the query:

foreach($data as $row){
    $ID = $row['id'];
    $prop1 = isset($row['prop1'])? $row['prop1']: null;
    $prop2 = isset($row['prop2'])? $row['prop2']: null;
    $prop3 = isset($row['prop3'])? $row['prop3']: null;

    $result = $createworkoutquery->execute();
    ...
}