In the previous installment of this MySQL series, I provided an overview of table manipulation in MySQL. In 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:
- First, you prepare your statement like INSERT INTO myguests(firstname, lastname, email, subject) VALUES (?,?,?,?). We left ? where we want to insert the values.
- 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.
- i – integer
- s – string
- d – double
- b – blob
- 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! 🙂
Shahzeb Ahmed
Shahzeb is a Digital Marketer with a Software Engineering background, works as a Community Manager — PHP Community at Cloudways. He is growth ambitious and aims to learn & share information about PHP & Laravel Development through practice and experimentation. He loves to travel and explore new ideas whenever he finds time. Get in touch with him at [email protected]