PHP Search Result Help

Digiboy · June 1, 2013

Hi guys,

I have a search result page where I user $_get to match result from my user search. What happens is no matter what I search for, always get the same result. I dont get any errors or warnings.

I just cant put my head around it anymore, this is my first time writing a search function in php.

Thanks you all in advance.

This is my code, I also get al result using mysql_fetch_array

$get_name=mysql_real_escape_string($_GET['search_name']);
	$get_location=mysql_real_escape_string($_GET['search_location']);

	$query = "SELECT COUNT(*) as num FROM $tableName WHERE name LIKE '%" . $get_name . "%' OR address_1 LIKE '%" . $get_location . "%' OR address_2 LIKE '%" . $get_location . "%' OR address_3 LIKE '%" . $get_location . "%' OR town LIKE '%" . $get_location . "%' OR country LIKE '%" . $get_location . "%' OR postcode LIKE '%" . $get_location . "%' ";

mac_gyver · June 1, 2013

you do realize that if you don't show us the result you did get and tell us what is wrong with it (what result you did expect), that we cannot possibly help you.

Digiboy · June 2, 2013







<?php include_once('includes/header.php'); ?>


<div class="box"><div class="column-left">


<h1>Search result ...</h1>


<?php


    $tableName="business";		

	$targetpage = "search.php"; 	

	$limit = 10;

	$get_name=mysql_real_escape_string($_GET['search_name']);

	$get_location=mysql_real_escape_string($_GET['search_location']);


    ///$query = "SELECT COUNT(*) as num FROM $tableName";

	$query = "SELECT COUNT(*) as num FROM $tableName WHERE name LIKE '%" . $get_name . "%' OR address_1 LIKE '%" . $get_location . "%' OR address_2 LIKE '%" . $get_location . "%' OR address_3 LIKE '%" . $get_location . "%' OR town LIKE '%" . $get_location . "%' OR country LIKE '%" . $get_location . "%' OR postcode LIKE '%" . $get_location . "%' ";

	$total_pages = mysql_fetch_array(mysql_query($query));

	$total_pages = $total_pages[num];


	$stages = 3;

	$page = mysql_escape_string($_GET['page']);

	if($page){

		$start = ($page - 1) * $limit; 

	}else{

		$start = 0;	

		}	


    // Get page data

	$query1 = "SELECT * FROM $tableName LIMIT $start, $limit";

	$result = mysql_query($query1);


	// Initial page num setup

	if ($page == 0){$page = 1;}

	$prev = $page - 1;	

	$next = $page + 1;							

	$lastpage = ceil($total_pages/$limit);		

	$LastPagem1 = $lastpage - 1;					



	$paginate = '';

	if($lastpage > 1)

	{	


$paginate .= "<div class='pagination capital'>";

$paginate .= "<ul>";


		// Previous

		if ($page > 1){

			$paginate.= "<li><a href='$targetpage?page=$prev'>previous</a></li>";

		}else{

			$paginate.= "<li><span class='disabled'>previous</span></li>";	}


      // Pages	

		if ($lastpage < 7 + ($stages * 2))	// Not enough pages to breaking it up

		{	

			for ($counter = 1; $counter <= $lastpage; $counter++)

			{

				if ($counter == $page){

					$paginate.= "<li><span class='current'>$counter</span></li>";

				}else{

					$paginate.= "<li><a href='$targetpage?page=$counter'>$counter</a></li>";}					

			}

		}

		elseif($lastpage > 5 + ($stages * 2))	// Enough pages to hide a few?

		{

			// Beginning only hide later pages

			if($page < 1 + ($stages * 2))		

			{

				for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)

				{

					if ($counter == $page){

						$paginate.= "<li><span class='current'>$counter</span></li>";

					}else{

						$paginate.= "<li><a href='$targetpage?page=$counter'>$counter</a></li>";}					

				}

				$paginate.= "...";

				$paginate.= "<li><a href='$targetpage?page=$LastPagem1'>$LastPagem1</a></li>";

				$paginate.= "<li><a href='$targetpage?page=$lastpage'>$lastpage</a></li>";		

			}

			// Middle hide some front and some back

			elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))

			{

				$paginate.= "<li><a href='$targetpage?page=1'>1</a></li>";

				$paginate.= "<li><a href='$targetpage?page=2'>2</a></li>";

				$paginate.= "...";

				for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)

				{

					if ($counter == $page){

						$paginate.= "<li><span class='current'>$counter</span></li>";

					}else{

						$paginate.= "<li><a href='$targetpage?page=$counter'>$counter</a></li>";}					

				}

				$paginate.= "...";

				$paginate.= "<li><a href='$targetpage?page=$LastPagem1'>$LastPagem1</a></li>";

				$paginate.= "<li><a href='$targetpage?page=$lastpage'>$lastpage</a></li>";		

			}

			// End only hide early pages

			else

			{   

				$paginate.= "<li><a href='$targetpage?page=1'>1</a></li>";

				$paginate.= "<li><a href='$targetpage?page=2'>2</a></li>";

				$paginate.= "...";

				$paginate.="</div>"; 

				for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)

				{

					if ($counter == $page){

						$paginate.= "<li><span class='current'>$counter</span></li>";

					}else{

						$paginate.= "<li><a href='$targetpage?page=$counter'>$counter</a></li>";}					

				}

			}

		}


				// Next

		if ($page < $counter - 1){ 

			$paginate.= "<li><a href='$targetpage?page=$next'>next</a></li>";

		}else{

			$paginate.= "<li><span class='disabled'>next</span></li>";

			}


		$paginate.= "<ul></div>";		



}


