Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Pagination problem

pagination php list

This topic has been archived. This means that you cannot reply to this topic.
2 replies to this topic

#1 isuru

isuru

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 249 posts

Posted 21 April 2013 - 12:06 PM

Hi!

 

I am trying to run the pagination code I created, but I can't figure out how to get all items from table separated to pages(pagination).

 

Following is the code.

public function getCatItemsByPage($cat, $page) {
        $query = "SELECT 
                    id
                  FROM 
                    categories
                  WHERE
                    name = '$cat'";
                  

        $result = mysql_query($query) or die(mysql_error());
              
        $id = mysql_result($result, 0, "id");
        
        
	 if($page == 1){
		$limit_start = 0;
		$limit_stop = 52;
	 }else if($page == 2){
		$limit_start = ($page - 1) * 52;
		$limit_stop = $limit_start + 52; 
	 }else{
		$limit_start = $page * 52;
		$limit_stop = $limit_start + 52; 
	 }
        
        $output_videos_query = "SELECT 
                   *
                  FROM 
                    videos
                  WHERE
                    cate_id=$id
                  ORDER BY
                    created_at
                  DESC LIMIT 
                    $limit_start, $limit_stop";
        
        $output_videos_result = mysql_query($output_videos_query) or die(mysql_error());
        
        return $output_videos_result;
        
    }

I pass category($cat) and page number($page). And the problem is how to calculate $limit_start and $limit_stop by page. I spent last hour trying to figure out how to get videos, but some pages don't get some videos or some pages get lots of videos.

 

Any idea on this, really appreciated.


Lost!

#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 21 April 2013 - 12:51 PM

 
        $limit_start = ($page-1) * 52;
        $limit_stop = $limit_start + 52;
 

 

That should work for any page



#3 JasonKnight

JasonKnight

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 312 posts

Posted 21 April 2013 - 05:52 PM

Uhm, the second parameter of LIMIT isn't the end, it's how MANY.

You want 52 per page, that would be:
DESC LIMIT $limit_start,52

When limit has one parameter, it's how many. When it's two parameters it's start and how many! As such the only thing you should need to calculate is the START.

$count=52;
$limit_start=$page*$count;

DESC LIMIT $limit_start,$count

LIMIT 10 -- grabs ten records from start
LIMIT 20,10 -- grabs records 20 to 30 -- aka 10 records starting at #20

Side note -- You probably shouldn't be using the deprecated mysql_ functions they've been telling us for eight years to stop using, particularly if you're plugging variables into your queries -- that's why we have prepared queries! In case you hadn't heard or noticed the big red warning boxes in the manual -- though I'm quite sure people will still refuse do stop doing it even when they disappear wholesale in a future PHP release. (alongside the eReg functions). Just saying...

I'd also suggest easing up on the variables and pointless IF statements.

Just to show you what I mean, here's how I'd kind-of be handling that in PDO. (I'd probably NOT be passing the result handler as the return)
 
public function getCatItemsByPage($cat, $page) {

	/* 
		Assumes this method's class has a $db property that is an 
		initialized and connected PDO object
	 */
	 
	$statement =  $this->db->prepare('
		SELECT id FROM categories
		WHERE name = :name
	');
	
	$statement->execute(array(
		':name' => $cat
	));
	
	$id = $statement -> fetchColumn();
	
	if (--$page < 0) $page = 0;
	
	$statement = $this->db->prepare('
		SELECT * FROM videos
		WHERE cate_id = :id
		ORDER BY created_at
		DESC LIMIT :start, :count
	');
	
	$statement->execute(array(
		':id' = $id,
		':start' => $page * 52,
		':count' => 52
	));
	
	return $statement;
}


Oh, forgot to mention, "emulated" prepared queries (the default) don't let you set limit values properly, so my PDO code would need this:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

before it -- usually I do that right after initializing it.

Edited by JasonKnight, 21 April 2013 - 05:46 PM.

The only thing about Dreamweaver that can be considered professional grade tools are the people promoting it's use.




Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download