PHP (Core & Framework)

How to Search your data using MySql and PHP

In this tutorial we will learn how to search your data. To give a representable design I have used bootstrap 4 in this tutorial.

Firstly I created a form with the fields as title ,start date and end date according to which user can search his or her data.

Let us understand the working of sql queries used in this tutorial.

“    $sql=”select m.*,u.name  from myrecords m, users u where m.user_id=u.user_id and u.user_id=”.$_SESSION[‘userID’];   “

Here we have taken user id from joined the two table with user id. In order to remove the ambiguity we wrote “ m.userid=u.userid..

After this we took 3 variables

$str1=””;
$str2=””;
$str3=””;

 if($title!=””)

{

$str1=”  m.title like ‘%$title%’ and “; 

}

if($sdate!=””)

{

$str2=”  m.startdate >= ‘$sdate’ and “; 

}

if($edate!=””)

{

$str3=”  m.enddate <= ‘edate’ and “; 

}

Here str1 is a variable that checks the title of table and the title entered by the user, similarly str2 and str3 check the range of the start and end date this will be done as required by the programmer.

Inorder to reduce the complexity of the sql query we used theses three variables and wrote “and” as per requirements.

<?php session_start(); include("connectins.php");?>

<!DOCTYPE html>


<html lang="en">
<head>
  <title>Manage data</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<!-- Header start here -->
<?php include("header.php");?>
<!-- Header ends here -->
<div class="container-fluid">
	<div class="row">
		<div class="col-md-3 bg-light">
			<?php include("menu.php"); ?>
		</div>
		<div class="col-md-9">
<form class="form-inline" name=f1 method=post action=''>
		
		<div class="form-group">
    <label for="title">Title: </label>
    <input name="title" type="text" class="form-control" placeholder="Enter Title" id="title">
  </div>
		<div class="form-group">
    	<label for="startdate">Start Date</label>
    	<input name="s" type="Date" class="form-control" placeholder="Enter Start Date" id="startdate">
    </div>
  
  <div class="form-group">
    	<label for="endate">End Date</label>
    	<input name="e" type="Date" class="form-control" placeholder="Enter End Date" id="enddate">
    </div>
		<button name="submit" type="submit" class="btn btn-primary btn-sm">  Search</button>

	
 </form>

 <?php

  
 $page_size=5;  // define page size (no of records per page)
  $page_no=$_GET['page_no'];    // get current page number
   if (isset($page_no))
   		{ $offset=$page_no * $page_size;  }
   else
  		{ $offset=0;  }  
    
	$str4=" limit $offset, $page_size";

 $sql="select m.*,u.name  from myrecords m, users u where m.user_id=u.user_id and u.user_id=".$_SESSION['userID'];

if(isset($_POST['submit']))
{	

	$title=$_POST['title'];
	$edate=$_POST['edate'];
	$sdate=$_POST['sdate'];

	$str1="";
	$str2="";
	$str3="";

	if($title!="")
	{
		$str1="  m.title like '%$title%' and "; 
	}
	if($sdate!="")
	{
		$str2="  m.startdate >= '$sdate' and "; 
	}
	if($edate!="")
	{
		$str3="  m.enddate <= 'edate' and "; 
	}


	$sql="select m.*,u.name  from myrecords m, users u where ".$str1.$str2.$str3." m.user_id=u.user_id and u.user_id=".$_SESSION['userID'];
}

$sqlQuery=$sql.$str4;
//echo $sqlQuery;
$rs=mysqli_query($conn,$sqlQuery);

echo"<br>";
?>
<div class="table-responsive-sm">          
  <table class="table table-bordered">
    <thead>
      <tr>
        <th>#</th>
        <th>Title</th>
        <th>Image</th>
        <th>Start Date</th>
        <th>End Date</th>
        <th>Post Date</th>
        <th>Action</th>
        
      </tr>
    </thead>
    <tbody>
     
    

<?php
$i=1;
while($row=mysqli_fetch_array($rs))
{	if($row['file_name']!="")echo "<tr>";
     echo "<td>$i</td>";
     echo "<td>".$row['title']."</td>";
     if($row['file_name']!="")
     		echo "<td>"."<img src='uploads/".$row['file_name']."' width=50 height=50 >"."</td>";
     	else
     		echo "<td>NA</td>";
     echo "<td>".$row['startdate']."</td>";
     echo "<td>".$row['enddate']."</td>";
     echo "<td>".$row['postdate']."</td>";
     echo "<td>"."<a href=delete.php?id=".$row['item_id'].">delete</a>
	  | <a href=editdetail.php?id=".$row['item_id'].">edit</a>"."</td>";
        
      echo "</tr>";
      $i=$i+1;
}


?>
</tbody>
  </table>
  



<p align="center">                               
          <?php
			
			$query=$sql;
			$result = mysqli_query($conn,$query); 
			$cnt=mysqli_num_rows($result);
			//echo "total number of records: ".$cnt;
			$pages=ceil($cnt/$page_size);
			
			if ($pages>1) 
				{
				for ($i=0;$i<$pages;++$i)
				  {
					echo " ";   
					if (!isset($_POST['submit']))
					   echo "<a href=show.php?page_no=$i>".($i+1)."</a>";
					else
					 echo "<a href=show.php?title=$title&edate=$edate&sdate=$sdate&page_no=$i>".($i+1)."</a>";
					  }
				}
					 
 

		?>
</p>
</div>
</div>
</div>
</div>
</div>
<?php include("footer.php");?>
</body>
</html>

 

Posted By: Rhythm Arya

Comment here

96 − = 90