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

Comments (4)

  1. [b][url=https://eco-corporation.ru][/url][/b]

  2. Наша компания занимается расскруткой продвижение сайта буржунет совершенно не дорого. В случае, если у вас существует свой бизнес, тогда рано или поздно вы лично осознаете, что без оптимизация и продвижение сайтов сшау вас нет возможности работать дальше. Сейчас фирма, которая подумывают о собственном будущем развитии, должна иметь веб-сайт для seo продвижение сайтов google. продвижение англоязычного сайта в google- способ, используя который возможно приобретать новых покупателей, и дополнительно получить проценты, с тем чтобы рассказать об наличии вашей собственной производственной компании, её продуктах, функциях. Специализированная международная фирма сделать для вашей фирмы инструмент, с помощью которого вы сможете залучать правильных партнеров, получать прибыль и расти.Продающийся сайт- лицо фирмы, в связи с этим имеет значение, кому вы доверяете создание своего веб страницы. Мы – команда профи, которые имеют обширный практический опыт конструирования электронную коммерцию с нуля, направления, разработанного типа. Сотрудники нашей фирмы неизменно действуем по результатом. Международная компания сумеет предоставить всем нашим заказчикам профессиональное сопровождение по доступной антикризисной расценке.Вы можете сделать заказ онлайн-визитку, рекламный сайт. Не сомневайтесь, что ваш портал будет разработан высококлассно, с разными самыми новыми технологиями.

    [url=https://apistudio.ru]продвижение сайта буржунет[/url]

  3. [b][url=https://www.cleaning-master.com]cleaning my house[/url][/b]
    Service Cleaning Staten Island was created in 2012 with a clear task: to perform a highly professional cleaning services in friendly manner that is not only which not just delights and satisfies all ours client! With deep cleaning, our employees cleansing elements refrigerator in the house . Competent employees “Cleaning Service” always ready decide varied problem, associated with guidance order. You always can call in “Cleaning Service” – our employees to the conscience cope with the work of any volume. Our Created Cleaning the holding located give you any help and solve this a problem! Our firm we carry out cleaning services(MAIDS DOWNTOWN) exclusively by means of eco-means, they absolutely safe and not even litter environment atmosphere. This the specialized company Williamsburg presents large set service on cleaning, you left only to choose the right, appropriate to you type of cleaning, and contact to our company for professional help. In our the company hourly work employee. This a highly large cleaning , that will leave your family hearth spotless . Regardless on , moving you or not Clean Master can be there to help to bring your personal old or new house exemplary appearance.We have involved only qualified masters, having the required practical experience. CLEANING MASTER Bococa apply excellent, not harmful detergents and scouring materials, reliable, professional and high quality equipment.Our organization provides services for cleaning – professional (industrial) cleaning. Cleaning famous company Clinton Hill- carries out production activity on cleaning.

  4. The spa center happy ending calls find out one of the ways massage techniques, is what we do. What is an Change roles massage interested in everyone. tantric massage this is the gift of giving for happiness. You willsurprised to that,what variety bliss can experience from adopting massage. In salon of erotic massage body to body massage masseuses will hold the most sensual nude massage.

    How is it done, and is there something exotic? We will tell you all about him that you wanted to know |Our russian massage is visited not only by men but also by women, and also by couples. You necessarily want to enjoy only this infinitely … Our intention this is to please men magical best massage with stones. Separate approach to your desires and conditions.

    The attractive women our the salon will give you an unforgettable experience. The salon is a place of rest and relaxation. The french massage, as in principle, and relaxation, affects on specific area body, this give a chance girls relax. Choose one or just two beauties! Choose which likes, both professional and professional proficiency!

    Our salon in N.Y.C. we represent beautiful premises with comfortable style. Data premises promote to stay with you not attracting the attention of other customers.

    We have a showroom in New York. Women Alexandra :
    [url=https://bodywork.manhattan-massage.com]bodywork massage salon[/url]

Comment here

15 + = 16