CODEWITHSUNDEEP
phpjquerymysqlajax
Nicole Foster
Nicole Foster

Reputation: 51

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:

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.

Upvotes: 0

Views: 140

Answers (1)

Marian Sabo
Marian Sabo

Reputation: 106

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

Upvotes: 2

Related Questions