PHP CRUD with MySQL

Recently, I had received a mail from a reader and he asked me to send PHP code for creating PHP login script which he wanted for his interview. This mail pushed me to write this tutorial, to create PHP CRUD script with MySQL database. This is a frequently asked task in Php interviews.

CRUD Operations

CRUD is nothing but representing group of database operation listed below.

  1. C – Creating or inserting new row to MySQL database.
  2. R – Read or retrieving database records.
  3. U – Update selected MySQL record.
  4. D – Delete or removing selected record from MySQL table.

php_crud_with_mysql

So, for performing this CRUD operations we should access MySQL from PHP via appropriate queries, we have seen already. In this article, we are going to create an interface as database front end to handle these operations.

Creating/Inserting New Row to MySQL Database

Let us take users table, we have taken for example of MySQL CONCAT, and the structure is,

users_table_structure

For providing user interface for the database insert, we should create a form, containing all the fields the users table has, except user_id which is auto generated numeric field. And, HTML code of ADD form will be as follows.

<html>
<head>
<title>Add New User</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<form name="frmUser" method="post" action="">
<div class="message"><?php if(isset($message)) { echo $message; } ?></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>
</form>
</body></html>

On top the above HTML code, we need to put PHP code logic handling database insert with appropriate query. So, the code is,

<?php
if(count($_POST)>0) {
$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
mysql_query("INSERT INTO users (userName, password, firstName, lastName) VALUES ('" . $_POST["userName"] . "','" . $_POST["password"] . "','" . $_POST["firstName"] . "','" . $_POST["lastName"] . "')");
$current_id = mysql_insert_id();
if(!empty($current_id)) {
$message = "New User Added Successfully";
}
}
?>

The add form will be shown to the user like as below. And, the success message shown in this form will be displayed, after successful insert.

user_add_form

Retrieving database records

Retrieving process is done by database SELECT query. After successful execution of SELECT query, datbase resource data will be returned. The following line is used to get resource data as the result of MySQL SELECT query.

$result = mysql_query("SELECT * FROM users");

Using this resource data reference, we should iterate with a PHP loop, to get each record from database. For example,

<?php
while($row = mysql_fetch_array($result)) {
?>
<!--Add HTML code to display records.-->
<?php
}
?>

After that, list of database records will be displayed to the browser, as shown below. And, this list contains icons to trigger update and delete operations for each record.

users_list

Update Selected MySQL record.

For triggering this operation with respect to each record, we can use edit icon displayed with users list. On clicking this icon for a specified row, then, edit form will be shown to the user with pre-populated user details, and it will be look like,

edit_form

For that, we need to use UPDATE query on submitting this form. After executing query, we need to retrieve updated user information using SELECT query to be populated to the form after edit. For that, the following code block shows PHP logic.

<?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
if(count($_POST)>0) {
mysql_query("UPDATE users set userName='" . $_POST["userName"] . "', password='" . $_POST["password"] . "', firstName='" . $_POST["firstName"] . "', lastName='" . $_POST["lastName"] . "' WHERE userId='" . $_POST["userId"] . "'");
$message = "Record Modified Successfully";
}
$result = mysql_query("SELECT * FROM users WHERE userId='" . $_GET["userId"] . "'");
$row= mysql_fetch_array($result);
?>

Delete Record from MySQL Table.

On clicking delete icon of a specified user record, the corresponding user id will be sent with querystring of the delete page URL, as we have passed for edit operation. And then, the delete page handles delete operation using MySQL DELETE query.

After deleting, we will be redirected to the users list page using PHP header() redirect, to know the effect of delete operation with the user list. And the code is,

<?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
mysql_query("DELETE FROM users WHERE userId='" . $_GET["userId"] . "'");
header("Location:list_user.php");
?>

I have added styles to beautify this PHP CRUD application. And the corresponding style sheet contains the following selector strings.

body {
font-family:Arial;
}
input {
font-family:Arial;
font-size:14px;
}
label{
font-family:Arial;
font-size:14px;
color:#999999;
}
.tblSaveForm {
border-top:2px #999999 solid;
background-color: #f8f8f8;
}
.tableheader {
background-color: #fedc4d;
}
.tablerow {
background-color: #A7D6F1;
color:white;
}
.btnSubmit {
background-color:#fd9512;
padding:5px;
border-color:#FF6600;
border-radius:4px;
color:white;
}
.message {
color: #FF0000;
text-align: center;
width: 100%;
}
.txtField {
padding: 5px;
border:#fedc4d 1px solid;
border-radius:4px;
}
.evenRow {
background-color: #E2EDF9;
font-size:12px;
color:#101010;
}
.evenRow:hover {
background-color: #ffef46;
}
.oddRow {
background-color: #B3E8FF;
font-size:12px;
color:#101010;
}
.oddRow:hover {
background-color: #ffef46;
}
.tblListForm {
border-top:2px #999999 solid;
}
.listheader {
background-color: #fedc4d;
font-size:12px;
font-weight:bold;
}
.link{
text-decoration:none;
color:#5e8fc7;
font-size:11px;
}

Download PHP CRUD with MySQL Source Code

This MySQL tutorial was added on September 6, 2013.

«

»