How To Make Simple CRUD In PHP And MySQL
In this part of the tutorial series, I will teach you how to execute insert, update and delete queries. These queries can be executed in three different ways.
- Single Query Execution
- Multi Queries Execution
- Prepared Statement Execution
Single Query Execution
It executes a single query at a time. Let’s start by inserting data into our previously created database. 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
include 'crud.php';
$sql = "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('w','w','t@gmail.com','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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
include 'crud.php';
$sql = "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('a','a','a@gmail.com','First Insert Using Multiple Queries');";
$sql .= "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('b','b','b@gmail.com','First Insert Using Multiple Queries');";
$sql .= "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('c','c','c@gmail.com','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.
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
include 'crud.php';
$firstn = "Ahmed";
$lastn = "Khan";
$email = "ahmed.khan@cloudways.com";
$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 database using single query execution. Write a new function in your crud.php written below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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 :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 select, insert, update and delete queries using three different ways.
Comments
Post a Comment