This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

📣 Try the fastest hosting platform with pay-as-you-go pricing & 24/7 expert support! MIGRATE NOW →

How to Make Simple CRUD in PHP and MySQL

Updated on November 8, 2022

4 Min Read
crud-app

In the previous installment of this MySQL series, I provided an overview of table manipulation in MySQLIn this part of the tutorial series, I will teach you how to create CRUD in PHP and MySQL by executing insert, update and delete queries. These queries can be executed in the following ways.

Single Query Execution

It executes a single query at a time.  Let’s start by inserting data into our database which we have created previously on our hosting for PHP MySQL. Create a new php file in the folder “practice” that we have previously created and name it crud.php. CRUD is the abbreviation for Create, Read, Update and Delete queries. Now in the new file, add this line at the top require_once ‘db_connection.php’ and create this new function:

require_once 'db_connection.php';

function SingleQuery($queri)
{
	$conn = OpenCon();
	
	
	if($conn->query($queri) === TRUE)
	{
		CloseCon($conn);
		return true;
	}
	else
	{
		return $conn->error;
	}

}

The function takes a single parameter as your required query and executes it. Now, create a new file, index2.php in the same folder and at top add this line require_once ‘crud.php’. Now add these lines in your index2.php:

include 'crud.php';

$sql = "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('w','w','[email protected]','First Insert Using Single Query')";

$result = SingleQuery($sql);

if($result === true)
{
	echo 'success';
	
}
else
{
	echo $result;
}

Then open your browser and locate to localhost/practice/index2.php and you will find success if you have typed the right query.

Multi Queries Execution

Using this method, you can execute more than one insert, select or update query. Let’s start by writing a function for Multi Insert or Update queries. Write this new function in your crud.php file

function MultiQuery($quries)
{
	$conn = OpenCon();
	
	
	if($conn->multi_query($quries) === true)
	{
		CloseCon($conn);
		return true;
	}
	else
	{
		return $conn->error;
	}
}

The function takes a single parameter as your required query and executes it. Now, call this function in your index2.php:

include 'crud.php';

$sql = "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('a','a','[email protected]','First Insert Using Multiple Queries');";
$sql .= "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('b','b','[email protected]','First Insert Using Multiple Queries');";
$sql .= "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('c','c','[email protected]','First Insert Using Multiple Queries');";

$result = MultiQuery($sql);

if($result === true)
{
 echo 'success';
 
}
else
{
 echo $result;
}

Now, open your browser and locate to localhost/practice/index2.php and you will find success if you have typed the right query.

Nothing as Easy as Deploying PHP Apps on Cloud

With Cloudways, you can have your PHP apps up and running on managed cloud servers in just a few minutes.

CRUD in PHP and MySQL With Prepared Statements

Prepared statements are used to execute same query multiple times with high efficiency. Now write this new function in your crud.php file:

function PreQuery($fname,$lname,$email,$subj)
{
	$conn = OpenCon();
	$query = $conn->prepare("INSERT INTO myguests(firstname, lastname, email, subject) VALUES (?,?,?,?)");
	$query->bind_param("ssss", $fname,$lname,$email,$subj);
	
	if($query->execute())
	{
		CloseCon($conn);
		return true;
	}
	else
	{
		return $conn->error;
	}
	

}

We will be selecting data using prepared statements. Let’s see how prepared statement works:

  1. First, you prepare your statement like INSERT INTO myguests(firstname, lastname, email, subject) VALUES (?,?,?,?). We left ? where we want to insert the values.
  2. Second, we will bind those values.The first parameter takes the data types of the value  and after that, the values. The data type argument can be of four types.
    1. i – integer
    2. s – string
    3. d – double
    4. b – blob
  3. We will execute it.

The function takes four parameters as your required value and executes it. Now, call this function in your index2.php:

include 'crud.php';

$firstn = "Ahmed";
$lastn = "Khan";
$email = "[email protected]";
$subject = "Inserting Data using prepared Query";
$result = PreQuery($firstn,$lastn,$email,$subject);

if($result === true)
{
	echo 'success';
	
}
else
{
	echo $result;
}

