It has become a common necessity for website owners to collect data and manage it properly. Creating a MySQL CRUD class allows you to conveniently create, read, update and delete entries for any of your projects, indifferent of how the database is devised. CRUD allows us to generate pages to list and edit database records.
So, in this tutorial I will show you how to build a simple CRUD web app, that will empower you with the basic functions of database management.

In this article I will discuss following things:-
• Creating the database
• Creating the table
• Make connection to the database
• Insert records in table
• Update records table
• Delete records from table

The tools I will be using are:-
• XAMPP
• Dreamweaver CS5 (you can use Notepad++ or any other IDE)

Before continuing with the tutorial I would like to discuss the following definitions:
• What is CRUD?
• What is a database?
• What is a table?
• What is XAMPP?
• Why we use Dreamweaver or any other IDE for website development?

What is CRUD?

CRUD stands for create, read, update, delete. So before working on any language, we are going to get our hands wet by going to CRUD operation.

What is database?

“A comprehensive collection of related data organized for convenient access, generally in a computer.”
This means database is something we use to store our data.

What is table?

A table is a container that holds information about like items. For example, an “Employee” table would contain the same basic details on each employee: name, title, department and so on.

What is XAMPP?

XAMPP is a free to use software that comes with three major services:-
1. Apache ()
2. Mysql ()
3. PhpMyAdmin ()

Why do we use Dreamweaver or any other IDE for website development?

Dreamweaver is a very popular IDE (Integrated Development Environment). IDEs are used for fast website development as it is more reliable and makes it easy to remove and identify errors at no time.

So let start our topic as we have gone through all the necessary knowledge we have to be known.

Step-1: Creating the database:

First run the XAMPP and start the Apache and MySQL services. If it does not initially start then try to see if you have Skype or any other application that may be using XAMPP’s ports. Close these applications to avoid any conflicts. Open phpMyAdmin and start Apache and MySQL.

Go to http://localhost/phpmyadmin in your browser and create a database and name it crud, you can name the database as require.

Step-2: Creating the table:

To create the table you have to click on the database name on the page http://localhost/phpmyadmin and give the name of the table as user and number of fields to 3.

Now a new page will be created. Fill the page as below:

Similary, fill the other two fields as:-

Field: username
Type: varchar
Length/Values: 25

Field: password
Type: varchar
Length/Values: 25

Don’t change any of the other field settings and click on the save button.

Step-3: Make connection to the database:

To make a connection to localhost, we use mysql_connect(). To select the database, we use mysql_select _db().

We use the following code to connect to the database:-

$connect=mysql_connect("localhost","root","");
mysql_select_db("crud",$connect);

In above two lines, localhost is the hostname, root is the username, password=”” and crud is the database name.

Step-4: Insert records in table:

To insert records in table we use the following mysql query:

$query="insert into user(username, password) values('$username', '$password')";
mysql_query($query);

In the above statements ‘user’ is table name, ‘username’ is the column name of the database table, ‘user’ and ‘password’ are also the column names of the table ‘user’. To run this query we use mysql_query($query).

Step-4: Update records in the table:

To update record in table we use the following mysql query:

$query="update user set username='$username' , password='$password' where id=".$_POST['id'];
mysql_query($query);

In above statement $_POST is used to get the values submitted from the html form whose method is post.

Step-5: Delete records From table:

To delete record from the table we use the following mysql query:

$query="delete from user where id=".$_GET['id'];

In the above statement $_GET is used to get the values submitted from the html form whose method is get.

Code of Index.php file

Download Source Files

<?php
$connect=mysql_connect("localhost","root","");
mysql_select_db("crud",$connect);
$username;
$password;
if(isset($_POST["insert"])){
	if($_POST["insert"]=="yes"){
	$username=$_POST["username"];
	$password=$_POST["password"];

$query="insert into user(username, password) values('$username', '$password')";
if(mysql_query($query))
echo "<center>Record Inserted!</center><br>";
	}
}

if(isset($_POST["update"])){
	if($_POST["update"]=="yes"){
	$username=$_POST["username"];
	$password=$_POST["password"];

$query="update user set username='$username' , password='$password' where id=".$_POST['id'];
if(mysql_query($query))
echo "<center>Record Updated</center><br>";
	}
}

if(isset($_GET['operation'])){
if($_GET['operation']=="delete"){
$query="delete from user where id=".$_GET['id'];
if(mysql_query($query))
echo "<center>Record Deleted!</center><br>";
}
}
?>
<html>
<body>
<form name=”insert” method="post" action="index.php">
<table align="center" border="0">
<tr>
<td>username:</td>
<td><input type="text" name="username" /></td>
</tr>
<tr>
<td>password:</td>
<td><input type="password" name="password" /></td>
</tr>
<tr>
<td> </td>
<td align="right">
<input type="hidden" name="insert" value="yes" />
<input type="submit" value="Insert Record"/>
</td>
</tr>
</table>
</form>
<?php

if(isset($_GET['operation'])){
if($_GET['operation']=="edit"){
?>
<form name=”update” method="post" action="index.php">
<table align="center" border="0">
<tr>
<td>username:</td>
<td><input type="text" name="username" value="<?php echo $_GET['username']; ?>" /></td>
</tr>
<tr>
<td>password:</td>
<td><input type="text" name="password" value="<?php echo $_GET['password']; ?>"/></td>
</tr>
<tr>
<td> </td>
<td align="right">
<input type="hidden" name="id" value="<?php echo $_GET['id'] ?>" />
<input type="hidden" name="update" value="yes" />
<input type="submit" value="update Record"/>
</td>
</tr>
</table>
</form>
<?php
}}
?>

