How to Navigate Through Database Records and Execute CRUD Operations in PHP with JavaScript

In this php tutorial, we will see how to use create a dynamic web application that displays product details in an HTML table and allows users to add, edit, or remove products.

It also provides navigation buttons for browsing through the product list using JavaScript.

Project Source Code:

db_connect.php script

<?php

// Define database connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "product_db";

try
{
    // Create a new PDO instance
   $conn = new PDO("mysql: host=$servername; dbname=$dbname", $username, $password);
   // Set PDO error mode to exception
   $conn -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
  // If a connection error occurs, display it
  echo "connection failde: " . $e->getMessage();
}

?>

operations.php script

<?php

require_once 'db_connect.php';

// Check if form is submitted - add
if(isset($_POST['add']))
{
  $name = $_POST['product-name'];
  $price = $_POST['product-price'];

  // insert data
  $stmt = $conn->prepare('INSERT INTO `product`(`name`, `price`) VALUES (:name, :price)');
  $stmt->bindParam(":name", $name);
  $stmt->bindParam(":price", $price);

  session_start();

  try{

 
  if($stmt->execute())
  {
    $_SESSION['success_message'] = 'Data inserted successfully';
    header('Location: nav-crud.php');
    exit();
  }
  else
  {
    $_SESSION['warnning_message'] = 'Data NOT inserted';
    header('Location: nav-crud.php');
    exit();
  }
 }
catch(PDOException $e){
    $_SESSION['error_message'] = "Error: Data NOT inserted - " . $e->getMessage();
    header('Location: nav-crud.php');
    exit();
}


}

// Check if form is submitted - edit
elseif(isset($_POST['edit']))
{
    $id = $_POST['product-id'];
    $name = $_POST['product-name'];
    $price = $_POST['product-price'];
  
    // edit data
    $stmt = $conn->prepare('UPDATE `product` SET `name`=:name,`price`=:price WHERE `id`=:id');
    $stmt->bindParam(":name", $name);
    $stmt->bindParam(":price", $price);
    $stmt->bindParam(":id", $id);
  
   
    session_start();

    try
    {
        if($stmt->execute())
        {
            if( $stmt->rowCount() > 0 ){
                $_SESSION['success_message'] = 'Data Updated successfully';
                header('Location: nav-crud.php');
                exit();
            }
            else
            {
                $_SESSION['warnning_message'] = 'Data NOT Updated';
                header('Location: nav-crud.php');
                exit();
            }
            
        }
        
    }
    catch(PDOException $e){

        $_SESSION['error_message'] = "Error: Data NOT updated - " . $e->getMessage();
        header('Location: nav-crud.php');
        exit();

    }


}

// Check if form is submitted - remove
elseif(isset($_POST['remove']))
{
    $id = $_POST['product-id'];
  
    // remove data
    $stmt = $conn->prepare('DELETE FROM `product` WHERE `id`=:id');
    $stmt->bindParam(":id", $id);
  
session_start();

try{

    if($stmt->execute())
    {
        if( $stmt->rowCount() > 0 ){
        $_SESSION['success_message'] = 'Data Deleted successfully';
        header('Location: nav-crud.php');
        exit();
    }
    else
    {
        $_SESSION['warnning_message'] = "Data NOT deleted";
        header('Location: nav-crud.php');
        exit();
    }
    }
}    
catch(PDOException $e){

    $_SESSION['error_message'] = "Error: Data NOT deleted - " . $e->getMessage();
    header('Location: nav-crud.php');
    exit();

}

}

?>

nav-crud.php page script

<?php

// Connect to the database
require_once 'db_connect.php';

// Define the SQL query to select all products from the "product" table
// Prepare the SQL statement
$stmt = $conn->prepare('SELECT * FROM `product`');

// Execute the SQL statement
$stmt -> execute();

// Fetch all the results from the statement and store them in an array
$results = $stmt -> fetchAll(PDO::FETCH_ASSOC);

// Initialize an empty array to store the products
$products = array();

foreach($results as $row)
{
  $product = array("id"=>$row["id"],
                   "name"=>$row["name"],
                   "price"=>$row["price"]
                   );

  // Add the product array to the products array
  array_push($products, $product);

}


?>

<!DOCTYPE html>
<html>
<head>
    <title>Product Information</title>
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>

<div class="container">

<form action="operations.php" method="post">

<?php

// Start the session
session_start();

// Check if the success message is set
if(isset($_SESSION['success_message'])){

      // Display the success message
  echo '<div class="alert success">
  <p>'.$_SESSION['success_message'].'</p>
  <span class="close">&times;</span>
</div>';

// Unset the success message
unset($_SESSION['success_message']);

}

// Check if the warnning message is set
elseif(isset($_SESSION['warnning_message'])){

      // Display the warnning message
  echo '<div class="alert warnning">
  <p>'.$_SESSION['warnning_message'].'</p>
  <span class="close">&times;</span>
</div>';

// Unset the warnning message
unset($_SESSION['warnning_message']);

}

