Retrieve Data From MySQL Using PHP 7

Steps:

Create a table (employee) in the database to store user information such as first name, last name, city, and email.
Connect to the database using database.php.
Insert user data into the database using a form in insert.php.
Retrieve data from the employee table and display it in a table format using retrieve.php.

Here’s a breakdown of the required steps and code:

1. Create Table (SQL query):

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;

This creates the table employee with the necessary fields.

2. Database Connection (`database.php`):

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if(!$conn) {
    die('Could not Connect My Sql:' . mysqli_connect_error());
}
?>

This file connects to the database db_php_test using mysqli.

3. Insert Data (`insert.php`):

<?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">
    <!-- Library -->
    <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>PHP INSERT</h3>
            <h5 class="text-center text-success"><?= $success; ?></h5>
            <form 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="col-sm-9 offset-sm-3">
                        <button type="submit" name="save" class="btn btn-primary">Save</button>
                    </div>
                </div>
            </form>
        </div>
    </div>
</body>
</html>

This file contains a form to insert user data like first name, last name, city, and email.

4. Process Data (`process.php`):

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

if(isset($_POST['save'])) {
    $first_name = $_POST['first_name'];
    $last_name  = $_POST['last_name'];
    $city_name  = $_POST['city_name'];
    $email      = $_POST['email'];
     
    $sql = "INSERT INTO employee (first_name, last_name, city_name, email) 
            VALUES ('$first_name', '$last_name', '$city_name', '$email')";

    if (mysqli_query($conn, $sql)) {
        $success = "Insert has been successfully!";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
    mysqli_close($conn);
}
?>

This script processes the form data and inserts it into the employee table.

5. Retrieve Data (`retrieve.php`):

<?php
include_once 'database.php';
$result = mysqli_query($conn, "SELECT * FROM employee");
?>
<!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.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">
        <table id="tableHorizontalWrapper" class="table table-striped table-bordered text-center">
            <thead>
                <tr>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>City</th>
                    <th>Email</th>
                </tr>
            </thead>
            <tbody>
                <?php
                while($row = mysqli_fetch_array($result)) {
                    echo "<tr>
                            <td>{$row['first_name']}</td>
                            <td>{$row['last_name']}</td>
                            <td>{$row['city_name']}</td>
                            <td>{$row['email']}</td>
                          </tr>";
                }
                mysqli_close($conn);
                ?>
            </tbody>
        </table>
    </div>
    <script>
    $(document).ready(function() {
        $('#tableHorizontalWrapper').DataTable({
            "scrollX": true
        });
    });
    </script>
</body>
</html>

This file retrieves data from the database and displays it in a table using DataTables for additional functionality (e.g., sorting, searching).

Key Points:

Database: The employee table contains the columns userid, first_name, last_name, city_name, and email. The userid is set as the primary key and is auto-incremented.
Insert: The form data is inserted into the employee table via the process.php file after form submission.
Retrieve: The retrieve.php file fetches all records from the employee table and displays them in a table format.

This code will allow you to insert new employee data and retrieve it from the MySQL database. Let me know if you need further assistance!

Souy Soeng

Retrieve Data From MySQL Using PHP 7

Steps:

Here’s a breakdown of the required steps and code:

1. Create Table (SQL query):

2. Database Connection (`database.php`):

3. Insert Data (`insert.php`):

4. Process Data (`process.php`):

5. Retrieve Data (`retrieve.php`):

Key Points:

Post a Comment

Souy Soeng

Retrieve Data From MySQL Using PHP 7

Steps:

Here’s a breakdown of the required steps and code:

1. Create Table (SQL query):

2. Database Connection (database.php):

3. Insert Data (insert.php):

4. Process Data (process.php):

5. Retrieve Data (retrieve.php):

Key Points:

Post a Comment

2. Database Connection (`database.php`):

3. Insert Data (`insert.php`):

4. Process Data (`process.php`):

5. Retrieve Data (`retrieve.php`):