Splitting query results (like search results)

Hi there, I have been trying to split the results of this code for about 2 days now:

what I am trying to do is have the URL show like this:

and show 100 results per page, does anyone know how I can have this accomplished? Thanks in advance.

 

This is what I have

		define("DEFAULT_NUM_RECS", 100);
		
		$link_id = db_connect($default_dbname);

	
		$sql = "select * from products";

		$sql_no_limit = $sql;
		
		if($page <= 1) {
			$page = 1;
			$sql = $sql . " LIMIT 0, " . DEFAULT_NUM_RECS;
      	}else {
			$sql = $sql . " LIMIT " . (($page-1) * DEFAULT_NUM_RECS) . ", " . DEFAULT_NUM_RECS;
      	}

		$result = mysql_query($sql);
		if($result) $hasRecords = mysql_num_rows($result) == 0 ? false : true;

		if($hasRecords)	{

			while($row = mysql_fetch_array($result)) {
				echo $row["product_name"];
			}
  			$numTotalRecs = mysql_num_rows(mysql_query($sql_no_limit));
  			$numPages = ceil($numTotalRecs / DEFAULT_NUM_RECS);
  			$nav = "";

  			// a link to the previous page?
  			if($page > 1)
    		$nav .= "<a href='$PHP_SELF?page=" . ($page-1) . "'>&lt;&lt; Prev</a> |";

  			for($i = 1; $i < $numPages+1; $i++) {
    			if($page == $i) {
      				// Bold the current page
      				$nav .= " <b>$i</b> |";
    			}else {
      				$nav .= " <a href='$PHP_SELF?page=$i'>$i</a> |";
    			}
  			}

  			if($page < $numPages)
    			$nav .= " <a href='$PHP_SELF?page=" . ($page+1) . "'>Next &gt;&gt;</a>";

			if(substr($nav, strlen($nav)-1, 1)=="|")
  				$nav = substr($nav, 0, strlen($nav)-1);

      		echo "<br>Pages: $nav";

		}else {
	        // No records returned from the query
			echo "No records found.";
		}

PHP:

I stripped some of the formatting I have in my codes. Hopefully didn't break the code too much

 

thanks very much, but i think im in way over my head on this one, im not even getting close I tried for like 45 minutes to get this to work i keep getting a no results page, its not mySQL i know because my code works except it shows all results on same page, I know its asking alot but could someone write the code that I would use for my site, all the informations in my first post, what I was trying to show is 100 results a page, I would do it if I knew how but I don't, thank you very much in advance.

 

Try this:

define("DEFAULT_NUM_RECS", 100);

$dbase['host'] = 'localhost'; 
$dbase['user'] = 'pgn_SEARCH'; 
$dbase['pass'] = '***'; 
$dbase['name'] = 'pgn_SEARCH';

$link_id = mysql_connect($dbase['host'], $dbase['user'],$dbase['pass']);
mysql_select_db($dbase['name'], $link_id);


$sql = "SELECT DISTINCT query FROM 'searchtable' WHERE query like 'a%' ORDER BY 'query' ASC";

$sql_no_limit = $sql;

if($page <= 1) {
	$page = 1;
	$sql = $sql . " LIMIT 0, " . DEFAULT_NUM_RECS;
}else {
	$sql = $sql . " LIMIT " . (($page-1) * DEFAULT_NUM_RECS) . ", " . DEFAULT_NUM_RECS;
}

$result = mysql_query($sql);
if($result) $hasRecords = mysql_num_rows($result) == 0 ? false : true;

if($hasRecords)	{

	while($row = mysql_fetch_array($result)) {
		echo "put data here<br>";
	}
	$numTotalRecs = mysql_num_rows(mysql_query($sql_no_limit));
	$numPages = ceil($numTotalRecs / DEFAULT_NUM_RECS);
	$nav = "";

	// a link to the previous page?
	if($page > 1)
	$nav .= "<a href='$PHP_SELF?page=" . ($page-1) . "'>&lt;&lt; Prev</a> |";

	for($i = 1; $i < $numPages+1; $i++) {
		if($page == $i) {
			// Bold the current page
			$nav .= " <b>$i</b> |";
		}else {
			$nav .= " <a href='$PHP_SELF?page=$i'>$i</a> |";
		}
	}

	if($page < $numPages)
		$nav .= " <a href='$PHP_SELF?page=" . ($page+1) . "'>Next &gt;&gt;</a>";

	if(substr($nav, strlen($nav)-1, 1)=="|")
		$nav = substr($nav, 0, strlen($nav)-1);

	echo "<br>Pages: $nav";

}else {
    // No records returned from the query
	echo "No records found.";
}

PHP:

 

Thank you so much for taking the time to write that for me, but still, when I navigate to it I get this: No records found. but I know thats not true because I have 3 results that start with the letter A, I navigated to this page maybe its wrong

http://www.ewobble.com/search/joe.php?page=1

 

Put echo $sql; just before the line $result = mysql_query($sql);
to see the actual sql being run.

Copy and paste the sql in your mysql tool (e.g. phpmyadmin) and run it. See if it return any result or not.

 

now i get this: SELECT DISTINCT query FROM 'searchtable' WHERE query like 'a%' ORDER BY 'query' ASC LIMIT 0, 100No records found. it's alright, i dont think it will be that bad to have everything listed on one page, maybe not good for SEO but i dont think it will hurt that much either... thanks for your help

 

Since you got the sql, run it in your phymyadmin to see if it throws any error.

 

This should get you the first 100 rows.

$offset=($page-1)*$requiredrows;
$getit = mysql_query("SELECT DISTINCT query FROM `searchtable`
WHERE query like '$letter%' ORDER BY `query` ASC LIMIT $offset,$requiredrows") or die(mysql_error());

Code (markup):