Nicole Foster Nicole Foster - 1 month ago 7
Ajax Question

Adding new record and updating record failing in mySQL table with jQuery

I'm following this article on how to do CRUD operations with jQuery dynamically and trying to integrate it with my PHP/MySQL application. However, the add and update record functions are not working and I'm unsure of where it is failing. My knowledge of AJAX and jQuery are limited.

Edit: Add and Update didn't work because I didn't have an ID attached to the carrier selects. Once I added the proper ID, everything started working again. Will update my staff.php to show what it should look like for anyone curious.

The MySQL table used for this is generated on the fly based, but the tables have the following rows:


  • id

  • email

  • mobilePhone

  • mobileCarrier

  • firstName

  • lastName



My code:

Staff.php

<?php
session_start();
require_once('../connection.php');
//get session variable, if empty, unset and logout
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: index.php");
} else {
$dept = $_SESSION[department];
}
?>
<!doctype html>
<html class="no-js" lang="en">
<head>
<meta charset="utf-8" />
<meta http-equiv="x-ua-compatible" content="ie=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Visitor Management</title>
<link rel="stylesheet" href="../css/foundation.min.css" />
<link rel="stylesheet" href="../css/app.css" />
<link type="text/css" rel="stylesheet" href="https://fast.fonts.net/cssapi/24365087-b739-4314-af6e-741946b60bef.css"/>
<link type="text/css" rel="stylesheet" href="https://fast.fonts.net/cssapi/b05259d9-ca62-44a8-8a19-d3facdbd64df.css"/>
<link type="text/css" rel="stylesheet" href="https://fast.fonts.net/cssapi/2603d516-f938-4b52-ae3a-11d25bb4c555.css"/>
<link type="text/css" rel="stylesheet" href="https://fast.fonts.net/cssapi/510266cf-74ab-4fa8-9b39-dd37b90d6ab0.css"/>
</head>

<body>
<?php
if (!$_SESSION['user']) {
header("Location: ../login.php"); // If session is not set that redirect to Login Page
}
?>

<!-- nav -->
<div class="top-bar admin">
<div class="top-bar-left">
<ul class="menu">
<li class="menu-text">Visitor Management</li>
</ul>
</div>
<div class="top-bar-right">
<ul class="menu">
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</div>

<div class="medium-2 columns dash">
<ul>
<li><a href="dashboard.php">Dashboard</a></li>
<li class="active"><a href="staff.php">Staff</a></li>
<li><a href="reports.php">Reports</a></li>
</ul>
</div>

<div class="medium-10 columns">
<div class="row checkin">
<h2>Staff Profiles</h2>
<h3>All Staff</h3>
<button class="button success" data-open="addStaff">Add New Staff Member</button>
<div class="staff"></div>

<!-- Add Staff Modal -->
<div class="reveal" id="addStaff" data-reveal>
<h3>Add New Staff Member</h3>
<label>First Name
<input type="text" placeholder="" id="firstname">
</label>
<label>Last Name
<input type="text" placeholder="" id="lastname">
</label>
<label>Email Address
<input type="email" placeholder="" id="email">
</label>
<label>Mobile Phone Number
<input type="tel" placeholder="" id="phone">
</label>
<label>Mobile Phone Carrier
<select name="carrier" id="carrier">
<option value="None"> </option>
<option value="AT&T">AT&amp;T</option>
<option value="Verizon">Verizon</option>
<option value="Sprint">Sprint</option>
<option value="TMobile">T-Mobile</option>
<option value="Metro PCS">Metro PCS</option>
</select>
</label>
<button class="button" type="button" onclick="addRecord()">Submit</button>
<button class="close-button" data-close aria-label="Close modal" type="button">
<span aria-hidden="true">&times;</span>
</button>
</div>

