Connect, Insert, Update, Delete Data from database using PHP

Here are the links of last two post in the series of this article.

In the first part I explained how to connect to the database using mysqli_connect() function.
In the second part I explained the code for inserting the data inserted in the HTML form by user, in the database's table.

In this part we will be fetching the data in two ways !

We will be dumping all the data directly from the table - (Details)
We will be getting some filtered information from the user and based on that information we will fetch the data from the database.

First Way :

Make a php file in your project directory ( in this series of tutorial we are using MyFirstProject directory)
Name that php file as get_All.php

<html>
<head>
<title> Fetch All Details</title>
</head>
<body>
<h3> Data Dump</h3>
<table>

<tr>
<th>id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>City</th>
</tr>
<?php 
include("Connection.php");

$query="Select * from details";
$result=mysqli_query($conn,$query);

while($row=mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>".$row[0]."</td>";
echo "<td>".$row[1]."</td>";
echo "<td>".$row[2]."</td>";
echo "<td>".$row[3]."</td>";
echo "<td>".$row[4]."</td>";
echo "</tr>";
 
}


?>
</table>
</body>
</html>

Here in the above code we have simply made a table in html with the header informing about the data shown in the corresponding column fetched from the database table.
The PHP code starts with including the Connection file which we made in the First Part of this tutorial series.
A variable $query is used to store the query string.

The string is a mysql query which is used to fetch all the information from the mentioned table. (Here details)

On line 20 we have fired the query using the function mysqli_query(). As explained in the second tutorial.
On line 22 in the while loop we are fetching the value from the query result, stored in the variable $result, using the function mysqli_fetch_array().
As we have fetched all the values from the table, we will get 5 columns indexed in the $row variable array as $row[0] to $row[4] respectively. ( If you don't know the table structure for this tutorial series : Click here)
Using the $row array we can echo the value fetched from the database using the query.
If there are no more results in the result set variable $result to be fetched than the while condition will return false and the while loop will terminate.
The above code will result in output something like this :

I inserted 6 rows in my database's details table using phpmyadmin and those rows are dumped by the query here.

Second way

In this second way, as I mentioned earlier, we will be making one html page first to filter detail.
This is like searching something from the database by entering/selecting some data.

for e.g Entering a name to search an entry from the database table for that name.

Here in this example below we will be having getting the filter name from the user (e.g id, first_name,last_name, age,city)
And then we will be searching the data for that filter.
For the HTML code lets make a file search.html .
Below is the code for that file:

<html>
<head><title>Search Details</title></head>
<body>

<center>
<h2>Search Data </h2>
<table border="2px"> 
<form name="form1" action="search_db.php" method="POST">
<tr>
<td >Search For:</td>
<td>
<select name="filter" required="required">
<option value="">Select Filter</option>
<option value="id">id</option>
<option value="first_name">First Name</option>
<option value="last_name">Last Name</option>
<option value="age">Age</option>
<option value="city">City</option>
</select>
</td>
</tr>

<tr>
<td>Data To be Searched :</td>
<td><input type="text" name="data" required="required" placeholder="Enter Search Data !" /></td>

</tr>

<tr>
<td colspan="2" align="center">
<input type="submit" name="submit" value="Submit !"/>
</td>
</tr>

</form>
</table>
</body>
</html>

Note : If you know html Or else you have followed my second tutorial in this series carefully, then you wont be facing any problem in understanding the above code. But even if u feel any doubt then you are free to comment and ask the question!
After getting the data using the names filter and data in the search_db.php file using the form tag's action attribute, we will process that information.
To do that have to make a new file which is obviously search_db.php.
The Code for search_db.php is as below :

<html>
<body>
<center>
<?php 

require ("Connection.php");

if(isset($_POST['submit']))
{
 $filter=$_POST['filter'];
 $data=$_POST['data'];
 if($filter=='age' || $filter=='id' )
  $query="SELECT * from details where `$filter`='$data'"; 
 else
  $query="SELECT * from details where $filter like '$data%'";
 $result=mysqli_query($conn,$query);
 
 if($result)
 {
  echo "<h2> Details Fetched Are </h3><table border='2'><tr>
 <th>id</th>
 <th>First Name</th>
 <th>Last Name</th>
 <th>Age</th>
 <th>City</th>
 </tr>";
 
  while($row=mysqli_fetch_array($result))
  {
   echo "<tr>";
   echo "<td>".$row[0]."</td>";
   echo "<td>".$row[1]."</td>";
   echo "<td>".$row[2]."</td>";
   echo "<td>".$row[3]."</td>";
   echo "<td>".$row[4]."</td>";
   echo "</tr>"; 
  }
 }
 else
  echo "<h3> Not Found !</h3>";
}
else
 echo " <h1> Go To HelL : -p ......"
?>

</center>
</body>
</html>

It's now your turn to understand this above code on your own, as the functions I have used in this code are already used in other php codes above. And other things are just basic loops and if-else ladders.
So just go through the code and if you have any, doubt feel free to comment !

You can get the code of this tutorial series here in our Github repository!

Have a happy coding! :-D

Search This Blog

YOUTH WORLD BLOG

Connect, Insert, Update, Delete Data from database using PHP - Part -3

First Way :

Second way

Comments

Post a Comment

Popular posts from this blog

Being Indian: Exploring ourselves

Windows phone Wifi connectivity problem.

Create Excel file using Java: Apache POI - PART - 2: Line Chart