I referenced this question, but couldn't get anything actionable from this to help with my question below.
PHP INSERT a variable number of records to mysql from a html form
I have a form built to add unlimited number of fields to record bin weights (jquery). I cannot get the data input into a mysql database correctly. I have built the tables to establish a many-to-many relationship allowing recording of the bin weights with a unique bin id, while tying back into an overall job table that receives the static inputs. Below are the pertinent tables. jobNumber is the primary key for the jobOpen table, it is not AutoIncrementing, but is unique.
CREATE TABLE IF NOT EXISTS `Production`.`jobCode` (
`jobCodeID` INT NOT NULL,
`jobCodeDesc` VARCHAR(45) NOT NULL,
PRIMARY KEY (`jobCodeID`));
CREATE TABLE IF NOT EXISTS `Production`.`jobOpen` (
`jobNumber` VARCHAR(10) NOT NULL,
`jobCreationDate` DATE NOT NULL,
`Receiving_recID` INT NOT NULL,
`jobType` VARCHAR(45) NOT NULL,
`jobCode_jobCodeID` INT NOT NULL,
PRIMARY KEY (`jobNumber`),
UNIQUE INDEX `jobNumber_UNIQUE` (`jobNumber` ASC),
INDEX `fk_jobOpen_Receiving1_idx` (`Receiving_recID` ASC),
INDEX `fk_jobOpen_jobCode1_idx` (`jobCode_jobCodeID` ASC),
CONSTRAINT `fk_jobOpen_meatReceiving1`
FOREIGN KEY (`Receiving_recID`)
REFERENCES `Production`.`Receiving` (`recID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_jobOpen_jobCode1`
FOREIGN KEY (`jobCode_jobCodeID`)
REFERENCES `Production`.`jobCode` (`jobCodeID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE IF NOT EXISTS `Production`.`jobBins` (
`binID` INT NOT NULL AUTO_INCREMENT,
`binWeight` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`binID`),
UNIQUE INDEX `binID_UNIQUE` (`binID` ASC));
CREATE TABLE IF NOT EXISTS `Production`.`jobBins_has_jobOpen` (
`jobBins_binID` INT NOT NULL,
`jobOpen_jobNumber` VARCHAR(10) NOT NULL,
PRIMARY KEY (`jobBins_binID`, `jobOpen_jobNumber`),
INDEX `fk_jobBins_has_jobOpen_jobOpen1_idx` (`jobOpen_jobNumber` ASC),
INDEX `fk_jobBins_has_jobOpen_jobBins1_idx` (`jobBins_binID` ASC),
CONSTRAINT `fk_jobBins_has_jobOpen_jobBins1`
FOREIGN KEY (`jobBins_binID`)
REFERENCES `Production`.`jobBins` (`binID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_jobBins_has_jobOpen_jobOpen1`
FOREIGN KEY (`jobOpen_jobNumber`)
REFERENCES `Production`.`jobOpen` (`jobNumber`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE IF NOT EXISTS `Production`.`Receiving` (
`recID` INT NOT NULL,
`recDate` DATE NOT NULL,
`vendor` VARCHAR(45) NOT NULL,
`vendorEstNum` VARCHAR(45) NOT NULL,
`rawmatdesc_rawMatCode` VARCHAR(45) NOT NULL,
`rawMatCost` DECIMAL NOT NULL,
`recPO` VARCHAR(45) NOT NULL,
`recWeight` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`recID`),
INDEX `fk_meatReceiving_rawmatdesc1_idx` (`rawmatdesc_rawMatCode` ASC),
CONSTRAINT `fk_meatReceiving_rawmatdesc1`
FOREIGN KEY (`rawmatdesc_rawMatCode`)
REFERENCES `NatDeliProduction`.`rawmatdesc` (`rawMatCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
The form page
<?php
//Include the database class
session_start();
require('../model/class.user.php');
$user = new USER();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859- 1">
<title>Open Job</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"> </script>
<link rel="stylesheet" href="../style.css" type="text/css" />
<script type="text/javascript">
var count = 0;
$(function(){
$('p#add_field').click(function(){
count += 1;
$('#container').append(
'<strong>Bin Weight' + count + '</strong><br />'
+ '<input id="field_' + count + '" name="fields[]' + '" type="number" /><br />' );
});
});
</script>
<body>
<?php
// if form submitted
if(isset($_POST['btnSubmit'])) {
$jobNumber = strip_tags($_POST['txt_jobNumber']);
$jobCreationDate = strip_tags($_POST['date_Date']);
$Receiving_recID = strip_tags($_POST['num_recID']);
$jobType = strip_tags($_POST['txt_jobType']);
$jobCode_jobCodeID = strip_tags($_POST['num_jobCode']);
$form_data = array(
'jobNumber' => $jobNumber,
'jobCreationDate' => $jobCreationDate,
'Receiving_recID' => $meatReceiving_recID,
'jobType' => $jobType,
'jobCode_jobCodeID' => $jobCode_jobCodeID,
);
if($jobNumber=="") {
$error[] = "provide job number !";
}
else if($jobCreationDate=="") {
$error[] = "provide the job creation date";
}
else if($Receiving_recID=="") {
$error[] = "provide receiving ID !";
}
else if($jobType=="") {
$error[] = "provide job type !";
}
else if($jobCode_jobCodeID=="") {
$error[] = "provide job code !";
}
else
{
try
{
$stmt = $user->runQuery("SELECT jobNumber FROM jobOpen WHERE jobNumber=:jobNumber");
$stmt->execute(array(':jobNumber'=>$jobNumber));
$row=$stmt->fetch(PDO::FETCH_ASSOC);
if($row['jobNumber']==$jobNumber) {
$error[] = "sorry product job number already exists !";
}
else
{
if($user->testInsert('jobOpen',$form_data)){
$user->redirect('jobOpen.php?jobAdded');
}
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
if ($_POST['fields']) {
//loop through added fields
foreach ($_POST['fields'] as $value) {
//Insert into job bins table
$postJobWeight = sprintf("INSERT INTO jobBins(binWeight) VALUES ('%s')",
mysql_real_escape_string($value));
$stmt = $user->runQuery($postJobWeight);
$insertedBinID = $user->last_insert_id();
//Insert in to link table
$postJobBinTable = sprintf("INSERT INTO jobBins_has_jobOpen (jobBins_binID,jobOpen_jobNumber) VALUES ('%s','%s')",
mysql_real_escape_string($insertedBinID),
mysql_real_escape_string($jobNumber));
$stmt = $user->runQuery($postJobBinTable);
}
} else {
}
echo "<h1> User Added, <strong>" . count($_POST['fields']) . "</strong> bin(s) for this job";
}
?>
<div class="user-form">
<div class="container">
<form method="post" class="form-signin">
<h2 class="form-signin-heading">Job Information</h2><hr />
<?php
if(isset($error))
{
foreach($error as $error)
{
?>
<div class="alert alert-danger">
<i class="glyphicon glyphicon-warning-sign"></i> <?php echo $error; ?>
</div>
<?php
}
}
else if(isset($_GET['jobAdded']))
{
?>
<div class="alert alert-info">
<i class="glyphicon glyphicon-log-in"></i> Job Opened Successfully
</div>
<?php
}
?>
<?php if (!isset($_POST['btnSubmit'])) { ?>
<label for="jobNumber">Job Number:</label>
<input type="text" name="txt_jobNumber" id="jobNumber" />
<div class="spacer"></div>
<label for="jobCreationDate">Job Date:</label>
<input type="date" name="date_Date" id="jobCreationDate" />
<div class="spacer"></div>
<label for="recID">Receiving ID:</label>
<input type="number" name="num_recID" id="recID" />
<div class="spacer"></div>
<label for="jobType">Job Type:</label>
<input type="text" name="txt_jobType" id="jobType" />
<div class="spacer"></div>
<label for="jobCode">Job Code:</label>
<input type="number" name="num_jobCode" id="jobType" />
<div class="spacer"></div>
<div id="container">
<p id="add_field"><a href="#"><span>» Add Bin Weight ...</span></a></p>
</div>
<div class="spacer"></div>
<input id="go" name="btnSubmit" type="submit" value="Open Job" class="btn" />
</form>
<?php } ?>
</body>
<?php include 'footer.php' ; ?>
Pertinent functions in class.user.php
public function testInsert($table_name, $form_data)
{
try
{
$fields = array_keys($form_data);
$values = array_values($form_data);
$stmt = $this->conn->prepare("INSERT INTO ".$table_name."(`".implode('`,`', $fields)."`)VALUES('".implode("','", $form_data)."')");
foreach ($form_data as $fields => $values){
$stmt->bindparam(':' . $fields,$values);
}
$stmt->execute();
return $stmt;
} catch (PDOException $e) {
echo $e->getMessage();
}
}
public function runQuery($sql)
{
$stmt = $this->conn->prepare($sql);
return $stmt;
}
public function last_insert_id() {
return mysql_insert_id();
}
The static inputs go nicely into the table jobOpen as they should, but the binWeights do nothing. Nothing is populated in either the jobBins table or the jobBins_has_jobOpen table. No error is thrown, just no values created or posted. I am sure I am missing something ridiculously simple, but I am not very familiar with JQuery, and I am also not great with PHP...any help would be greatly appreciated.
