Problem with random results and pagination

virtuoso1 posted this at 13:34 — 27th March 2009.

They have: 2 posts

Joined: Mar 2009

I have a search engine pulling the results from a MYSQL database with pagination.

THE PROBLEM: I need the results to come up random on all the pages after each search or whenever the page is refreshed without doubling. I can do it with one page but the problem is with more pages.

I'll be very grateful if somebody can come up with any ideas as I cannot find a solution anywhere.

decibel.places posted this at 15:12 — 27th March 2009.

He has: 1,494 posts

Joined: Jun 2008

Hi virtuoso, welcome to TWF!

Can you post some code? Remember to enclose it in code tags, the third icon from the right in the toolbar...

greg posted this at 15:32 — 27th March 2009.

He has: 1,581 posts

Joined: Nov 2005

There are various ways to get random data from a database.
But without knowing what your code is doing it's hard to say how to help you.

Do you want each page to not display results that were on previous pages? If so you will need to "remember" what has been displayed, and if you don't do this, then eventually visitors may see "items" they have already seen, but may not have seen all items.

So it also depends on how many "items" you have in the table you are getting random data from, how many displayed per page etc.

For example, if you have total of 30 items in the DB table, and display 10 per page, then there isn't much scope for random, and no point remembering previous results.
If however you have tens of thousands of "items" then you could probably safely just select random on each page each time it's accessed.
But again, eventually visitors will see "items" already seen, but may not have seen all items.

Showing some of your code would help, and a bit more info as to what exactly you want and how many "items" you are accessing.

pr0gr4mm3r posted this at 15:59 — 27th March 2009.

He has: 1,502 posts

Joined: Sep 2006

The only way I can see accomplishing this would be to store all the results in a temp table, and then you can load them for normal way using limit() with the pagination. You don't need to store all the data in the temp table, just the record IDs. Then clear out old search queries every once in a while.

virtuoso1 posted this at 08:27 — 30th March 2009.

They have: 2 posts

Joined: Mar 2009

<?php
include("config.php");
$page = $_GET['page'];
$records_per_page = 7;
$padding = 2;
if(!filter_var($page, FILTER_VALIDATE_INT, 1))
  $page=1;    
  $post_id = $_GET['post']; 


$offset = ($page-1) * $records_per_page;


$result = mysql_query("SELECT * FROM logins , category WHERE logins.category_id = category.category_id and category.category = '$post_id' LIMIT $offset, $records_per_page");

function build_url($filename, $key, $value){
  $values = array();
  $query_str = array();
  //get the query string arguments and store them in
  //the $values array
  parse_str($_SERVER['QUERY_STRING'], $values);
  //loop through the $values array and add the appropriate 
  //keys to the query string
  foreach($values as $k=>$v){
    //IF, though, a key in the existing query string matches the same key
    //we're trying to add, ignore it, since we'll add it manually in a moment
    //This prevents having multiples of the same keys
    if($k!=$key){
      $query_str[] = "{$k}={$v}";
    }
  }
  //add in our new key and value
  $query_str[] = "{$key}={$value}";
  //reconstruct the full URL using the implode() function to piece together all
  //the query string values in the $query_string array, joining them together with "&"  
  return "$filename?".implode("&", $query_str);
}


$count_result = mysql_query("SELECT COUNT(*) FROM logins , category WHERE logins.category_id = category.category_id and category.category = '$post_id'");
$count_row = mysql_fetch_array($count_result);
//fetch the total number of rows in the table
$count = $count_row["COUNT(*)"];

while ($user_query = mysql_fetch_array ($count_result)) {
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo "$post_id"?></title>
<link href="style2.css" rel="stylesheet" type="text/css"/>
<link href="table.css" rel="stylesheet" type="text/css"/>
<link href="layout.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="jquery-1.2.1.pack.js"></script>
<script type="text/javascript">
function lookup(inputString) {
		if(inputString.length == 0) {
			// Hide the suggestion box.
			$('#suggestions').hide();
		} else {
			$.post("rpc.php", {queryString: ""+inputString+""}, function(data){
				if(data.length >0) {
					$('#suggestions').show();
					$('#autoSuggestionsList').html(data);
				}
			});
		}
	} // lookup
	
	function fill(thisValue) {
		$('#inputString').val(thisValue);
		setTimeout("$('#suggestions').hide();", 200);
	}
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}
function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
</script>
<style type="text/css">
	body {
	color: #000;
	font: 14px Arial, Helvetica, sans-serif;
	}
	
	

</style>



</head>
<body link="#0A77CC" vlink="#0A77CC" alink="#0A77CC">

<div id="main">
<div><img src="images/header.gif" width="751" height="114" border="0" usemap="#Map" />
  <map name="Map" id="Map">
    <area shape="rect" coords="-2,0,755,120" href="index.php" />
  </map>
</div>	
    <div id="content">
	  
      
      <div class="search1">
<div class="roundedcornr_box_797844">
   <div class="roundedcornr_top_797844"><div></div></div>
      <div class="roundedcornr_content_797844">
<div align="left" style="padding:0 20px 0 40px;">
		<div style="max-height:40px;"><form action="view.php" method="get" name="search">
        <div align="left"  class="text1" style="margin-top:-10px; width:170px;">Business Search:</div>
        <input style="margin-top:3px; vertical-align: top; margin-right:20px; height:25px; padding:6px 0 0 4px;" name="post" type="text" size="80" value="" id="inputString" onkeyup="lookup(this.value);" onblur="fill();" autocomplete="OFF"/>
        <input onclick="$rand" style="vertical-align:bottom;" class="submit" type="IMAGE" src="images/search_small.gif" alt="Submit button" name="submit"/>
        <input name="yes" type="hidden" value="yes" />
		  <div class="suggestionsBox" id="suggestions" style="display: none;">
		    <img src="upArrow.png" style="position: relative; top: -9px; left: 30px;" alt="upArrow" />
		    <div align="left" class="suggestionList" id="autoSuggestionsList">
		      
		      </div>
		    </div>
		</form></div>
      </div>
      </div>
   <div class="roundedcornr_bottom_797844"><div></div></div>