// Check if the error message is set
elseif(isset($_SESSION['error_message'])){

    // Display the error message
echo '<div class="alert error">
<p>'.$_SESSION['error_message'].'</p>
<span class="close">&times;</span>
</div>';

// Unset the error message
unset($_SESSION['error_message']);

}


?>

    <h1>Product Information</h1>
    <div class="form-row">
        <label for="product-id">Product ID:</label>
        <input type="number" name="product-id" id="product-id" required>
    </div>
    <div class="form-row">
        <label for="product-name">Product Name:</label>
        <input type="text" name="product-name" id="product-name" required>
    </div>

    <div class="form-row">
        <label for="product-price">Product Price:</label>
        <input type="text" name="product-price" id="product-price" required>
    </div>
    <div class="form-row">
        <button type="button" id="btn-first">First</button>
        <button type="button" id="btn-next">Next</button>
        <button type="button" id="btn-previous">Previous</button>
        <button type="button" id="btn-last">Last</button>
    </div>
    <div class="form-row">
        <button type="submit" id="btn-add" name="add">Add</button>
        <button type="submit" id="btn-edit" name="edit">Edit</button>
        <button type="submit" id="btn-remove" name="remove">Remove</button>
    </div>
</form>

<div id="table-container">
    <table id="products-table">

       <thead>
           <tr>
               <th>ID</th>
               <th>Name</th>
               <th>Price</th>
           </tr>
       </thead>
       <tbody>
           <?php 
           // display all products from the database
              foreach($results as $row)
              {
                echo '<tr onclick="getRowData(this)">';  
                 echo '<td>'.$row['id'].'</td>';
                 echo '<td>'.$row['name'].'</td>';
                 echo '<td>'.$row['price'].'</td>';
                echo '</tr>';
              }

           ?>
           
       </tbody>

    </table>
</div>

</div>


<script>

/* get the selected html table row */

function getRowData(row)
{
   // Get the cells in the clicked row
   const cells = row.getElementsByTagName("td");
   // Extract the data from each cell
   const id = cells[0].textContent;
   const name = cells[1].textContent;
   const price = cells[2].textContent;

   // Populate the input fields with the data
   document.getElementById("product-id").value = id;
   document.getElementById("product-name").value = name;
   document.getElementById("product-price").value = price;

}

/**************/


/* close the message alert */

document.querySelectorAll(".close").forEach(function(closeButton){
  
    closeButton.addEventListener('click', function(){

         closeButton.parentElement.style.display = "none";

    });

});

/**************/



/*** navigation buttons ***/

// convert the PHP array to a JavaScript object in JSON format.
const products = <?php echo json_encode($products); ?>
// Get the buttons
const btnFirst = document.getElementById('btn-first');
const btnNext = document.getElementById('btn-next');
const btnPrevious = document.getElementById('btn-previous');
const btnLast = document.getElementById('btn-last');

// Define a variable to keep track of the current index
let currentIndex = 0;

// Function to update the form fields based on the current index
function updateForm(){

    const product = products[currentIndex];
    document.getElementById('product-id').value = product.id;
    document.getElementById('product-name').value = product.name;
    document.getElementById('product-price').value = product.price;

}

function handleButtonClick(e)
{
  switch(e.target.id)
  {
      case'btn-first':
            currentIndex = 0;
            break;
      case'btn-next':
            currentIndex = Math.min(currentIndex + 1, products.length - 1);
            break;
      case'btn-previous':
            currentIndex = Math.max(currentIndex - 1, 0);
            break;
      case'btn-last':
            currentIndex = products.length - 1;
            break;
      default:
           break;
  }

  updateForm();

}

// Add event listeners to the buttons
btnFirst.addEventListener('click', handleButtonClick);
btnNext.addEventListener('click', handleButtonClick);
btnPrevious.addEventListener('click', handleButtonClick);
btnLast.addEventListener('click', handleButtonClick);


// Initialize the form
updateForm();


</script>

</body>
</html>

Code Explanation:

Database Connection and Data Retrieval: The code establishes a connection to a database through the 'db_connect.php' file and subsequently executes a SQL query aimed at fetching all product records from the "product" table, with the retrieved results being stored in an array.

Displaying Products: Displays product information in an HTML table, including ID, name, and price, and allows users to click on a row to select a product, which populates the input fields for editing.

Navigation Buttons: Implement navigation buttons such as 'First,' 'Next,' 'Previous,' and 'Last' to facilitate browsing through the product list.

These buttons enable users to switch between products displayed in the HTML form input fields.

CRUD Operations: This interface contains input fields for product ID, name, and price, along with corresponding buttons to add, edit, or remove products. The "Add" button inserts new products, "Edit" updates existing product details, and "Remove" deletes products from the database.

Form Submission Handling (operations.php): In the operations.php file, form submissions for adding, editing, and removing products are managed securely using prepared statements to interact with the database.

The code includes checks for successful or failed database operations and sets appropriate session messages based on the outcomes.

Message Alerts: The code checks session variables containing success, warning, or error messages, and showcases them in colored alert boxes, usually linked to database operations.

JavaScript Code: The getRowData function is employed to retrieve data from the selected row in the product table and populate the input fields.

OUTPUT: