I am trying to send Json data to sql using the below php code but nothing goes into sql and I can't figure out why. The Json is coming as POST from another server with the below information. Any help I would be great full for I have an example of the Json file if needed.
<?php
$con = mysql_connect("localhost","@username","@password") or die('Could not connect: ' . mysql_error());
mysql_select_db("job-complete", $con);
//read the json file contents
$jsondata = file_get_contents('php://input');
//convert json object to php associative array
$data = json_decode($jsondata, true);
//pull all job assignments
//set all data
$jobnumber = $data['jobNumber'];
$id = $data['businessUnit']['id'];
$bname = $data['businessUnit']['name'];
$campaign = $data['campagin']['name'];
$cname = $data['customer']['name'];
$castreet = $data['customer']['address']['street'];
$cacountry = $data['customer']['address']['country'];
$cacity = $data['customer']['address']['city'];
$castate = $data['customer']['address']['state'];
$cazip = $data['customer']['address']['zip'];
$castreetadd = $data['customer']['address']['streetAddress'];
$summary = $data['summary'];
$createdon = $data['createdOn'];
$scheduledon = $data['scheduledOn'];
$completedon = $data['completedOn'];
$start = $data['start'];
$end = $end['end'];
$duration = $data['duration'];
$charge = $data['noCharge'];
$ijobid = $data['invoice']['jobId'];
$jobassignresults = array();
$jatech = array(); //initiate variable
foreach($data['jobAssignments'] as $chunk){
$jatech = $chunk['technician'];
$jatechid = $jatech['id'];
$jatechname = $jatech['name'];
$jatechfinished = array($jatechid, $jatechname);
$jobassignreults[] = $jatechfinished;
}
foreach($jatech as $key => $techs){
$sql = "INSERT INTO techtable ('ijobid','jtid','jtname') VALUES ($ijobid, $jatechid, $jatechname)";
}
$jasplit = $data['jobAssignments']['split'];
$jadriving = $data['jobAssignments']['totalDrivingHours'];
$jaworking = $data['jobAssignments']['totalWorkingHours'];
$jaassigned = $data['jobAssignments']['assignedOn'];
$jatechstatus = $data['jobAssignments']['status'];
$iid = $data['invoice']['id'];
$ictive = $data['invoice']['active'];
$ijobnumber = $data['invoice']['jobNumber'];
$isvalue = $data['invoice']['status']['value'];
$isname = $data['invoice']['status']['name'];
$inumber = $data['invoice']['numnber'];
$itax = $data['invoice']['tax'];
$isubtotal = $data['invoice']['subtotal'];
$itotal = $data['invoice']['total'];
$ibalance = $data['invoice']['balance'];
$iitemsid = $data['invoice']['items']['id'];
$iiteminvoiceid = $data['invoice']['items']['invoiceId'];
$iitemactive = $data['invocie']['items']['active'];
$iitemskuid = $data['invoice']['items']['sku']['id'];
$iitemskuname = $data['invoice']['items']['sku']['name'];
$iitemskudisplayname = $data['invoice']['items']['sku']['displayName'];
$iitemskutype = $data['invoice']['items']['sku']['type'];
$iitemsalestype = $data['invoice']['items']['salesType'];
$iitemdescription = $data['invoice']['items']['description'];
$iitemsqty = $data['invoice']['items']['qty'];
$iitemsunitrate = $data['invoice']['items']['unitRate'];
$iitemtotal = $data['invoice']['items']['total'];
$iitemtotalcost = $data['invoice']['items']['totalCost'];
$ipaymentsid = $data['invoice']['payments']['id'];
$ipaymentsinvoiceid = $data['invoice']['payments']['invoiceId'];
$iptypeid = $data['invoice']['payments']['type']['id'];
$iptypename = $data['invoice']['payments']['type']['name'];
$invoiceamount = $data['invoice']['amount'];
$invoicetotalamount = $data['invoice']['totalAmount'];
//insert into mysql table
$sql = "INSERT INTO jobdone (jnumb, bid, bname, campaign, cname, castreet, cacountry, cacity, castate, cazip, castreetadd, summary, createdon, scheduledon, completedon, start, end, duration, charge, jasplit, jadriving, jaworking, jaassigned, jatechstatus, iid, ictive, ijobid, ijobnumber, isvalue, isname, inumber, itax, isubtotal, itotal, ibalance, iitemsid, iiteminvoiceid, iitemactive, iitemskuid, iitemskuname, iitemskudisplayname, iitemskutype, iitemsalestype, iitemdescription, iitemsqty, iitemsunitrate, iitemtotal, iitemtotalcost, ipaymentsid, ipaymentsinvoiceid, iptypename, invoiceamount, invoicetotalamount) VALUE ($jnumb, $bid, $bname, $campaign, $cname, $castreet, $cacountry, $cacity, $castate, $cazip, $castreetadd, $summary, $createdon, $scheduledon, $completedon, $start, $end, $duration, $charge, $jasplit, $jadriving, $jaworking, $jaassigned, $jatechstatus, $iid, $ictive, $ijobid, $ijobnumber, $isvalue, $isname, $inumber, $itax, $isubtotal, $itotal, $ibalance, $iitemsid, $iiteminvoiceid, $iitemactive, $iitemskuid, $iitemskuname, $iitemskudisplayname, $iitemskutype, $iitemsalestype, $iitemdescription, $iitemsqty, $iitemsunitrate, $iitemtotal, $iitemtotalcost, $ipaymentsid, $ipaymentsinvoiceid, $iptypename, $invoiceamount, $invoicetotalamount)";
if(!mysql_query($sql,$con))
{
die('Error : ' . mysql_error());
}
?>
Edited File:
<?php
$servername = "localhost";
$username = "@username";
$password = "@password";
$dbname = "job-complete";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//read the json file contents
$jsondata = file_get_contents('php://input');
//convert json object to php associative array
$data = json_decode($jsondata, true);
//pull all job assignments
//set all data
$jobnumber = $data['jobNumber'];
$id = $data['businessUnit']['id'];
$bname = $data['businessUnit']['name'];
$campaign = $data['campagin']['name'];
$cname = $data['customer']['name'];
$castreet = $data['customer']['address']['street'];
$cacountry = $data['customer']['address']['country'];
$cacity = $data['customer']['address']['city'];
$castate = $data['customer']['address']['state'];
$cazip = $data['customer']['address']['zip'];
$castreetadd = $data['customer']['address']['streetAddress'];
$summary = $data['summary'];
$createdon = $data['createdOn'];
$scheduledon = $data['scheduledOn'];
$completedon = $data['completedOn'];
$start = $data['start'];
$end = $data['end'];
$duration = $data['duration'];
$charge = $data['noCharge'];
$ijobid = $data['invoice']['jobId'];
$jobassignresults = array();
$jatech = array(); //initiate variable
foreach($data['jobAssignments'] as $chunk){
$jatech = $chunk['technician'];
$jatechid = $jatech['id'];
$jatechname = $jatech['name'];
$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);
}
}
$jasplit = $data['jobAssignments']['split'];
$jadriving = $data['jobAssignments']['totalDrivingHours'];
$jaworking = $data['jobAssignments']['totalWorkingHours'];
$jaassigned = $data['jobAssignments']['assignedOn'];
$jatechstatus = $data['jobAssignments']['status'];
$iid = $data['invoice']['id'];
$ictive = $data['invoice']['active'];
$ijobnumber = $data['invoice']['jobNumber'];
$isvalue = $data['invoice']['status']['value'];
$isname = $data['invoice']['status']['name'];
$inumber = $data['invoice']['numnber'];
$itax = $data['invoice']['tax'];
$isubtotal = $data['invoice']['subtotal'];
$itotal = $data['invoice']['total'];
$ibalance = $data['invoice']['balance'];
$iitemsid = $data['invoice']['items']['id'];
$iiteminvoiceid = $data['invoice']['items']['invoiceId'];
$iitemactive = $data['invocie']['items']['active'];
$iitemskuid = $data['invoice']['items']['sku']['id'];
$iitemskuname = $data['invoice']['items']['sku']['name'];
$iitemskudisplayname = $data['invoice']['items']['sku']['displayName'];
$iitemskutype = $data['invoice']['items']['sku']['type'];
$iitemsalestype = $data['invoice']['items']['salesType'];
$iitemdescription = $data['invoice']['items']['description'];
$iitemsqty = $data['invoice']['items']['qty'];
$iitemsunitrate = $data['invoice']['items']['unitRate'];
$iitemtotal = $data['invoice']['items']['total'];
$iitemtotalcost = $data['invoice']['items']['totalCost'];
$ipaymentsid = $data['invoice']['payments']['id'];
$ipaymentsinvoiceid = $data['invoice']['payments']['invoiceId'];
$iptypeid = $data['invoice']['payments']['type']['id'];
$iptypename = $data['invoice']['payments']['type']['name'];
$invoiceamount = $data['invoice']['amount'];
$invoicetotalamount = $data['invoice']['totalAmount'];
//insert into mysql table
$sql = "INSERT INTO jobdone (jnumb, bid, bname, campaign, cname, castreet, cacountry, cacity, castate, cazip, castreetadd, summary, createdon, scheduledon, completedon, start, end, duration, charge, jasplit, jadriving, jaworking, jaassigned, jatechstatus, iid, ictive, ijobid, ijobnumber, isvalue, isname, inumber, itax, isubtotal, itotal, ibalance, iitemsid, iiteminvoiceid, iitemactive, iitemskuid, iitemskuname, iitemskudisplayname, iitemskutype, iitemsalestype, iitemdescription, iitemsqty, iitemsunitrate, iitemtotal, iitemtotalcost, ipaymentsid, ipaymentsinvoicedid, iptypename, invoiceamount, invoicetotalamount) VALUES ('$jnumber', '$id', '$bname', '$campaign', '$cname', '$castreet', '$cacountry', '$cacity', '$castate', '$cazip', '$castreetadd', '$summary', '$createdon', '$scheduledon', '$completedon', '$start', '$end', '$duration', '$charge', '$jasplit', '$jadriving', '$jaworking', '$jaassigned', '$jatechstatus', '$iid', '$ictive', '$ijobid', '$ijobnumber', '$isvalue', '$isname', '$inumber', '$itax', '$isubtotal', '$itotal', '$ibalance', '$iitemsid', '$iiteminvoiceid', '$iitemactive', '$iitemskuid', '$iitemskuname', '$iitemskudisplayname', '$iitemskutype', '$iitemsalestype', '$iitemdescription', '$iitemsqty', '$iitemsunitrate', '$iitemtotal', '$iitemtotalcost', '$ipaymentsid', '$ipaymentsinvoiceid', '$iptypename', '$invoiceamount', '$invoicetotalamount')";
// Check connection
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Edited above to change the ' to `
Edited php file to correct for arrays:
<?php
$servername = "localhost";
$username = "@username";
$password = "@password";
$dbname = "job-complete";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//read the json file contents
$jsondata = file_get_contents('php://input');
//convert json object to php associative array
$data = json_decode($jsondata, true);
//pull all job assignments
//set all data
$jobnumber = $data['jobNumber'];
$id = $data['businessUnit']['id'];
$bname = $data['businessUnit']['name'];
$campaign = $data['campaign']['name'];
$cname = $data['customer']['name'];
$castreet = $data['customer']['address']['street'];
$cacountry = $data['customer']['address']['country'];
$cacity = $data['customer']['address']['city'];
$castate = $data['customer']['address']['state'];
$cazip = $data['customer']['address']['zip'];
$castreetadd = $data['customer']['address']['streetAddress'];
$summary = $data['summary'];
$createdon = $data['createdOn'];
$scheduledon = $data['scheduledOn'];
$completedon = $data['completedOn'];
$start = $data['start'];
$end = $data['end'];
$duration = $data['duration'];
$charge = $data['noCharge'];
$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);
}
}
$iid = $data['invoice']['id'];
$ictive = $data['invoice']['active'];
$ijobnumber = $data['invoice']['jobNumber'];
$isvalue = $data['invoice']['status']['value'];
$isname = $data['invoice']['status']['name'];
$inumber = $data['invoice']['number'];
$itax = $data['invoice']['tax'];
$isubtotal = $data['invoice']['subtotal'];
$itotal = $data['invoice']['total'];
$ibalance = $data['invoice']['balance'];
foreach($data['invoice']['items'] as $chunked){
$iitemsid = $chunked['id'];
$iiteminvoiceid = $chunked['invoiceId'];
$iitemactive = $chunked['active'];
$iitemskuid = $chunked['sku']['id'];
$iitemskuname = $$chunked['sku']['name'];
$iitemskudisplayname = $chunked['sku']['displayName'];
$iitemskutype = $chunked['sku']['type'];
$iitemsalestype = $chunked['salesType'];
$iitemdescription = $chunked['description'];
$iitemsqty = $chunked['qty'];
$iitemsunitrate = $chunked['unitRate'];
$iitemtotal = $chunked['total'];
$iitemtotalcost = $chunked['totalCost'];
}
foreach($data['invoice']['payments'] as $chunking){
$ipaymentsid = $chunking['id'];
$ipaymentsinvoiceid = $chunking['invoiceId'];
$iptypeid = $chunking['type']['id'];
$iptypename = $chunking['type']['name'];
$invoiceamount = $chunking['amount'];
$invoicetotalamount = $chunking['totalAmount'];
}
//insert into mysql table
$sql = "INSERT INTO `jobdone` (`jnumb`, `bid`, `bname`, `campaign`, `cname`, `castreet`, `cacountry`, `cacity`, `castate`, `cazip`, `castreetadd`, `summary`, `createdon`, `scheduledon`, `completedon`, `start`, `end`, `duration`, `charge`, `jasplit`, `jadriving`, `jaworking`, `jaassigned`, `jatechstatus`, `iid`, `ictive`, `ijobid`, `ijobnumber`, `isvalue`, `isname`, `inumber`, `itax`, `isubtotal`, `itotal`, `ibalance`, `iitemsid`, `iiteminvoiceid`, `iitemactive`, `iitemskuid`, `iitemskuname`, `iitemskudisplayname`, `iitemskutype`, `iitemsalestype`, `iitemdescription`, `iitemsqty`, `iitemsunitrate`, `iitemtotal`, `iitemtotalcost`, `ipaymentsid`, `ipaymentsinvoicedid`, `iptypename`, `invoiceamount`, `invoicetotalamount`) VALUES ('$jobnumber', '$id', '$bname', '$campaign', '$cname', '$castreet', '$cacountry', '$cacity', '$castate', '$cazip', '$castreetadd', '$summary', '$createdon', '$scheduledon', '$completedon', '$start', '$end', '$duration', '$charge', '$jasplit', '$jadriving', '$jaworking', '$jaassigned', '$jatechstatus', '$iid', '$ictive', '$ijobid', '$ijobnumber', '$isvalue', '$isname', '$inumber', '$itax', '$isubtotal', '$itotal', '$ibalance', '$iitemsid', '$iiteminvoiceid', '$iitemactive', '$iitemskuid', '$iitemskuname', '$iitemskudisplayname', '$iitemskutype', '$iitemsalestype', '$iitemdescription', '$iitemsqty', '$iitemsunitrate', '$iitemtotal', '$iitemtotalcost', '$ipaymentsid', '$ipaymentsinvoiceid', '$iptypename', '$invoiceamount', '$invoicetotalamount')";
// Check connection
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Question: I am getting the following errors like my sql statement cant read what is in the arrays how can i fix this?
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemsid in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iiteminvoiceid in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemactive in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemskuid in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemskuname in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemskudisplayname in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemskutype in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemsalestype in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemdescription in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemsqty in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemsunitrate in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemtotal in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iitemtotalcost in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: ipaymentsid in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: ipaymentsinvoiceid in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: iptypename in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: invoiceamount in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
[26-Sep-2016 20:36:47 UTC] PHP Notice: Undefined variable: invoicetotalamount in /home/jtairadmin/public_html/webhooks/job_complete.php on line 97