</div>
</div>
  </div>

<div align="right" class="page"><span style="padding:15px 10px 10px 15px;">

</span> 
<div style="float:left; padding-left:10px; color: #0A77CC;"><?php echo "<b>$count</b>"; echo "&nbsp;results for&nbsp;"; echo "<b>\"$post_id\" </b>";?></div><div class="pager" ><?php 
  //we will use this information to build our navigation list
echo (($page>1)?"<a href='".build_url("view.php", "page", $page-1)."'>prev</a>":"prev")." | ";
for($i=1; $i<=($count/$records_per_page)+1; $i++){
  if($i!=$page)
    echo "<a href='".build_url("view.php", "page", $i)."'>$i</a>";
  else
    echo $i;
  //output a little symbol (|) to seperate the links
  //but not for the last link
  if($i<$count/$records_per_page)
    echo " | ";
}
echo " | ".(($page<$count/$records_per_page)?"<a href='".build_url("view.php", "page", $page+1)."'>next</a>":"next");?></div></div>


 

  <div id="contentWrapper">
   <div id="contentMain">
 
<table style="width:100%;">

<?php
 

$j = 2;

if($row = mysql_fetch_array($result)){



  do{
 if ($j % 2 == 0) {
        $color = "vzebra-odd";
    } else {
        $color = "vzebra-odd2";
    }
?>

<?php
 echo "<span class= \"company\">{$row['company_name']}</span><br>"; 
?>

<?php
 echo "<br>";
?>

<?php
 echo ($row['address1']); 
?>

<?php
 if (!empty($row['address2']) ){
        echo $zap;
        }
?>

<?php
 echo ($row['address2']); 
?>

<?php
 if (!empty($row['address3']) ){
        echo $zap;
        }
?>

<?php
 echo ($row['address3']);
?>

<?php
 if (($row['town']) > 0){
        echo $zap;
        }
?>

<?php
 echo ($row['town']); 
?>

<?php
 if (!empty($row['county']) ){
        echo $zap;
        }
?>

<?php
 echo ($row['county']); 
?>

<?php
 if (($row['post_code']) > 0){
        echo $zap;
        }
?>

<?php
 echo ($row['post_code']); 
?>

<?php
 echo "<br>";
?>

<?php
 if (($row['telephone']) > 0){
        echo $tel;
        }
?>

<?php
 echo ($row['telephone']); 
?>

<?php
 if (($row['mobile']) > 0){
        echo $mob;
        }
?>

<?php
 echo ($row['mobile']); 
?>

<?php
 if (($row['fax']) > 0){
        echo $fax;
        }
?>

<?php
 echo ($row['fax']); 
?>

<?php
 if (!empty($row['email']) ){
        echo $e_m;
        }
?>

<?php
 echo "<a href= <a href="mailto:"" title="mailto:"">mailto:"</a> .$row['email'].">".$row['email']."</a>" ;
?>

<?php
 if (!empty($row['www']) ){
        echo $w;
        }
?>

<?php
 echo "<a  href=http://".$row['www']." target=\"_blank\">".$row['www']."</a>"; 
?>

<?php
 echo "<br>";
?>

<?php
 if (!empty($row['bullet1']) ){
        echo $dot;
        }
?>

<?php
 echo($row['bullet1']); 
?>

<?php
 if (!empty($row['bullet2']) ){
        echo $dot;
        }
?>

<?php
 echo ($row['bullet2']); 
?>

<?php
 if (!empty($row['bullet3']) ){
        echo $dot;
        }
?>

<?php
 echo ($row['bullet3']); 
?>

<?php
   $j++;
  }  while($row = mysql_fetch_array($result));  
}
?>

<?php
 echo "<b>$count</b>"; echo "&nbsp;results for&nbsp;"; echo "<b>\"$post_id\" </b>";
?>

<?php
//we will use this information to build our navigation list
echo (($page>1)?"<a href='".build_url("view.php", "page", $page-1)."'>prev</a>":"prev")." | ";
for($i=1; $i<=($count/$records_per_page)+1; $i++){
  if($i!=$page)
    echo "<a href='".build_url("view.php", "page", $i)."'>$i</a>";
  else
    echo $i;
  //output a little symbol (|) to seperate the links
  //but not for the last link
  if($i<$count/$records_per_page)
    echo " | ";
}
echo " | ".(($page<$count/$records_per_page)?"<a href='".build_url("view.php", "page", $page+1)."'>next</a>":"next");
?>

</div>
</div>
</div>
</div>

</div>
</div>

</body>
</html>

greg posted this at 13:22 — 30th March 2009.

He has: 1,581 posts

Joined: Nov 2005

If that's your full code, I see a couple of issues:

if (($row['town']) > 0){ If town is NOT numerical then that will always be false.

What are these vars - $zap $tel $mob $e_m $w $fax $dot?
You echo them if some criteria returns true, but I don't see them being set to anything within your code. I.E. they contain no data - NULL.

Such as

<?php
if (!empty($row['bullet3']) ){
        echo $dot;
        }

echo ($row['bullet3']);
?>

That will echo out $dot, which is a null var so wont actually display anything, and then will echo out $row['bullet3'] regardless of if it was empty or not.

As for your original problem, I don't have time right now to go through all the code, so will have a better look later (unless someone else does)

(I also edited your post to add the code tags, third from the right on the posting tools. If you don't wrap html and Javascript in the code tags it wont display on your post.)