?>


<ul>


<?php 


echo "<script type='text/javascript' src='lib/js/jquery.min.js'></script>

	<script type='text/javascript' src='lib/jquery.raty.min.js'></script>";

$rating_cnt = 0;


		while($row = mysql_fetch_array($result))

		{


       $bus_name=$row['name'];

       $bus_address_1=$row['address_1'];

       $bus_address_2=$row['address_2'];

       $bus_address_3=$row['address_3'];

       $bus_town=$row['town'];

       $bus_country=$row['country'];

       $bus_postcode=$row['potscode'];

       $bus_tel=$row['tel'];

	   $bus_website=$row['website'];

       $bus_video=$row['video_link'];

       $bus_about=$row['about'];



       $select=mysql_query("SELECT * FROM towns WHERE id='$bus_town'"); while($myrow=mysql_fetch_array($select)) {$new_town=$myrow['town'];}



        ///////

		echo "

<div class='business_list'>

<div class='business-bars light-blue'><div class='business-bar-txt'>$bus_name, $new_town</div></div>


<div class='business-row'>

<div class='business-vimeo'><iframe src='http://player.vimeo.com/video/$bus_video' width='150' height='150' frameborder='0' webkitAllowFullScreen mozallowfullscreen allowFullScreen></iframe></span></div>

<div class='business-about'>$bus_about</div>

</div>

<div class='row2'>

<div class='bus_vote'>";


//////////////////// This is where rating should appear, table name is business

$rating_cnt++;


$total_rate = 0;

$rating_qry = "SELECT * FROM business_rating WHERE bid='".$row['id']."'";

$rating_res = mysql_query($rating_qry) or die(mysql_error());

$count_rating = mysql_num_rows($rating_res);

while($rating_row = mysql_fetch_array($rating_res))

{

	$total_rate += $rating_row['rate'];

}


if($count_rating!=0)

	$rating_score = round($total_rate/$count_rating);

else

	$rating_score = 0;


if(isset($_SESSION['account_no']) && $_SESSION['account_no']!='')

{

echo "<input type='hidden' id='prd_id_".$rating_cnt."' name='prd_id_".$rating_cnt."' value='".$row['id']."' />

	<div id='business_rate_".$rating_cnt."'></div>

	<script type='text/javascript'>

		$(function() {

		  $.fn.raty.defaults.path = 'lib/img';


		  $('#business_rate_".$rating_cnt."').raty({

			path     : 'lib/img',

			cancel   : false,

			cancelOff: 'cancel-off-big.png',

			cancelOn : 'cancel-on-big.png',

			half     : false,

			size     : 24,

			score 	 : ".$rating_score.",

			starHalf : 'star-half-big.png',

			starOff  : 'star-off-big.png',

			starOn   : 'star-on-big.png',

			click: function(score, evt) {

				var prd_id = $('#prd_id_".$rating_cnt."').val();

				$.ajax({

					type: 'POST',

					url: 'add_rate.php',

					data: { bid: prd_id, rating:score }

					}).done(function( msg ) {

						alert('You add rating on this business successfully.');

				});

			}

		  });

		});

	</script>";

}

else

{

	echo "<div id='business_rate_".$rating_cnt."'></div>

	<script type='text/javascript'>

		$(function() {

		  $.fn.raty.defaults.path = 'lib/img';


		  $('#business_rate_".$rating_cnt."').raty({

			path     : 'lib/img',

			cancel   : false,

			cancelOff: 'cancel-off-big.png',

			cancelOn : 'cancel-on-big.png',

			half     : false,

			size     : 24,

			readOnly : true,

			score 	 : ".$rating_score.",

			starHalf : 'star-half-big.png',

			starOff  : 'star-off-big.png',

			starOn   : 'star-on-big.png'

		  });

		});

	</script>";

}

//////////////////

echo"

<div class='bus_address'><div class='business-bar-txt'>$bus_address_1, $bus_address_2</div></div></div></div>


<div class='row3'>




<div class='bus_phone'><div class='business-bar-txt business-margin-left'>$bus_tel</div></div>

<div class='bus_website'><div class='business-bar-txt'><a href='$bus_website' target='_new'>$bus_website</a></div></div>



<div class='btn-group booking-review-toolbar'>

<a data-toggle='modal' href='#review' class='btn'>Reviews</a>

<a href='$bus_website' target='_new' class='btn btn-danger'>Booking</a>

</div>


</div></div> 

";

}

	 echo "<div class='results'>$total_pages Results</div>";

 // pagination

 echo $paginate;

	?>

