Joshua Miller Joshua Miller - 2 months ago 6
JSON Question

how to only insert an as many items are in my array with php to mysql from json

I have a php file that i am inserting data from a json into a mysql database, and i am using a foreach() to list the tech's and then insert them into mysql. my problem is it seems to just insert 3 of the same things into the database and not 1 for each tech.

Example

id 1, 1, 1
name mike, mike, mike
number 10, 10, 10

the above should be more like

id 1, 2, 3
name mike, sandy, joe
number 10, 11, 12


I can see what i am trying to do but when i google i get nothing but how to count with count().

$ijobid = $data['invoice']['jobId'];
foreach($data['jobAssignments'] as $chunk){
$jatech = $chunk['technician'];
$jatechid = $jatech['id'];
$jatechname = $jatech['name'];
$jasplit = $chunk['split'];
$jadriving = $chunk['totalDrivingHours'];
$jaworking = $chunk['totalWorkingHours'];
$jaassigned = $chunk['assignedOn'];
$jatechstatus = $chunk['status'];
$jatechfinished = array($jatechid, $jatechname);
$jobassignreults[] = $jatechfinished;
}
foreach($jatech as $key => $techs){
$sql = "INSERT INTO `techtable` (`ijobid`,`jtid`,`jtname`) VALUES ('$ijobid', '$jatechid', '$jatechname')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

}


this is my php code, i know it works to connect because i get data but it doesn't seem to work correctly. the tech data in the json is not static and can be different every time i pass it, so today it could list 1 tech tomorrow 5.

Thank you in advance.

Edit - here is the json section i am pulling from:

"jobAssignments": [
{
"id": 15797,
"jobId": 15792,
"jobNumber": "15792",
"technician": {
"id": 156,
"name": "Mike"
},
"split": 100,
"totalDrivingHours": 1680,
"totalWorkingHours": 7680,
"assignedOn": "2015-11-05T09:08:22.5680879",
"status": "Done"
}
],

Answer

You have 2 foreach loops. The first consumes all the input data so leaving only the last occurance in the scalar variables. Your second foreach loops around $jatech which is the LAST version of $jatech = $chunk['technician'];

This should work a little better

$ijobid = $data['invoice']['jobId'];
foreach($data['jobAssignments'] as $chunk){
    $jatech = $chunk['technician'];
    $jatechid = $jatech['id'];
    $jatechname = $jatech['name'];
    $jasplit = $chunk['split'];
    $jadriving = $chunk['totalDrivingHours'];
    $jaworking = $chunk['totalWorkingHours'];
    $jaassigned = $chunk['assignedOn'];
    $jatechstatus = $chunk['status'];
    $jatechfinished = array($jatechid, $jatechname);
    $jobassignreults[] = $jatechfinished;

    $sql = "INSERT INTO `techtable` (`ijobid`,`jtid`,`jtname`) 
                VALUES ('$ijobid', '$jatechid', '$jatechname')";
    if (mysqli_query($conn, $sql)) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
}

And this would be more efficient

$ijobid = $data['invoice']['jobId'];

$sql = "INSERT INTO `techtable` (`ijobid`,`jtid`,`jtname`) 
                VALUES (?,?,?)";
$stmt = $conn->prepare($sql);

foreach($data['jobAssignments'] as $chunk){
    $jatech = $chunk['technician'];
    $jatechid = $jatech['id'];
    $jatechname = $jatech['name'];
    $jasplit = $chunk['split'];
    $jadriving = $chunk['totalDrivingHours'];
    $jaworking = $chunk['totalWorkingHours'];
    $jaassigned = $chunk['assignedOn'];
    $jatechstatus = $chunk['status'];
    $jatechfinished = array($jatechid, $jatechname);
    $jobassignreults[] = $jatechfinished;

    $stmt->bind_param('sss', $ijobid, $jatechid, $jatechname);
    if ( $stmt->execute() ) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

The only thing you may need to check is that all 3 datatypes are in fact strings.

 $stmt->bind_param('sss', $ijobid, $jatechid, $jatechname);
Comments