<!-- Edit Staff Modal -->
<div class="reveal" id="editStaff" data-reveal>
<h3>Edit Staff Member</h3>
<label>First Name
<input type="text" placeholder="" id="update_firstname">
</label>
<label>Last Name
<input type="text" placeholder="" id="update_lastname">
</label>
<label>Email Address
<input type="email" placeholder="" id="update_email">
</label>
<label>Mobile Phone Number
<input type="tel" placeholder="" id="update_phone">
</label>
<label>Mobile Phone Carrier
<select name="update_carrier" id="update_carrier">
<option value="None"> </option>
<option value="AT&T">AT&amp;T</option>
<option value="Verizon">Verizon</option>
<option value="Sprint">Sprint</option>
<option value="TMobile">T-Mobile</option>
<option value="Metro PCS">Metro PCS</option>
</select>
</label>
<button class="button" type="button" onclick="UpdateUserDetails()">Submit</button>
<button class="close-button" data-close aria-label="Close modal" type="button">
<span aria-hidden="true">&times;</span>
</button>
<input type="hidden" id="hidden_user_id">
</div>
</div>
</div>

<script src="../js/vendor/jquery.min.js"></script>
<script src="../js/vendor/what-input.min.js"></script>
<script src="../js/foundation.min.js"></script>
<script src="../js/app.js"></script>
<script>
// Add Staff Member
function addRecord() {
// get values
var firstname = $("#firstname").val();
var lastname = $("#lastname").val();
var email = $("#email").val();
var phone = $("#phone").val();
var carrier = $("#carrier").val();

// Add record
$.post("addRecord.php", {
firstname: firstname,
lastname: lastname,
email: email,
phone: phone,
carrier: carrier
}, function (data, status) {
// close the popup
$("#addStaff").foundation("close");

// read records again
readRecords();

// clear fields from the popup
$("#firstname").val("");
$("#lastname").val("");
$("#email").val("");
$("#phone").val("");
$("#carrier").val("");
});
}

// READ staff members
function readRecords() {
$.get("readRecords.php", {}, function (data, status) {
$(".staff").html(data);
});
}
$(document).ready(function () {
// READ recods on page load
readRecords(); // calling function
});

// Delete staff member
function DeleteUser(id) {
var conf = confirm("Are you sure, do you really want to delete this staff member?");
if (conf == true) {
$.post("deleteUser.php", {
id: id
},
function (data, status) {
// reload Users by using readRecords();
readRecords();
}
);
}
}

// Get staff member details
function GetUserDetails(id) {
// Add User ID to the hidden field for furture usage
$("#hidden_user_id").val(id);
$.post("readUserDetails.php", {
id: id
},
function (data, status) {
// PARSE json data
var user = JSON.parse(data);
// Assing existing values to the modal popup fields
$("#update_firstname").val(user.firstName);
$("#update_lastname").val(user.lastName);
$("#update_email").val(user.email);
$("#update_phone").val(user.mobilePhone);
$("#update_carrier").val(user.mobileCarrier);

}
);
// Open modal popup
$("#editStaff").foundation("open");
}

function UpdateUserDetails() {
// get values
var firstname = $("#update_firstname").val();
var lastname = $("#update_lastname").val();
var email = $("#update_email").val();
var phone = $("#update_phone").val();
var carrier = $("#update_carrier").val();

// get hidden field value
var id = $("#hidden_user_id").val();

// Update the details by requesting to the server using ajax
$.post("updateUserDetails.php", {
id: id,
firstname: firstname,
lastname: lastname,
email: email,
phone: phone,
carrier: carrier
},
function (data, status) {
// hide modal popup
$("#editStaff").foundation("close");
// reload Users by using readRecords();
readRecords();
}
);
}
</script>
</body>
</html>


Connection.php

<?php
# FileName="connection.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_VisitorManagement = "localhost";
$database_VisitorManagement = "visitor-management";
$username_VisitorManagement = "***";
$password_VisitorManagement = "***";
$VisitorManagement = mysqli_connect($hostname_VisitorManagement, $username_VisitorManagement, $password_VisitorManagement, $database_VisitorManagement);

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

date_default_timezone_set('America/New_York');
?>


addRecord.php

<?php
// include Database connection file
session_start();
require_once('../connection.php');
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: ../index.php");
} else {
$dept = $_SESSION[department];
}


$staffTable = $dept . "_staff";

if(isset($_POST['firstname']) && isset($_POST['lastname']) && isset($_POST['email']) && isset($_POST['phone']) && isset($_POST['carrier']))
{

// get values
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$carrier = $_POST['carrier'];

$query = "INSERT INTO {$staffTable}(firstName, lastName, email, mobilePhone, mobileCarrier) VALUES('$firstname', '$lastname', '$email', '$phone', '$carrier')";
if (!$result = mysqli_query($VisitorManagement, $query)) {
exit(mysqli_error($VisitorManagement));
}
echo "Staff Member Has Been Added!";
}
?>


readRecords.php

<?php
// include Database connection file
session_start();
require_once('../connection.php');
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: ../index.php");
} else {
$dept = $_SESSION[department];
}


$staffTable = $dept . "_staff";

// Design initial table header
$data = "<table id='staff'>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Mobile Phone</th>
<th>Mobile Carrier</th>
<th></th>
</tr>
</thead>";

$query = "SELECT * FROM {$staffTable} ORDER BY lastName";

if (!$result = mysqli_query($VisitorManagement, $query)) {
exit(mysqli_error($VisitorManagement));
}

// if query results contains rows then featch those rows
if(mysqli_num_rows($result) > 0)
{
$number = 1;
while($row = mysqli_fetch_assoc($result))
{
$data .= '<tbody>
<tr>
<td>'.$row['firstName'] . " " . $row['lastName'].'</td>
<td>'.$row['email'].'</td>
<td>'.$row['mobilePhone'].'</td>
<td>'.$row['mobileCarrier'].'</td>
<td>
<button onclick="GetUserDetails('.$row['id'].')" class="button secondary">Edit</button> <button onclick="DeleteUser('.$row['id'].')" class="button alert">Delete</button>
</td>
</tr>
</tbody>';
$number++;
}
}
else
{
// records now found
$data .= '<tr><td colspan="6">Records not found!</td></tr>';
}

$data .= '</table>';

echo $data;
?>


deleteUser.php

<?php

// include Database connection file
session_start();
require_once('../connection.php');
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: ../index.php");
} else {
$dept = $_SESSION[department];
}


$staffTable = $dept . "_staff";

// check request
if(isset($_POST['id']) && isset($_POST['id']) != "")
{

// get user id
$user_id = $_POST['id'];

// delete User
$query = "DELETE FROM {$staffTable} WHERE id = '$user_id'";
if (!$result = mysqli_query($VisitorManagement, $query)) {
exit(mysqli_error($VisitorManagement));
}
}
?>


readUserDetails.php

<?php
// include Database connection file
session_start();
require_once('../connection.php');
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: ../index.php");
} else {
$dept = $_SESSION[department];
}

$staffTable = $dept . "_staff";

// check request
if(isset($_POST['id']) && isset($_POST['id']) != "")
{
// get User ID
$user_id = $_POST['id'];

// Get User Details
$query = "SELECT * FROM {$staffTable} WHERE id = '$user_id'";
if (!$result = mysqli_query($VisitorManagement, $query)) {
exit(mysqli_error($VisitorManagement));
}
$response = array();
if(mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$response = $row;
}
}
else
{
$response['status'] = 200;
$response['message'] = "Data not found!";
}
// display JSON data
echo json_encode($response);
}
else
{
$response['status'] = 200;
$response['message'] = "Invalid Request!";
}
?>


updateUserDetails.php

<?php
// include Database connection file
session_start();
require_once('../connection.php');
if(empty($_SESSION['department'])) {
session_unset();
session_destroy();
header("Location: ../index.php");
} else {
$dept = $_SESSION[department];
}

$staffTable = $dept . "_staff";

// check request
if(isset($_POST))
{
// get values
$id = $_POST['id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$carrier = $_POST['carrier'];

// Updaste User details
$query = "UPDATE {$staffTable} SET firstName = '$firstname', lastName = '$lastname', email = '$email', mobilePhone = '$phone', mobileCarrier = '$carrier' WHERE id = '$id'";
if (!$result = mysqli_query($VisitorManagement, $query)) {
exit(mysqli_error($VisitorManagement));
}
}
?>


Am I missing something? Has anyone used that article before to create add, update, and delete records functions before?

Thank you for your help.

Answer

The best you can do now is to debug your code (using developers function of your browser - check if your ajax script is called properly) and/or check your php error log.

anyway, remove curly brackets in the insert/update query, it may helps too

Comments