</ul>



<div id="review" class="modal hide fade in" style="display: none; ">

            <div class="modal-header">

              <a class="close" data-dismiss="modal">×</a>

              <h3>Reviews</h3>

            </div>

            <div class="modal-body">


              <div class="fb-comments" data-href="" data-width="520" data-num-posts="10"></div>


            </div>

            <div class="modal-footer">


              <a href="#" class="btn" data-dismiss="modal">Close</a>

            </div>

          </div>






</div> <!-- end of column left  --->

<div class="column-right"><?php include_once('includes/right-column.php');?></div>

<?php include_once('includes/footer.php');?>

cpd · June 2, 2013

Have you printed your query out? Its quite possible that because you're using LIKE with wild cards on both sides of the value and OR for everything there is a match for every row.

Print the query and show it to us along with some of your database data.

Posting a page of code isn't going to help, especially when its confusing as hell.

Jessica · June 2, 2013

There's so many problems with that script.

Never ever run queries in loops. Use a JOIN.

You only select the COUNT where your search is matched, then you do $query1 = "SELECT * FROM $tableName LIMIT $start, $limit";.

Digiboy · June 2, 2013

Thanks, I noticed this, tried it with this

$query1 = "SELECT * FROM $tableName WHERE name LIKE  '%" . $get_name . "%' OR address_1 LIKE '%" . $get_location . "%' OR address_2 LIKE '%" . $get_location . "%' OR postcode LIKE '%" . $get_location . "%'";

but still the same.

DavidAM · June 2, 2013

If either field (name or location) is blank when submitted, that query will return everything in the database.

cpd · June 2, 2013

Hence why I asked you to print the query out so you could identify your own error, in this case you've had someone kind enough to state it.

mac_gyver · June 2, 2013

here are some hints -

both of your queries from $tableName must have the same WHERE clause in them. build the WHERE clause in a php variable so that you can just put it into both queries (don't repeat yourself - DRY). the reason for not repeating code that is used multiple times is so that it is only defined in one place so that you only have to change it in one place.

the WHERE clause that you build should only have search terms that are not empty (what CPD and DavidAM are telling you). if $get_name or $get_location are empty, don't even put the LIKE terms using them into the WHERE clause. your logic may in fact produce a completely empty where clause if you want to allow both $get_name or $get_location to be empty.

you only need to escape string data that you put directly into queries. $_GET['page'] isn't used directly in a query, nor is it string data, and there's no point in escaping it.

you need to limit the value in $page (greeter than 0 and less then or equal to $lastpage) before you use $page to calculate the $start value so that someone entering bad values cannot produce query errors.

your pagination links need to have $_GET['search_name'] and $_GET['search_location'] in them so that the search terms are carried over between pages.

the mysql_ database library is not recommend for new code (it will be removed in a future php version.) you need to switch to either the mysqli or PDO database library.

mac_gyver · June 2, 2013

the following is just the relevant part of your code, cleaned up and organized, that builds and runs the two queries -

$tableName="business";
$limit = 10; // number of rows per page

// produce the where clause
    // actual where clause code is an exercise for you to do ....

// get count of total matching rows
$query = "SELECT COUNT(*) FROM $tableName $where_clause";
list($total_rows) = mysql_fetch_row(mysql_query($query));

// calculate the last page number
$lastpage = ceil($total_rows/$limit);

// get, condition, and limit the $page number
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1; // default to page 1
if($page > $lastpage){
    $page = $lastpage;
}
if($page < 1){
    $page = 1;
}

// calculate the starting row
$start = ($page - 1) * $limit;

// Get page data
$query = "SELECT * FROM $tableName $where_clause LIMIT $start, $limit";
$result = mysql_query($query);

// the rest of your code goes here ....

Sign In

PHP Search Result Help

Recommended Posts

Digiboy

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Digiboy

Link to comment

Share on other sites

cpd

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

Digiboy

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

cpd

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information