PHP CRUD with MySQL
In this tutorial, we are going to see an example program to learn how to do database CRUD operations using PHP and MySQL. CRUD tends to Create, Read, Update and Delete operations with database table records. In the previous tutorial, we have seen how to access MySQL database via PHP.
In this example, we are going to create an interface as database front end to handle these operations. We have users table containing users information like name, password and more. With this table, we have to perform CRUD using MySQL.
Creating New Row in MySQL Database
The code below is to provide the user interface for the database insert. This HTML form contains input fields to enter user data to be inserted into the table.
<form name="frmUser" method="post" action="">
<div style="width:500px;">
<div class="message"><?php if(isset($message)) { echo $message; } ?></div>
<div align="right" style="padding-bottom:5px;"><a href="index.php" class="link"><img alt='List' title='List' src='images/list.png' width='15px' height='15px'/> List User</a></div>
<table border="0" cellpadding="10" cellspacing="0" width="500" align="center" class="tblSaveForm">
<tr class="tableheader">
<td colspan="2">Add New User</td>
</tr>
<tr>
<td><label>Username</label></td>
<td><input type="text" name="userName" class="txtField"></td>
</tr>
<tr>
<td><label>Password</label></td>
<td><input type="password" name="password" class="txtField"></td>
</tr>
<td><label>First Name</label></td>
<td><input type="text" name="firstName" class="txtField"></td>
</tr>
<td><label>Last Name</label></td>
<td><input type="text" name="lastName" class="txtField"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="submit" value="Submit" class="btnSubmit"></td>
</tr>
</table>
</div>
</form>
On submitting this form the following PHP code create INSERT query with the form fields and fire this query to add new record into database,
<?php
if(count($_POST)>0) {
require_once("db.php");
$sql = "INSERT INTO users (userName, password, firstName, lastName) VALUES ('" . $_POST["userName"] . "','" . $_POST["password"] . "','" . $_POST["firstName"] . "','" . $_POST["lastName"] . "')";
mysqli_query($conn,$sql);
$current_id = mysqli_insert_id($conn);
if(!empty($current_id)) {
$message = "New User Added Successfully";
}
}
?>
PHP MySQL Read
The following code shows how to fetch all the records from the database and to list in the list page.
<?php
require_once("db.php");
$sql = "SELECT * FROM users ORDER BY userId DESC";
$result = mysqli_query($conn,$sql);
?>
<html>
<head>
<title>Users List</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<form name="frmUser" method="post" action="">
<div style="width:500px;">
<div class="message"><?php if(isset($message)) { echo $message; } ?></div>
<div align="right" style="padding-bottom:5px;"><a href="add_user.php" class="link"><img alt='Add' title='Add' src='images/add.png' width='15px' height='15px'/> Add User</a></div>
<table border="0" cellpadding="10" cellspacing="1" width="500" class="tblListForm">
<tr class="listheader">
<td>Username</td>
<td>First Name</td>
<td>Last Name</td>
<td>CRUD Actions</td>
</tr>
<?php
$i=0;
while($row = mysqli_fetch_array($result)) {
if($i%2==0)
$classname="evenRow";
else
$classname="oddRow";
?>
<tr class="<?php if(isset($classname)) echo $classname;?>">
<td><?php echo $row["userName"]; ?></td>
<td><?php echo $row["firstName"]; ?></td>
<td><?php echo $row["lastName"]; ?></td>
<td><a href="edit_user.php?userId=<?php echo $row["userId"]; ?>" class="link"><img alt='Edit' title='Edit' src='images/edit.png' width='15px' height='15px' hspace='10' /></a> <a href="delete_user.php?userId=<?php echo $row["userId"]; ?>" class="link"><img alt='Delete' title='Delete' src='images/delete.png' width='15px' height='15px'hspace='10' /></a></td>
</tr>
<?php
$i++;
}
?>
</table>
</form>
</div>
</body></html>
MySQL Update via PHP
First, we fetch record by id and populate the values in the edit form. On submitting edited user information we form an update query to edit the record with the reference of its id. The code is,
<?php
require_once("db.php");
if(count($_POST)>0) {
$sql = "UPDATE users set userName='" . $_POST["userName"] . "', password='" . $_POST["password"] . "', firstName='" . $_POST["firstName"] . "', lastName='" . $_POST["lastName"] . "' WHERE userId='" . $_POST["userId"] . "'";
mysqli_query($conn,$sql);
$message = "Record Modified Successfully";
}
$select_query = "SELECT * FROM users WHERE userId='" . $_GET["userId"] . "'";
$result = mysqli_query($conn,$select_query);
$row = mysqli_fetch_array($result);
?>
Delete Record from MySQL Table
The following code is used to delete a record from the database by sending the record id in page URL.
<?php
require_once("db.php");
$sql = "DELETE FROM users WHERE userId='" . $_GET["userId"] . "'";
mysqli_query($conn,$sql);
header("Location:index.php");
?>
Leave Comment