<?php
$query="select * from user";
$result=mysql_query($query);
if(mysql_num_rows($result)>0){
	echo "<table align='center' border='1'>";
	echo "<tr>";
	echo "<th>Id</th>";
	echo "<th>Username</th>";
	echo "<th>Password</th>";
	echo "</tr>";
	while($row=mysql_fetch_array($result)){
	echo "<tr>";
	echo "<td>".$row['id']."</td>";
	echo "<td>".$row['username']."</td>";
	echo "<td>".$row['password']."</td>";
	echo "<td><a href='index.php?operation=edit&id=".$row['id']."&username=".$row['username']."&password=".$row['password']."'>edit</a></td>";
	echo "<td><a href='index.php?operation=delete&id=".$row['id']."'>delete</a></td>";
	echo "</tr>";
	}
	echo "</table>";
}
else{
echo "<center>No Records Found!</center>";
}

?>
</body>
</html>

Explanation of the code:

$connect=mysql_connect("localhost","root","");
mysql_select_db("crud",$connect);

The above two statements help to connect to the database(“crud”).

if(isset($_POST["insert"])){
	if($_POST["insert"]=="yes"){
	$username=$_POST["username"];
	$password=$_POST["password"];

isset($_POST["insert"]) checks whether it is set or not, meaning whether the insert button is pressed or not.

if($_POST["insert"]=="yes"){

This statement checks whether the inserted form is submitted or not.
$_POST["username"] gets the value submitted from the form. Post stands for form method which is post.

$query="insert into user(username, password) values('$username', '$password')";
if(mysql_query($query))

$query stores the insert query. This query is inserting the username and password that we get from the insert form when the insert button is pressed to the table ‘user’.

if(mysql_query($query))

if statements returns true if the query is successfully run.

if(isset($_POST["update"])){
	if($_POST["update"]=="yes"){
	$username=$_POST["username"];
	$password=$_POST["password"];

$query=”update user set username=’$username’ , password=’$password’ where id=”.$_POST['id'];
if(mysql_query($query))
echo “

These statements updates record in the table ‘user’ getting the id which is submitted with the form named ‘updat’’. Code logic is the same as described before for insert.

if(isset($_GET['operation'])){
if($_GET['operation']=="delete"){
$query="delete from user where id=".$_GET['id'];
if(mysql_query($query))
echo "<center>Record Deleted!</center><br>";
}

The above code runs when the delete link is pressed. It first checks whether the operation keyword is set or not. If it is set then check whether it is delete operation and then runs the delete query. If the query runs successfully then it shows ‘Record Deleted!’ with center align to the page.

<form name=”insert” method="post" action="index.php">

This defines the form with the method post. The difference between post and get method of form is that with get method the form submitted values are showed in the url and there is limit to insert values with the form. Post is used if we want to submit some private data and want to hide the information from the user. The data is not attached with url. Action defines where to submit the values. We have to submit the values in the same page so we type the name of the same file i.e index.php.

<input type="hidden" name="insert" value="yes" />

Hidden type means this field is hidden from the user but it contains the data and is also submitted when the form is submitted while pressing the button. I am using this hidden field to check whether the form is submitted or not.

if(isset($_GET['operation'])){
if($_GET['operation']=="edit"){

The above two lines of code checks whether the operation is set and operation is edit or not.

value="<?php echo $_GET['username']; ?>"

Assign the username value “get” from the url. Nowthis value loads with the form.

$query="select * from user";
$result=mysql_query($query);

The above two line code runs the select query to get data from the table ‘user’.

if(mysql_num_rows($result)>0){

Count the number of records fetched from the table user and checks whether records are greater than zero.

while($row=mysql_fetch_array($result)){
	echo "<tr>";
	echo "<td>".$row['id']."</td>";
	echo "<td>".$row['username']."</td>";
	echo "<td>".$row['password']."</td>";

These statements fetches the table rows from the $result and then stores in $row array and then we display all of the elements from this array i.e. $row[‘id’] and two more.

href='index.php?operation=edit&id=".$row['id']."&username=".$row['username']."&password=".$row['password']."'>edit</a></td>";
	echo "<td><a href='index.php?operation=delete&id=".$row['id']."'>delete</a></td>";
	echo "</tr>";

These statements are used to add some data to the url i.e id, username and password.

How this application works

First the page loads and then there is an option to insert record. If any record exists in the table then it shows up with the edit and deleted links.
When we click on edit link, a new form opens and we can edit the record and then click on update record button and the record is updated in the database. The page loads again and this time it shows the new updated records with the same edit and delete links.
When we click on the delete link the record is deleted and a message will be displayed at the top of the page that the record is successful deleted.
This is how the application works.

These are the terms that may help you more:-

isset()
isset() is used to check whether the variable passed to this function has assigned any value.

Hidden field
The hidden type of html field is that which is submitted with the form but not visible in the page displayed. We use hidden fields to transfer values in the form such as id of the table.

How to run the code

Run the code in Firefox or Chrome by first running XAMPP with apache and MySQL started. Also note that you have to create the database and table with same name I used column names with same name as I have used in this application.

So, you now know the basics of CRUD in PHP. I hope you liked this tutorial and you have learnt CRUD in PHP.