Now, open your browser and locate to localhost/practice/index2.php and you will be successful if you have typed the right query.

Select Query Execution

Now, let us select the data from our MySQL database using single query execution. Write a new function in your crud.php written below:

function selectdata($sql)
{
	$conn = OpenCon();
	
	$result = $conn->query($sql);
	if($result)
	{
		if($result->num_rows > 0)
		{
			return $result;
		}
		else
		{
			return "zero";
		}
	}
	else
	{
		return $result->error;
	}
}

The function first checks that the query is executed successfully. If not, it sends an error. Second, it checks whether number of rows is greater than 0 or not. If so, it sends “Zero results found”. Now, call this function in index2.php write the following code:

<table>
 <tr>
 <td> Name</td>
 <td> Email</td>
 <td> Message</td>
 </tr>
<?php
include 'crud.php';

$sql = "SELECT * FROM `myguests`";

$result = selectdata($sql);

if($result != "zero")
{
 
 while($row = $result->fetch_assoc())
 {
 echo "<tr>";
 echo "<td>" . $row['firstname'].' '.$row['lastname'] . "</td>";
 echo "<td>" . $row['email']. "</td>"; 
 echo "<td>" . $row['subject']. "</td>"; 
 echo "</tr>";

 }
 
 
}
else
{
 echo $result;
}
?>
 </table>

After this, open your browser and locate to localhost/practice/index2.php and you will find all the data store in your database in your index2.php page.

Update Query Using Prepared Statement

Let us write a new function in your crud.php file to execute update statement:

function UpdateQuery($column,$value,$id)
{
$conn = OpenCon();
$query = $conn->prepare("UPDATE myguests SET $column = ? WHERE id = ?");
$query->bind_param("si",$value,$id);

if($query->execute())
{
CloseCon($conn);
return true;
}
else
{
return $conn->error;
}
}

The update parameter takes three parameters. One is the column name which needs to be updated, second is the value which will be replaced by the previous value and third is the ID of the row in which it will be changed. Now, execute this function in index2.php :

include 'crud.php';


$result = UpdateQuery("firstname","David",1);

if($result === true)
{
	echo 'success';
	
}
else
{
	echo $result;
}

When you’ve done that, open your browser and locate to localhost/practice/index2.php and you will be successful if there is no error.

Delete Query Using Prepared Statement

Let us write a new function in your crud.php file to execute a delete statement:

function DeleteQuery($id)
{
$conn = OpenCon();
	$query = $conn->prepare("DELETE FROM myguests WHERE id = ?");
	$query->bind_param("i",$id);
	//var_dump($query);

	
	if($query->execute())
	{
		CloseCon($conn);
		return true;
	}
	else
	{
		return $conn->error;
	}
}

The delete parameter takes one parameter which will be the ID of the row which needs to be deleted from the table. Now, execute this function in index2.php:

include 'crud.php';

$result = DeleteQuery(1);

if($result === true)
{
	echo 'success';
	
}
else
{
	echo $result;
}

After that, open your browser and locate to localhost/practice/index2.php and you will find success if there is no error.

Conclusion

In this tutorial, we have learned how to execute CRUD in PHP and MySQL using three different ways. In the next installment of this MySQL series, I will discuss MySQL data fetch clauses. Here is a short introduction and list of major topics in this MySQL series.

If you’ve got any questions about what we learned in this tutorial, feel free to comment below! Good luck! 🙂

Share your opinion in the comment section. COMMENT NOW

Share This Article

Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works.

×

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Thankyou for Subscribing Us!

×

Webinar: How to Get 100% Scores on Core Web Vitals

Join Joe Williams & Aleksandar Savkovic on 29th of March, 2021.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Want to Experience the Cloudways Platform in Its Full Glory?

Take a FREE guided tour of Cloudways and see for yourself how easily you can manage your server & apps on the leading cloud-hosting platform.

Start my tour

CYBER WEEK SAVINGS

  • 0

    Days

  • 0

    Hours

  • 0

    Mints

  • 0

    Sec

GET OFFER

For 4 Months &
40 Free Migrations

For 4 Months &
40 Free Migrations

Upgrade Now