I apologize for any confusion with this issue. Every suggestion seems like it should work but the fact that they aren’t is incredibly frustrating. I’ll post each file I’m using in it’s entirety to hopefully help see what I’ve done (and done wrong). There are 3 in total (not including the CSS file) 2 are php and 1 is a js script. The site works very similarly to this one https://www.sitepoint.com/creating-a-scrud-system-using-jquery-json-and-datatables/ as I’ve tailored this one to my needs. I hope this helps.
data.php
<?php
// Database details
$db_server = 'localhost';
$db_username = 'root';
$db_password = '#######';
$db_name = 'mydb';
// Get job (and id)
$job = '';
$id = '';
if (isset($_GET['job'])){
$job = $_GET['job'];
if ($job == 'get_records' ||
$job == 'get_record' ||
$job == 'add_record' ||
$job == 'edit_record'
) {
if (isset($_GET['id'])){
$id = $_GET['id'];
if (!is_numeric($id)){
$id = '';
}
}
} else {
$job = '';
}
}
// Prepare array
$mysql_data = array();
// Valid job found
if ($job != ''){
// Connect to database
$db_connection = mysqli_connect($db_server, $db_username, $db_password, $db_name);
if (mysqli_connect_errno()){
$result = 'error';
$message = 'Failed to connect to database: ' . mysqli_connect_error();
$job = '';
}
// Execute job
if ($job == 'get_records'){
// Get records
$query = "SELECT * FROM DB_Table1 ORDER BY var_1";
$query = mysqli_query($db_connection, $query);
if (!$query){
$result = 'error';
$message = 'query error';
} else {
$result = 'success';
$message = 'query success';
while ($record = mysqli_fetch_array($query)){
$functions = '<div class="function_buttons"><ul>';
$functions .= '<li class="function_edit"><a data-id="' . $record['HEADID'] . '" data-name="' . $record['var_1'] . '"><span>Edit</span></a></li>';
$functions .= '</ul></div>';
$mysql_data[] = array(
"var_1" => $record['var_1'],
"var_2" => $record['var_2'],
"variable_check " => $record['variable_check'],
"functions" => $functions
);
}
}
} elseif ($job == 'get_record'){
// Get record
if ($id == ''){
$result = 'error';
$message = 'id missing';
} else {
$query = "SELECT * FROM DB_Table1 WHERE HEADID = '" . mysqli_real_escape_string($db_connection, $id) . "'";
$query = mysqli_query($db_connection, $query);
if (!$query){
$result = 'error';
$message = 'query error';
} else {
$result = 'success';
$message = 'query success';
while ($record = mysqli_fetch_array($query)){
$mysql_data[] = array(
"var_1" => $record['var_1'],
"var_2" => $record['var_2'],
"variable_check " => $record['variable_check '],
);
}
}
}
} elseif ($job == 'add_record'){
// Add record
$query = "INSERT INTO DB_Table1 SET ";
if (isset($_GET['var_1'])) { $query .= "var_1 = '" . mysqli_real_escape_string($db_connection, $_GET['var_1']) . "', "; }
if (isset($_GET['var_2'])) { $query .= "var_2 = '" . mysqli_real_escape_string($db_connection, $_GET['var_2']) . "', "; }
if (isset($_GET['variable_check ']) && $_GET['variable_check '] == 'on') { $query .= "variable_check = 1 "; } else { $query .= "variable_check = 0 "; }
$query = mysqli_query($db_connection, $query);
if (!$query){
$result = 'error';
$message = 'query error';
} else {
$result = 'success';
$message = 'query success';
}
} elseif ($job == 'edit_record'){
// Edit record
if ($id == ''){
$result = 'error';
$message = 'id missing';
} else {
$query = "UPDATE DB_Table1 SET ";
if (isset($_GET['var_1'])) { $query .= "var_1 = '" . mysqli_real_escape_string($db_connection, $_GET['var_1']) . "', "; }
if (isset($_GET['var_2'])) { $query .= "var_2 = '" . mysqli_real_escape_string($db_connection, $_GET['var_2']) . "', "; }
if (isset($_GET['variable_check ']) && $_GET['variable_check '] == 0) { $query .= "variable_check = 0 "; } else { $query .= "variable_check = 1 "; }
$query .= "WHERE HEADID = '" . mysqli_real_escape_string($db_connection, $id) . "'";
$query = mysqli_query($db_connection, $query);
if (!$query){
$result = 'error';
$message = 'query error';
} else {
$result = 'success';
$message = 'query success';
}
}
}
// Close database connection
mysqli_close($db_connection);
}
// Prepare data
$data = array(
"result" => $result,
"message" => $message,
"data" => $mysql_data
);
// Convert PHP array to JSON array
ini_set('memory_limit', '500M');
$json_data = json_encode($data);
print $json_data;
?>
webapp.js
$(document).ready(function(){
// On page load: datatable
var table_records = $('#table_records').dataTable({
"ajax": "data.php?job=get_records",
"columns": [
{ "data": "var_1" },
{ "data": "variable_check" },
{ "data": "var_2" },
{ "data": "functions", "sClass": "functions" }
],
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [-1] }
],
"lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
"oLanguage": {
"oPaginate": {
"sFirst": " ",
"sPrevious": " ",
"sNext": " ",
"sLast": " ",
},
"sLengthMenu": "Records per page: _MENU_",
"sInfo": "Total of _TOTAL_ records (showing _START_ to _END_)",
"sInfoFiltered": "(filtered from _MAX_ total records)"
}
});
var form_record = $('#form_record');
form_record.validate();
// Show message
function show_message(message_text, message_type){
$('#message').html('<p>' + message_text + '</p>').attr('class', message_type);
$('#message_container').show();
if (typeof timeout_message !== 'undefined'){
window.clearTimeout(timeout_message);
}
timeout_message = setTimeout(function(){
hide_message();
}, 8000);
}
// Hide message
function hide_message(){
$('#message').html('').attr('class', '');
$('#message_container').hide();
}
// Show loading message
function show_loading_message(){
$('#loading_container').show();
}
// Hide loading message
function hide_loading_message(){
$('#loading_container').hide();
}
// Show lightbox
function show_lightbox(){
$('.lightbox_bg').show();
$('.lightbox_container').show();
}
// Hide lightbox
function hide_lightbox(){
$('.lightbox_bg').hide();
$('.lightbox_container').hide();
}
// Lightbox background
$(document).on('click', '.lightbox_bg', function(){
hide_lightbox();
});
// Lightbox close button
$(document).on('click', '.lightbox_close', function(){
hide_lightbox();
});
// Escape keyboard key
$(document).keyup(function(e){
if (e.keyCode == 27){
hide_lightbox();
}
});
// Hide iPad keyboard
function hide_ipad_keyboard(){
document.activeElement.blur();
$('input').blur();
}
// Add Record button
$(document).on('click', '#add_record', function(e){
e.preventDefault();
$('.lightbox_content h2').text('New Record');
$('#form_record button').text('Add New Record');
$('#form_record').attr('class', 'form add');
$('#form_record').attr('data-id', '');
$('#form_record .field_container label.error').hide();
$('#form_record .field_container').removeClass('valid').removeClass('error');
$('#form_record #var_2').val('');
$('#form_record #var_1').val('');
$('#form_record #variable_check').val('');
show_lightbox();
});
// Add Record submit form
$(document).on('submit', '#form_record.add', function(e){
e.preventDefault();
// Validate form
if (form_record.valid() == true){
// Send Record information to database
hide_ipad_keyboard();
hide_lightbox();
show_loading_message();
var form_data = $('#form_record').serialize();
var request = $.ajax({
url: 'data.php?job=add_record',
cache: false,
data: form_data,
dataType: 'json',
contentType: 'application/json; charset=utf-8',
type: 'get'
});
request.done(function(output){
if (output.result == 'success'){
// Reload datatable
table_records.api().ajax.reload(function(){
hide_loading_message();
var customer_name = $('#var_1').val();
show_message("Record '" + var_1 + "' added successfully.", 'success');
}, true);
} else {
hide_loading_message();
show_message('Add request failed', 'error');
}
});
request.fail(function(jqXHR, textStatus){
hide_loading_message();
show_message('Add request failed: ' + textStatus, 'error');
});
}
});
// Edit Record button
$(document).on('click', '.function_edit a', function(e){
e.preventDefault();
// Get Record information from database
show_loading_message();
var id = $(this).data('id');
var request = $.ajax({
url: 'data.php?job=get_record',
cache: false,
data: 'id=' + id,
dataType: 'json',
contentType: 'application/json; charset=utf-8',
type: 'get'
});
request.done(function(output){
if (output.result == 'success'){
(output.data[0].variable_check == "1") ? $("#form_record #variable_check").prop('checked', true) : $("#form_record #variable_check").prop('checked', false); // check for 1
$('.lightbox_content h2').text('Edit Record');
$('#form_record button').text('Update Record');
$('#form_record').attr('class', 'form edit');
$('#form_record').attr('data-id', id);
$('#form_record .field_container label.error').hide();
$('#form_record .field_container').removeClass('valid').removeClass('error');
$('#form_record #var_2').val(output.data[0].var_2);
$('#form_record #var_1').val(output.data[0].var_1);
$('#form_record #variable_check').val(output.data[0].variable_check);
hide_loading_message();
show_lightbox();
} else {
hide_loading_message();
show_message('Information request failed', 'error');
}
});
request.fail(function(jqXHR, textStatus){
hide_loading_message();
show_message('Information request failed: ' + textStatus, 'error');
});
});
// Edit Record submit form
$(document).on('submit', '#form_record.edit', function(e){
e.preventDefault();
// Validate form
if (form_record.valid() == true){
// Send Record information to database
hide_ipad_keyboard();
hide_lightbox();
show_loading_message();
var id = $('#form_record').attr('data-id');
var form_data = $('#form_record').serialize();
var request = $.ajax({
url: 'data.php?job=edit_record&id=' + id,
cache: false,
data: form_data,
dataType: 'json',
contentType: 'application/json; charset=utf-8',
type: 'get'
});
request.done(function(output){
if (output.result == 'success'){
// Reload datatable
table_records.api().ajax.reload(function(){
hide_loading_message();
var customer_name = $('#var_1').val();
show_message("Record '" + var_1 + "' edited/added successfully.", 'success');
}, true);
} else {
hide_loading_message();
show_message('Edit request failed', 'error');
}
});
request.fail(function(jqXHR, textStatus){
hide_loading_message();
show_message('Edit request failed: ' + textStatus, 'error');
});
}
});
});
Index.php
<?php
require_once("../login/functions.php");
require_once("../login/connection.php");
?>
<!doctype html>
<html lang="en" dir="ltr">
<head>
<title></title>
<meta charset="utf-8">
<meta name="viewport" content="width=1000, initial-scale=1">
<meta http-equiv="X-UA-Compatible" content="IE=Edge">
<link rel="stylesheet" href="//fonts.googleapis.com/css?family=Oxygen:400,700">
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css">
<link rel="stylesheet" href="layout.css">
<script charset="utf-8" src="//ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script charset="utf-8" src="//cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>
<script charset="utf-8" src="//cdn.jsdelivr.net/jquery.validation/1.13.1/jquery.validate.min.js"></script>
<script charset="utf-8" src="webapp.js"></script>
</head>
<body>
<div id="page_container">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="40%"><h1>Record Information
</h1> </td>
<td width="53%"></td>
<td width="2%"> </td>
<td width="5%"></td>
</tr>
</table>
<button type="button" class="button" id="add_record">Add Record (Blank)</button>
<table class="datatable" id="table_records">
<thead>
<tr>
<th>Variable 1</th>
<th>Variable 2</th>
<th>Variable Check</th>
<th>Functions</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<div class="lightbox_bg"></div>
<div class="lightbox_container">
<div class="lightbox_close"></div>
<div class="lightbox_content">
<h2>ADD RECORD</h2>
<form class="form add" id="form_record" data-id="" novalidate>
<div class="input_container">
<label for="var_1">Variable 1: <span class="required">*</span></label>
<div class="field_container">
<input type="text" class="text" name="var_1" id="var_1" value="">
</div>
</div>
<div class="input_container">
<label for="var_2">Variable 2: <span class="required">*</span></label>
<div class="field_container">
<input type="text" class="text" name="var_2" id="var_2" value="">
</div>
</div>
<div class="input_container">
<label for="variable_check">Variable Check:</label>
<div class="field_container">
<!-- don't have required -->
<input type="checkbox" name="variable_check" id="variable_check" value="1">
</div>
</div>
<div class="button_container">
<button type="submit">Add Blank Record</button>
</div>
</form>
</div>
</div>
<noscript id="noscript_container">
<div id="noscript" class="error">
<p>JavaScript support is needed to use this page.</p>
</div>
</noscript>
<div id="message_container">
<div id="message" class="success">
<p>This is a success message.</p>
</div>
</div>
<div id="loading_container">
<div id="loading_container2">
<div id="loading_container3">
<div id="loading_container4">
Loading, please wait...
</div>
</div>
</div>
</div>
</body>
</html>