Getting Started with CRUD In PHP

by Web Design

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.

You can download the source files for this tutorial here: CRUD In PHP Source

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 “

Record Updated

“;[/sql]

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.

Author: (1 Posts)

SwatChap is a web design fanatic. He loves to design beautiful websites and playing play ping-pong in his free time. He is currently working on WpJutsu, a website that provides users with tons of design resources.

  • Raito Yitsushi

    It’s vulnerable

    eg:
    $query=”delete from user where id=”.$_GET['id'];

    id:
    1 or 1 = 1
    it’s an easy example but UNION and it will be a complex attack

    first of all:
    – put it into a quote
    – use mysql_real_escape
    – if you need an Integer (eg: Id) use (int)($variable) and you get 0 or an ID

    ok it’s a simple example but if someone want to learn from it he will be write vulnerable codes.

    • punit

      thanks bhai

  • Icehawg

    Actually, CRUD stands for Create, Read, Update and Delete. Hence the name CRUD.

    A 10 character ID? That’s preparing for 999,999,999 users…that’s overkill. Also, since it is an auto-incremented primary key(assuming) you should have also added the “UNSIGNED” attribute to the ID since it will never be a negative.

    “NOT NULL” should also be on the username and password.

    Also, why do you call this a class at the beginning?

  • http://planetcjd.co.uk/ Chris

    Mmm you might want to check your code formatting. Also – you’re not validating the input from the user – you should always do this, without exception.

  • Adrian

    Definitely something wrong with your WP code highlighter, trying to encode the code in HTML characters codes.

    Also, why not PDO? It’s definitely much more efficient. Mysql_* is just getting to old.

    • http://www.linknomer.com andrei

      Maybe not PDO but at least some OOP it’s far more efficient, secure.

  • http://www.watana-design.com watana web design

    Thank you for tutorial.
    I’m Web design but i will learning to take about database.

    But Step 3-5 now i can’t understand.
    May be have time and Bible very much for me.

  • http://www.piotrnalepa.pl Piotr Nalepa

    You should teach CRUD using mysqli or PDO. Mysql is not recommended in web developing anymore.

  • http://www.bizsherpa.com Andrew

    So, I read the title and took the ‘Getting Started” to heart. Thanks, great article – it gets a person started.

    Sure there are some db tuneups that could be used, inputs could be cleansed to prevent SQL injections and malicious script but the title said getting started!

    This is intro content to get someone started and it fits the bill nicely. Good job!

  • http://www.bizsherpa.com Andrew

    //we do not want to use what the user supplied directly, se we clean it up

    $username_temp = mysql_entities_fix_string($_POST['username']);

    //functions
    function mysql_entities_fix_string($string)
    // turn any or / into < so that people cannot enter malicious script
    {
    return htmlentities(mysql_fix_string($string));
    }

    function mysql_fix_string($string)
    // Get rid of slashes, quotes from the entered text
    {
    if (get_magic_quotes_gpc()) $string = stripslashes($string);
    return mysql_real_escape_string($string);
    }

  • Sean Thompson

    Definitively check your code formatting. Seems you have it set to format to HTML and not PHP so you’re getting weird characters everywhere.

    No one can use that code with the formatting.

  • http://www.shiftedwork.de/blog Daniel S

    This is not a good tutorial. Its vulnerable for Injection and it uses the damn old mysql_functions. Use PDO, Guy!

  • http://twitter.com/MartenComSi Marten van Urk

    CRUD in PHP can be combined with a data mapper so easily. Less code then above and better code reusability and most of all the security is above this level ;-)

  • Ross

    Just delete all of this advice from the internet and you’ll make it a better place.

    If anyone wants to learn anything, do the world a favour and don’t use a single line of code from these ‘tutorials’

  • po

    this sucks mysql_ is way old at least use mysqli_

  • http://outro.lt raveren

    So so terrible, no separation of concerns, not PDO, no reuseable code, simply terrible.

  • Tooki

    Thank you! But I see many error on this code. Please, check it.

  • Nr Selva

    You are not using OOPS
    It is useless… You didn’t explain BUSINESS LOGIC and PRESENTATION LOGIC

  • arezou

    Thanks  a lot.

  • Samrat

    Thanks! a lot.. such a simple script… ! awesome for understand easily the complexity…

  • Davycardinaal

    Thanks, this really helped me out!

  • Schaerli

    Thank you very much

  • tomexsans

    please OOP is better 

  • Budi Panuntun89

    Thank you very much, it’s usefull for my homework. because not all homework use oop. then it’s really helped me out.

  • Eugenio

    Just want to highlight that there are many ready-to-use CRUD PHP applications which don’t need code, for example DaDaBIK (http://dadabik.org) or Appgini (http://www.bigprof.com/appgini/)

  • Rodrigo Almeida Rodriguez

    Hello,

    great tutorial

    I find a solution and now I´m using the Crudin
    Is a PHP framework that build a complete CRUD interface in PHP and MySQL

    http://crudin.smarc.com.br/en/index.html

    I can save tens of hours of programming with this
    Crudin reads my database and in real time build my custom CRUD