Insert Data Into MySQL Using PHP

1. Database Table (employee)

The table definition remains the same. Here’s the SQL code for creating the table:

CREATE TABLE `employee` (
  `userid` int(8) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(55) NOT NULL,
  `last_name` varchar(55) NOT NULL,
  `city_name` varchar(55) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Make sure that the the userid column is set to AUTO_INCREMENT for automatic id generation.

2. `database.php`

Modify your database.php to use PDO (PHP Data Objects) for a better and safer database connection, avoiding issues with mysqli and providing easier handling for prepared statements.

<?php
$servername = 'localhost';
$username   = 'root';
$password   = '';
$dbname     = 'db_php_test';

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

3. `insert.php`

Your insert.php file should contain the form where the user submits their data. Make sure you have proper HTML and form validation.

<?php
    include_once 'process.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>PHP INSERT</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
</head>
<body>

<div class="container">
    <div class="shadow p-3 mb-5 bg-white rounded">
        <h3 class="mt33">PHP INSERT</h3>
        <h5 class="text-center text-success" id="message"><?= $sucess ;?></h5>
        <br>
        <form class="mt33" action="insert.php" method="post">
            <div class="form-group row">
                <label for="first_name" class="control-label col-sm-3">First name *</label>
                <div class="col-sm-9">
                    <input type="text" class="form-control" id="first_name" name="first_name" placeholder="Enter first name" required>
                </div>
            </div>

            <div class="form-group row">
                <label for="last_name" class="control-label col-sm-3">Last name *</label>
                <div class="col-sm-9">
                    <input type="text" class="form-control" id="last_name" name="last_name" placeholder="Enter last name" required>
                </div>
            </div>

            <div class="form-group row">
                <label for="city_name" class="control-label col-sm-3">City name *</label>
                <div class="col-sm-9">
                    <select class="custom-select" id="city_name" name="city_name" required>
                        <option value="">Please select city name</option>
                        <option value="Tokyo">Tokyo</option>
                        <option value="Phnom Penh">Phnom Penh</option>
                        <option value="USA">USA</option>
                    </select>
                </div>
            </div>

            <div class="form-group row">
                <label for="email" class="control-label col-sm-3">Email *</label>
                <div class="col-sm-9">
                    <input type="email" class="form-control" id="email" name="email" placeholder="Enter email" required>
                </div>
            </div>

            <div class="form-group row">
                <div class="offset-sm-3 col-sm-9 pull-right">
                    <button type="submit" id="save" name="save" class="btn btn-primary">Save</button>
                </div>
            </div>
        </form>
    </div>
</div>

<script>
    $(document).ready(function() {
        setTimeout(function() {
            $('#message').hide();
        }, 3000);
    });
</script>

</body>
</html>

4. `process.php`

Here is how you can refactor process.php to use prepared statements to securely insert data into the database:

<?php
include_once 'database.php';
$sucess = "";

if(isset($_POST['save'])) {
    // Get user input
    $first_name = $_POST['first_name'];
    $last_name  = $_POST['last_name'];
    $city_name  = $_POST['city_name'];
    $email      = $_POST['email'];

    // Prepare SQL query
    $sql = "INSERT INTO employee (first_name, last_name, city_name, email) VALUES (:first_name, :last_name, :city_name, :email)";

    // Prepare the statement
    $stmt = $conn->prepare($sql);

    // Bind parameters
    $stmt->bindParam(':first_name', $first_name);
    $stmt->bindParam(':last_name', $last_name);
    $stmt->bindParam(':city_name', $city_name);
    $stmt->bindParam(':email', $email);

    // Execute the query
    if ($stmt->execute()) {
        $sucess = "Insert has been successfully!";
    } else {
        $sucess = "Failed to insert data!";
    }

    // Close connection
    $conn = null;
}
?>

5. `retrieve.php`

This file retrieves data from the employee table and displays it in a table using Bootstrap and DataTables for better display.

<?php
include_once 'database.php';
$query = "SELECT * FROM employee";
$stmt = $conn->prepare($query);
$stmt->execute();
$employees = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Retrieve Data</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css">
    <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>

<div class="container">
    <h3>Employee List</h3>
    <table id="employeeTable" class="table table-striped table-bordered">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>City</th>
                <th>Email</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($employees as $employee): ?>
                <tr>
                    <td><?= htmlspecialchars($employee['first_name']) ?></td>
                    <td><?= htmlspecialchars($employee['last_name']) ?></td>
                    <td><?= htmlspecialchars($employee['city_name']) ?></td>
                    <td><?= htmlspecialchars($employee['email']) ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<script>
    $(document).ready(function() {
        $('#employeeTable').DataTable();
    });
</script>

</body>
</html>

Conclusion

Security: The code uses prepared statements for secure database queries to prevent SQL injection.
PDO: I've refactored your code to use PDO for database interaction instead of mysqli, which is more modern and flexible.
Data Retrieval: The retrieve.php file fetches all records from the database and displays them using DataTables, which allows for sorting and searching.

Souy Soeng

Insert Data Into MySQL Using PHP

1. Database Table (employee)

2. `database.php`

3. `insert.php`

4. `process.php`

5. `retrieve.php`

Conclusion

Post a Comment

Souy Soeng

Insert Data Into MySQL Using PHP

1. Database Table (employee)

2. database.php

3. insert.php

4. process.php

5. retrieve.php

Conclusion

Post a Comment

2. `database.php`

3. `insert.php`

4. `process.php`

5. `retrieve.php`