Fairly new to PHP/Ajax/jQuery/MySQL and I’m stuck on this piece of a large project using a PHP Ajax jQuery MySQL Datatables Live Editable Table.
Correct data is being displayed from the server and I’m able to Add new records & Delete existing records, but nothing happens after Form Submission when attempting to Update an existing record. No errors. Have attempted debugging for several hours and I’m stuck.
Help, tips, pointers, and recommendations appreciated.
4 Primary Files Included:
- index.php (Form for adds/updates)
- ajax.js (scripts)
- ajax_action.php (script/query handling)
- Records.php (queries)
index.php:
<?php
include('inc/header.php');
?>
<title>Found Gaming Portal</title>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="css/dataTables.bootstrap.min.css" />
<script src="js/ajax.js"></script>
<?php include('inc/container.php');?>
<div class="container contact">
<h2>Vendor Management</h2>
<div class="col-lg-10 col-md-10 col-sm-9 col-xs-12">
<div class="panel-heading">
<div class="row">
<div class="col-md-10">
<h3 class="panel-title"></h3>
</div>
<div class="col-md-2" align="right">
<button type="button" name="add" id="addRecord" class="btn btn-success">Add New Vendor</button>
</div>
</div>
</div>
<table id="recordListing" class="table table-bordered table-striped table-compact">
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>Type</th>
<th>ID #</th>
<th>ID Type</th>
<th>ID State</th>
<th>Phone</th>
<th>Email</th>
<th>Address</th>
<th>Address</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
<th></th>
<th></th>
</tr>
</thead>
</table>
</div>
<div id="recordModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="recordForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Edit Vendor</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label for="Name" class="control-label">Name</label>
<input type="text" class="form-control" id="Name" name="Name" placeholder="Name" required>
</div>
<div class="form-group">
<label for="Type" class="control-label">Vendor Type</label>
<select name="Type" id="Type">
<option value="Individual">Individual</option>
<option value="Company">Company</option>
</select>
</div>
<div class="form-group">
<label for="IDNumber" class="control-label">ID Number</label>
<input type="text" class="form-control" id="IDNumber" name="IDNumber" placeholder="IDNumber">
</div>
<div class="form-group">
<label for="IDType" class="control-label">ID Type</label>
<select name="IDType" id="IDType">
<option value=""></option>
<optgroup label ="State ID">
<option value="DL">Drivers License</option>
<option value="Non-Drive">Non-Driver License</option>
<option value="Military">Military</option>
<option value="Concealed Carry">Concealed Carry</option>
</optgroup>
<optgroup label="International ID">
<option value="Passport">Passport</option>
<option value="Visa">Non-Resident Visa</option>
</optgroup>
</select>
</div>
<div class="form-group">
<label for="IDState" class="IDState">ID Issuer</label>
<select name="IDState" id="IDState">
<option value=""></option>
<option value="TX">Texas</option>
<optgroup label="US States/Terr">
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
<option value="CA">California</option>
<option value="CO">Colorado</option>
<option value="CT">Connecticut</option>
<option value="DC">District of Columbia</option>
<option value="DE">Delaware</option>
<option value="FL">Florida</option>
<option value="GA">Georgia</option>
<option value="GU">Guam</option>
<option value="HI">Hawaii</option>
<option value="ID">Idaho</option>
<option value="IL">Illinois</option>
<option value="IN">Indiana</option>
<option value="IA">Iowa</option>
<option value="KS">Kansas</option>
<option value="KY">Kentucky</option>
<option value="LA">Louisiana</option>
<option value="ME">Maine</option>
<option value="MD">Maryland</option>
<option value="MA">Massachusetts</option>
<option value="MI">Michigan</option>
<option value="MN">Minnesota</option>
<option value="MS">Mississippi</option>
<option value="MO">Missouri</option>
<option value="MT">Montana</option>
<option value="NE">Nebraska</option>
<option value="NV">Nevada</option>
<option value="NH">New Hampshire</option>
<option value="NJ">New Jersey</option>
<option value="NM">New Mexico</option>
<option value="NY">New York</option>
<option value="NC">North Carolina</option>
<option value="ND">North Dakota</option>
<option value="OH">Ohio</option>
<option value="OK">Oklahoma</option>
<option value="OR">Oregon</option>
<option value="PA">Pennsylvania</option>
<option value="PR">Puerto Rico</option>
<option value="RI">Rhode Island</option>
<option value="SC">South Carolina</option>
<option value="SD">South Dakota</option>
<option value="TN">Tennessee</option>
<option value="UT">Utah</option>
<option value="VT">Vermont</option>
<option value="VA">Virginia</option>
<option value="WA">Washington</option>
<option value="WV">West Virginia</option>
<option value="WI">Wisconsin</option>
<option value="WY">Wyoming</option>
</optgroup>
</select>
</div>
<div class="form-group">
<label for="Phone" class="control-label">Phone Number</label>
<input type="tel" class="form-control" id="Phone" name="Phone" placeholder="Phone" pattern="[0-9]{3}.[0-9]{3}.[0-9]{4}" maxlength="12" required>
<small>Format: 123.123.1234</small>
</div>
<div class="form-group">
<label for="Email" class="control-label">Email</label>
<input type="email" class="form-control" id="Email" name="Email">
</div>
<div class="form-group">
<label for="Address1" class="control-label">Address</label>
<input type="Address1" class="form-control" id="Address1" name="Address1">
</div>
<div class="form-group">
<input type="Address2" class="form-control" id="Address2" name="Address2">
</div>
<div class="form-group">
<label for="City" class="control-label">City</label>
<input type="City" class="form-control" id="City" name="City">
</div>
<div class="form-group">
<label for="State" class="control-label">State</label>
<select name="State" id="State">
<option value="TX">Texas</option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
<option value="CA">California</option>
<option value="CO">Colorado</option>
<option value="CT">Connecticut</option>
<option value="DC">District of Columbia</option>
<option value="DE">Delaware</option>
<option value="FL">Florida</option>
<option value="GA">Georgia</option>
<option value="HI">Hawaii</option>
<option value="ID">Idaho</option>
<option value="IL">Illinois</option>
<option value="IN">Indiana</option>
<option value="IA">Iowa</option>
<option value="KS">Kansas</option>
<option value="KY">Kentucky</option>
<option value="LA">Louisiana</option>
<option value="ME">Maine</option>
<option value="MD">Maryland</option>
<option value="MA">Massachusetts</option>
<option value="MI">Michigan</option>
<option value="MN">Minnesota</option>
<option value="MS">Mississippi</option>
<option value="MO">Missouri</option>
<option value="MT">Montana</option>
<option value="NE">Nebraska</option>
<option value="NV">Nevada</option>
<option value="NH">New Hampshire</option>
<option value="NJ">New Jersey</option>
<option value="NM">New Mexico</option>
<option value="NY">New York</option>
<option value="NC">North Carolina</option>
<option value="ND">North Dakota</option>
<option value="OH">Ohio</option>
<option value="OK">Oklahoma</option>
<option value="OR">Oregon</option>
<option value="PA">Pennsylvania</option>
<option value="PR">Puerto Rico</option>
<option value="RI">Rhode Island</option>
<option value="SC">South Carolina</option>
<option value="SD">South Dakota</option>
<option value="TN">Tennessee</option>
<option value="UT">Utah</option>
<option value="VT">Vermont</option>
<option value="VA">Virginia</option>
<option value="WA">Washington</option>
<option value="WV">West Virginia</option>
<option value="WI">Wisconsin</option>
<option value="WY">Wyoming</option>
</select>
</div>
<div class="form-group">
<label for="Zip" class="control-label">Zip</label>
<input type="Zip" class="Zip" id="Zip" name="Zip">
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="id" id="id" />
<input type="hidden" name="action" id="action" value="" />
<input type="submit" name="save" id="save" class="btn btn-info" value="Save" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>
</div>
<?php include('inc/footer.php');?>
ajax.js:
$(document).ready(function(){
var dataRecords = $('#recordListing').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
'processing': true,
'serverSide': true,
'serverMethod': 'post',
"order":[],
"ajax":{
url:"ajax_action.php",
type:"POST",
data:{action:'listRecords'},
dataType:"json"
},
"columnDefs":[
{
"targets":[13, 14],
"orderable":false,
},
],
"pageLength": 10
});
$('#addRecord').click(function(){
$('#recordModal').modal('show');
$('#recordForm')[0].reset();
$('.modal-title').html("<i class='fa fa-plus'></i> Add Vendor");
$('#action').val('addRecord');
$('#save').val('Add');
});
$("#recordListing").on('click', '.update', function(){
var VendorID = $(this).attr("VendorID");
var action = 'getRecord';
$.ajax({
url:'ajax_action.php',
method:"POST",
data:{VendorID:VendorID, action:action},
dataType:"json",
success:function(data){
$('#recordModal').modal('show');
$('#VendorID').val(data.VendorID);
$('#Name').val(data.Name);
$('#Type').val(data.Type);
$('#IDNumber').val(data.IDNumber);
$('#IDType').val(data.IDType);
$('#IDState').val(data.IDState);
$('#Phone').val(data.Phone);
$('#Email').val(data.Email);
$('#Address1').val(data.Address1);
$('#Address2').val(data.Address2);
$('#City').val(data.City);
$('#State').val(data.State);
$('#Zip').val(data.Zip);
$('.modal-title').html("<i class='fa fa-plus'></i> Edit Vendor");
$('#action').val('updateRecord');
$('#save').val('Save');
}
})
});
$("#recordModal").on('submit','#recordForm', function(event){
event.preventDefault();
$('#save').attr('disabled','disabled');
var formData = $(this).serialize();
$.ajax({
url:"ajax_action.php",
method:"POST",
data:formData,
success:function(data){
$('#recordForm')[0].reset();
$('#recordModal').modal('hide');
$('#save').attr('disabled', false);
dataRecords.ajax.reload();
}
})
});
$("#recordListing").on('click', '.delete', function(){
var VendorID = $(this).attr("VendorID");
var action = "deleteRecord";
if(confirm("Are you sure you want to delete this vendor?")) {
$.ajax({
url:"ajax_action.php",
method:"POST",
data:{VendorID:VendorID, action:action},
success:function(data) {
dataRecords.ajax.reload();
}
})
} else {
return false;
}
});
});
ajax_action.php
<?php
include_once 'config/Database.php';
include_once 'class/Records.php';
$database = new Database();
$db = $database->getConnection();
$record = new Records($db);
if(!empty($_POST['action']) && $_POST['action'] == 'listRecords') {
$record->listRecords();
}
if(!empty($_POST['action']) && $_POST['action'] == 'addRecord') {
$record->Name = $_POST["Name"];
$record->Type = $_POST["Type"];
$record->IDNumber = $_POST["IDNumber"];
$record->IDType = $_POST["IDType"];
$record->IDState = $_POST["IDState"];
$record->Phone = $_POST["Phone"];
$record->Email = $_POST["Email"];
$record->Address1 = $_POST["Address1"];
$record->Address2 = $_POST["Address2"];
$record->City = $_POST["City"];
$record->State = $_POST["State"];
$record->Zip = $_POST["Zip"];
$record->addRecord();
}
if(!empty($_POST['action']) && $_POST['action'] == 'getRecord') {
$record->VendorID = $_POST["VendorID"];
$record->getRecord();
}
if(!empty($_POST['action']) && $_POST['action'] == 'updateRecord') {
$record->VendorID = $_POST["VendorID"];
$record->Name = $_POST["Name"];
$record->Type = $_POST["Type"];
$record->IDNumber = $_POST["IDNumber"];
$record->IDType = $_POST["IDType"];
$record->IDState = $_POST["IDState"];
$record->Phone = $_POST["Phone"];
$record->Email = $_POST["Email"];
$record->Address1 = $_POST["Address1"];
$record->Address2 = $_POST["Address2"];
$record->City = $_POST["City"];
$record->State = $_POST["State"];
$record->Zip = $_POST["Zip"];
$record->updateRecord();
}
if(!empty($_POST['action']) && $_POST['action'] == 'deleteRecord') {
$record->VendorID = $_POST["VendorID"];
$record->deleteRecord();
}
?>
Records.php
<?php
class Records {
private $recordsTable = 'vendor';
public $VendorID;
public $Name;
public $Type;
public $IDNumber;
public $IDType;
public $IDState;
public $Phone;
public $Email;
public $Address1;
public $Address2;
public $City;
public $State;
public $Zip;
private $conn;
public function __construct($db){
$this->conn = $db;
}
public function listRecords(){
$columns = array('VendorID', 'Name', 'Type', 'IDNumber', 'IDType', 'IDState', 'Phone', 'Email', 'Address1', 'Address2', 'City', 'State', 'Zip');
$sqlQuery = "SELECT * FROM ".$this->recordsTable." ";
if(!empty($_POST["search"]["value"])){
$sqlQuery .= 'where(Name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR Phone LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR Email LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR IDNumber LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY VendorID DESC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->recordsTable);
$stmtTotal->execute();
$allResult = $stmtTotal->get_result();
$allRecords = $allResult->num_rows;
$displayRecords = $result->num_rows;
$records = array();
while ($record = $result->fetch_assoc()) {
$rows = array();
$rows[] = $record['VendorID'];
$rows[] = ucfirst($record['Name']);
$rows[] = $record['Type'];
$rows[] = $record['IDNumber'];
$rows[] = $record['IDType'];
$rows[] = $record['IDState'];
$rows[] = $record['Phone'];
$rows[] = $record['Email'];
$rows[] = $record['Address1'];
$rows[] = $record['Address2'];
$rows[] = $record['City'];
$rows[] = $record['State'];
$rows[] = $record['Zip'];
$rows[] = '<button type="button" name="update" VendorID="'.$record["VendorID"].'" class="btn btn-warning btn-xs update">Update</button>';
$rows[] = '<button type="button" name="delete" VendorID="'.$record["VendorID"].'" class="btn btn-danger btn-xs delete" >Delete</button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"iTotalRecords" => $displayRecords,
"iTotalDisplayRecords" => $allRecords,
"data" => $records
);
echo json_encode($output);
}
public function getRecord(){
if($this->VendorID) {
$sqlQuery = "
SELECT * FROM ".$this->recordsTable."
WHERE VendorID = ?";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->bind_param("i", $this->VendorID);
$stmt->execute();
$result = $stmt->get_result();
$record = $result->fetch_assoc();
echo json_encode($record);
}
}
public function updateRecord(){
if($this->VendorID) {
$stmt = $this->conn->prepare("
UPDATE ".$this->recordsTable."
SET Name = ?, Type = ?, IDNumber = ?, IDType = ?, IDState = ?, Phone = ?, Email = ?, Address1 = ?, Address2 = ?, City = ?, State = ?, Zip = ?
WHERE VendorID = ?");
$this->VendorID = htmlspecialchars(strip_tags($this->VendorID));
$this->Name = htmlspecialchars(strip_tags($this->Name));
$this->Type = htmlspecialchars(strip_tags($this->Type));
$this->IDNumber = htmlspecialchars(strip_tags($this->IDNumber));
$this->IDType = htmlspecialchars(strip_tags($this->IDType));
$this->IDState = htmlspecialchars(strip_tags($this->IDState));
$this->Phone = htmlspecialchars(strip_tags($this->Phone));
$this->Email = htmlspecialchars(strip_tags($this->Email));
$this->Address1 = htmlspecialchars(strip_tags($this->Address1));
$this->Address2 = htmlspecialchars(strip_tags($this->Address2));
$this->City = htmlspecialchars(strip_tags($this->City));
$this->State = htmlspecialchars(strip_tags($this->State));
$this->Zip = htmlspecialchars(strip_tags($this->Zip));
$stmt->bind_param("sssssssssssii", $this->Name, $this->Type, $this->IDNumber, $this->IDType, $this->IDState, $this->Phone, $this->Email, $this->Address1,
$this->Address2, $this->City, $this->State, $this->Zip, $this->VendorID);
if($stmt->execute()){
return true;
}
}
public function addRecord(){
if($this->Name) {
$stmt = $this->conn->prepare("
INSERT INTO ".$this->recordsTable."(`Name`, `Type`, `IDNumber`, `IDType`, `IDState`, `Phone`, `Email`, `Address1`, `Address2`, `City`, `State`, `Zip`)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
$this->Name = htmlspecialchars(strip_tags($this->Name));
$this->Type = htmlspecialchars(strip_tags($this->Type));
$this->IDNumber = htmlspecialchars(strip_tags($this->IDNumber));
$this->IDType = htmlspecialchars(strip_tags($this->IDType));
$this->IDState = htmlspecialchars(strip_tags($this->IDState));
$this->Phone = htmlspecialchars(strip_tags($this->Phone));
$this->Email = htmlspecialchars(strip_tags($this->Email));
$this->Address1 = htmlspecialchars(strip_tags($this->Address1));
$this->Address2 = htmlspecialchars(strip_tags($this->Address2));
$this->City = htmlspecialchars(strip_tags($this->City));
$this->State = htmlspecialchars(strip_tags($this->State));
$this->Zip = htmlspecialchars(strip_tags($this->Zip));
$stmt->bind_param("sssssssssssi", $this->Name, $this->Type, $this->IDNumber, $this->IDType, $this->IDState, $this->Phone, $this->Email, $this->Address1, $this->Address2, $this->City, $this->State, $this->Zip,);
if($stmt->execute()){
return true;
}
}
}
public function deleteRecord(){
if($this->VendorID) {
$stmt = $this->conn->prepare("
DELETE FROM ".$this->recordsTable."
WHERE VendorID = ?");
$this->VendorID = htmlspecialchars(strip_tags($this->VendorID));
$stmt->bind_param("i", $this->VendorID);
if($stmt->execute()){
return true;
}
}
}
}
?>
Edit: Added screenshot