SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using LIMIT to extract the correct DB record

    Hi I wonder if anyone can help I have an application which extracts the number of news entries in a database and creates pagination automatically. It allows the user to go backwards and forwards through the stack.


    Code:
    The SQL code is
    
    // get id from previous page
    $newsid = $_GET['newsid'];
    
    
    // connect to careers DB
    mysql_select_db("dbcareers");
    
    
    // dynamically assign ids for navigation
    $query_gid = "
    	SELECT *
        FROM tblnews
    	WHERE publish_date <= now()
    	AND tblnews.hidden = 0	
    	ORDER BY publish_date ASC";
    
    
    $result_gid = mysql_query($query_gid);
    
    // create variables
    $dynamicid = 1;
    $currentid = 0;
    
    
    while ($row_gid = mysql_fetch_array($result_gid)) {
    
       $title[$dynamicid] = $row_gid['title'];
    
      // get id for current record
        if ($row_gid['newsid'] == $newsid) {
          $currentid = $dynamicid;
    	  //echo "current record: " . $currentid;
      }
    
     // set a dynamic PK for each record
     $dynamicid = $dynamicid + 1;
    
    }
    
    //echo "</ul>";
    
    //number of records to show per page
    $display = 1;
    
    
    // determine how many records there are
    if (isset($_GET['nr'])) {
           $num_records = $_GET['nr'];
    } else {			
    	   $query_gnumrecords = "SELECT publish_date AS publish_date
           FROM tblnews 
    	   WHERE publish_date <= now()
    	   AND tblnews.hidden = 0
    	   " ;
    	
    	   // execute the query
           $result_gnumrecords = @mysql_query($query_gnumrecords);
    	 
    	   //find the number of records
           $num_records = @mysql_num_rows($result_gnumrecords);	     
    	
    }
    
    
    // determine where in the database to start returning results
    
    // already been determined
    if (isset($_GET['s'])) {
          $currentid = $_GET['s'];	  
    } else {
          $currentid = $currentid - 1;
    	  
    	  /* subtract 1 from start initially. Current record will always be in the range 0 to (number of records - 
    	  1) because the dynamic SQL statement always extracts the record AFTER the start parameter  */ 
    	  //$start = ($start - 1);
    }
    
    
    
    // use dynamic SQL for back/previous link submission
    if (isset($_GET['link'])){	   
    	   $query_gnewsentry = "
                  SELECT title,
    		      body,
    		      UNIX_TIMESTAMP(tblnews.publish_date) AS publish_date,
    			  imageid
    		      FROM tblnews	
    			  ORDER BY publish_date ASC			  			   
    			  LIMIT $currentid, $display			  	
    			  ";	
    
    
    // select news record based on passed id
    } else {
             $query_gnewsentry = "
                  SELECT title,
    		      body,
    		      UNIX_TIMESTAMP(tblnews.publish_date) AS publish_date,
    			  imageid
    		      FROM tblnews
    		      WHERE newsid = '$newsid' 			  	 
    			  LIMIT 1";		
    }
    
    The important bit to note is the  
    
    if (isset($_GET['link'])){	   
    	   $query_gnewsentry = "
                  SELECT title,
    		      body,
    		      UNIX_TIMESTAMP(tblnews.publish_date) AS publish_date,
    			  imageid
    		      FROM tblnews	
    			  ORDER BY publish_date ASC			  			   
    			  LIMIT $currentid, $display			  	
    			  ";	
    
    
    // select news record based on passed id
    }

    $currentid is the number of the record extracted from the DB. Now this is fine as long as the number of records extracted from the DB is the same as the dynamically created array:

    Code:
    while ($row_gid = mysql_fetch_array($result_gid)) {
    
       $title[$dynamicid] = $row_gid['title'];
    
      // get id for current record
        if ($row_gid['newsid'] == $newsid) {
          $currentid = $dynamicid;
    	  //echo "current record: " . $currentid;
      }
    
     // set a dynamic PK for each record
     $dynamicid = $dynamicid + 1;
    
    }
    Which allows the user to move up and down the stack with this nav:

    Code:
    <?php 
       
     /* The current record has to be in the range 0 to (number of records - 1) because the dynamic SQL statement always extracts the record AFTER the start parameter. See above */ 
    
    // if there is more than one page
    if ($num_records > 1) {
    
    echo "<ul>"; 
    
          // if its not the last record make a next button 
          if (($currentid + 1) != $num_records) {
                echo "<li><a href=\"" . $_SERVER['SCRIPT_NAME'] . "?s=" . ($currentid + $display)  . "&amp;link=1\">". $title[($currentid + $display + 1)]  . "</a></li>";		
    	   
    }
    
    
          // if it's not the first record make a previous button	   
    	  if ($currentid != 0) {
    	         echo "<li><a class=\"previous\" href=\"" . $_SERVER['SCRIPT_NAME'] ."?s=" . ($currentid - $display) . "&amp;link=1\">" . $title[($currentid + $display - 1)] . "</a></li>"; 
    	   }
    }
    
    echo "</ul>";
      
    ?>
    But sometimes the number of records extracted may not match the number of records in the DB e.g. hidden records may not be included (WHERE publish_date <= now() AND tblnews.hidden = 0). In this case LIMIT $currentid, $display will be out of synchronisation.

    Does anyone have any ideas?

    Thanks
    Steven

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i don't understand what you code is doing exactly, but this might help you. let's say you have this query:
    Code:
    select body
         , author
      from news_articles
     where public = 1
     limit $offset, $width
    you can add sql_calc_found_rows to find out how many rows you would have gotten without the limit:
    PHP Code:
    $query "select sql_calc_found_rows
                     body
                   , author
                from news_articles
               where public = 1
               limit 
    $offset$width";
    $res mysql_query($query) or die(mysql_error());

    $rows_res mysql_query("select found_rows()") or die(mysql_error());
    list(
    $num_rows) = mysql_fetch_array($rows_res); 
    $num_rows now holds the total number of rows the query would have returned with LIMIT.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •