MySQL PHP view more detailed file and form design
Step 1: db.php
Creating the Database Table
Execute the following SQL query to create a table named employees inside your MySQL database. We will use this table for all of our future operations.
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(255) NOT NULL,
office VARCHAR(255) NOT NULL,
age VARCHAR(255) NOT NULL,
start_date DATETIME NOT NULL,
salary INT(10) NOT NULL
);Step 2: config.php
Creating the Config File
After creating the table, we need to create a PHP script in order to connect to the MySQL database server. Let's create a file named "config.php" and put the following code inside it.
We'll later include this config file in other pages using the PHP require_once() function.
<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '654321');
define('DB_NAME', 'name_db');
/* Attempt to connect to MySQL database */
$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check connection
if($connection === false)
{
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>Step 3: index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Data table</title>
<!-- library css -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.4/css/buttons.bootstrap4.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-12">
<br>
<h3 class="titulo-tabla">Record Data Table Using PHP</h3>
<hr>
<?php
// Include config file
require_once "config.php";
// Attempt select query execution
$sql = "SELECT * FROM employees";
?>
<?php
if($result = mysqli_query($connection, $sql))
{
if(mysqli_num_rows($result) > 0)
{
?>
<table id="example" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td><?= $row['id'] ;?></td>
<td><?= $row['name'] ;?></td>
<td><?= $row['position']; ?></td>
<td><?= $row['office'] ;?></td>
<td><?= $row['age'] ;?></td>
<td><?= $row['start_date']; ?></td>
<td>$<?= $row['salary'] ;?></td>
<td>
<?php
echo "<a href='read.php?id=". $row['id'] ."' title='View Record' data-toggle='tooltip'> <i class='fa fa-eye' aria-hidden='true' style='color:black'></i></a>";
echo "<a href='update.php?id=". $row['id'] ."' title='Update Record' data-toggle='tooltip'> <i class='fa fa-edit' aria-hidden='true' style='color:#3ca23c;'></i></a>";
echo "<a href='delete.php?id=". $row['id'] ."' title='Delete Record' data-toggle='tooltip'> <i class='fa fa-trash' aria-hidden='true' style='color:red;'></i></a>";
?>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
// Free result set
mysqli_free_result($result);
}
else
{
echo "<p class='lead'><em>No records were found.</em></p>";
}
}
else
{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($connection);
}
// Close connection
mysqli_close($connection);
?>
<a href="create.php" class="btn btn-success pull-left">Add New Employee</a>
</div>
</div>
</div>
<!-- library js -->
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.bootstrap4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.print.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.colVis.min.js"></script>
<!-- internal script -->
<script src="js/export.js"></script>
</body>
</html>Step 4: create.php
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $position = $office = $age = $start_date = $salary = "";
$name_err = $position_err = $office_err = $age_err = $start_date_err = $salary_err = "";
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST")
{
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name))
{
$name_err = "Please enter a name.";
}
elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/"))))
{
$name_err = "Please enter a valid name.";
}
else
{
$name = $input_name;
}
// Validate position
$input_position = trim($_POST["position"]);
if(empty($input_position))
{
$position_err = "Please enter a position.";
}
elseif(!($input_position))
{
$position_err = "Please enter a valid position.";
}
else
{
$position = $input_position;
}
// Validate office
$input_office = trim($_POST["office"]);
if(empty($input_office))
{
$office_err = "Please enter a office.";
}
elseif(!($input_office))
{
$office_err = "Please enter a valid office.";
}
else
{
$office = $input_office;
}
// Validate age
$input_age = trim($_POST["age"]);
if(empty($input_age))
{
$age_err = "Please enter the age.";
}
elseif(!($input_age))
{
$age_err = "Please enter a positive integer value.";
}
else
{
$age = $input_age;
}
// Validate date
$input_start_date = trim($_POST["start_date"]);
if(empty($input_start_date))
{
$start_date_err = "Please enter the start date.";
}
elseif(!($input_start_date))
{
$start_date_err = "Please enter a positive integer value.";
}
else
{
$start_date = $input_start_date;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary))
{
$salary_err = "Please enter the salary amount.";
}
elseif(!ctype_digit($input_salary))
{
$salary_err = "Please enter a positive integer value.";
}
else
{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($position_err) && empty($office_err) && empty($age_err) && empty($start_date_err) && empty($salary_err))
{
// Prepare an insert statement
$sql = "INSERT INTO employees (name, position, office, age, start_date, salary) VALUES (?,?,?,?,?,?)";
if($stmt = mysqli_prepare($connection, $sql))
{
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "ssssss", $name, $position, $age, $office, $start_date, $salary);
// Set parameters
$name = $name;
$position = $position;
$office = $office;
$age = $age;
$start_date = $start_date;
$salary = $salary;
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
// Records created successfully. Redirect to landing page
header("location: index.php");
exit();
}
else
{
echo "Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// Close connection
mysqli_close($connection);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,700">
<title>Create Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<!-- add style css -->
<link rel="stylesheet" href="css/css-create-style.css">
</head>
<body>
<div class="container">
<div class="signup-form">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h2>Create Record</h2>
</div>
<p>Please fill this form and submit to add employee record to the database.</p>
<form action="<?= htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="form-group <?= (!empty($name_err)) ? 'has-error' : ''; ?>">
<label>Name</label>
<input type="text" name="name" class="form-control" value="<?= $name; ?>">
<span class="help-block"><?= $name_err;?></span>
</div>
<div class="form-group <?= (!empty($position_err)) ? 'has-error' : ''; ?>">
<label>Position</label>
<input type="text" name="position" class="form-control" value="<?= $position; ?>">
<span class="help-block"><?= $position_err;?></span>
</div>
<div class="form-group <?= (!empty($office_err)) ? 'has-error' : ''; ?>">
<label>Office</label>
<input type="text" name="office" class="form-control" value="<?= $office; ?>">
<span class="help-block"><?= $salary_err;?></span>
</div>
<div class="form-group <?= (!empty($age_err)) ? 'has-error' : ''; ?>">
<label>Age</label>
<input type="number" name="age" class="form-control" value="<?= $age; ?>">
<span class="help-block"><?= $age_err;?></span>
</div>
<div class="form-group<?= (!empty($start_date_err)) ? 'has-error' : ''; ?>">
<label>Start Date</label>
<input type="date" name="start_date" class="form-control" value="<?= $start_date; ?>">
<span class="help-block"><?= $start_date_err;?></span>
</div>
<div class="form-group <?= (!empty($salary_err)) ? 'has-error' : ''; ?>">
<label>Salary</label>
<input type="text" name="salary" class="form-control" value="<?= $salary; ?>">
<span class="help-block"><?= $salary_err;?></span>
</div>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-default" style="color:red;">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Step 5: read.php
<?php
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"])))
{
// Include config file
require_once "config.php";
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = ?";
if($stmt = mysqli_prepare($connection, $sql))
{
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "i", $param_id);
// Set parameters
$param_id = trim($_GET["id"]);
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt))
{
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) == 1)
{
/* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$position = $row["position"];
$office = $row["office"];
$age = $row["age"];
$start_date = $row["start_date"];
$salary = $row["salary"];
}
else
{
// URL doesn't contain valid id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
else
{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($connection);
}
else
{
print_r($sql);
exit();
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style type="text/css">
.wrapper{
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1>View Record</h1>
<hr>
</div>
<div class="form-group">
<label>Name :<span class="font-weight-bold text text-success"> <?= $row["name"]; ?></span></label>
</div>
<div class="form-group">
<label>Position : <span class="font-weight-bold"> <?= $row["position"]; ?></span></label>
</div>
<div class="form-group">
<label>Office : <span class="font-weight-bold"> <?= $row["office"]; ?></span></label>
</div>
<div class="form-group">
<label>Age : <span class="font-weight-bold"> <?= $row["age"]; ?></span></label>
</div>
<div class="form-group">
<label>Start Date : <span class="font-weight-bold"> <?= $row["start_date"]; ?></span></label>
</div>
<div class="form-group">
<label>Salary : $<span class="font-weight-bold text-info"> <?= $row["salary"]; ?></span></label>
</div>
<p><a href="index.php" type="button" class="btn btn-outline-primary">Back</a></p>
</div>
</div>
</div>
</div>
</body>
</html>Step 6: error.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Error</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 750px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1>Invalid Request</h1>
</div>
<div class="alert alert-danger fade in">
<p>Sorry, you've made an invalid request. Please <a href="index.php" class="alert-link">go back</a> and try again.</p>
</div>
</div>
</div>
</div>
</div>
</body>
</html>Step 7: css/style.css
body {
background-image: linear-gradient(315deg, #bdd4e7 0%, #8693ab 74%);
font-family: 'Roboto', sans-serif;
}
.help-block {
color:red;
}
.form-control {
font-size: 15px;
}
.form-control, .form-control:focus, .input-group-text {
border-color: #e1e1e1;
}
.form-control, .btn {
border-radius: 3px;
}
.signup-form {
width: 100%;
margin: 0 auto;
padding: 30px 0;
}
.signup-form form {
color: #999;
border-radius: 3px;
margin-bottom: 15px;
background: #fff;
box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3);
padding: 30px;
}
.signup-form h2 {
color: #333;
font-weight: bold;
margin-top: 0;
}
.signup-form hr {
margin: 0 -30px 20px;
}
.signup-form .form-group {
margin-bottom: 20px;
}
.signup-form label {
font-weight: normal;
font-size: 15px;
}
.signup-form .form-control {
min-height: 38px;
box-shadow: none !important;
}
.signup-form .input-group-addon {
max-width: 42px;
text-align: center;
}
.signup-form .btn, .signup-form .btn:active {
font-size: 16px;
font-weight: bold;
background: #19aa8d !important;
border: none;
min-width: 140px;
}
.signup-form .btn:hover, .signup-form .btn:focus {
background: #179b81 !important;
}
.signup-form a {
color: #fff;
text-decoration: underline;
}
.signup-form a:hover {
text-decoration: none;
}
.signup-form form a {
color: #19aa8d;
text-decoration: none;
}
.signup-form form a:hover {
text-decoration: underline;
}
.signup-form .fa {
font-size: 21px;
}
.signup-form .fa-paper-plane {
font-size: 18px;
}
.signup-form .fa-check {
color: #fff;
left: 17px;
top: 18px;
font-size: 7px;
position: absolute;
}
Step 8: js.js
$(document).ready(function() {
var table = $('#example').DataTable( {
lengthChange: false,
buttons: [ 'copy', 'excel', 'pdf', 'colvis' ]
} );
table.buttons().container()
.appendTo( '#example_wrapper .col-md-6:eq(0)' );
} );
0 Comments
CAN FEEDBACK
Emoji