1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Splitting query results (like search results)

Discussion in 'PHP' started by Al Capone, Aug 19, 2005.

  1. #1
    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.SEMrush
     
    Al Capone, Aug 19, 2005 IP
    SEMrush
  2. Connect

    Connect Guest

    Messages:
    191
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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 ;)
     
    Connect, Aug 19, 2005 IP
  3. Al Capone

    Al Capone Well-Known Member

    Messages:
    784
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    140
    #3
    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.
     
    Al Capone, Aug 19, 2005 IP
  4. Connect

    Connect Guest

    Messages:
    191
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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:
    :rolleyes:
     
    Connect, Aug 19, 2005 IP
  5. Al Capone

    Al Capone Well-Known Member

    Messages:
    784
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    140
    #5
    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
     
    Al Capone, Aug 19, 2005 IP
  6. Connect

    Connect Guest

    Messages:
    191
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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.
     
    Connect, Aug 20, 2005 IP
  7. Al Capone

    Al Capone Well-Known Member

    Messages:
    784
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    140
    #7
    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
     
    Al Capone, Aug 20, 2005 IP
  8. Connect

    Connect Guest

    Messages:
    191
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Since you got the sql, run it in your phymyadmin to see if it throws any error.
     
    Connect, Aug 22, 2005 IP
  9. SpeedyDomainRegistration

    SpeedyDomainRegistration Peon

    Messages:
    170
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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):