Pagination with 4 SQL queries

[petenaylor]

Hi all

 

I have a site with 4 SQL queries that I need to paginate so that it returns 10 hits per page maximum. Basically there will be varying values of adverts in each SQL table.

 

Here's my code:

 

<!-- Get Trader premium adverts -->
   
   <?php 
 $gettraderads = mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'premium' AND paid = 1 AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 $numtraderads = mysql_num_rows($gettraderads);
 while ($showtraderads = mysql_fetch_array($gettraderads)) {
include('trader-ad-cell.php'); 
 }
?>
    
    <!-- Get Trader standard adverts -->
   
   <?php 
 $gettraderads = mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'standard' AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 $numtraderads = mysql_num_rows($gettraderads);
 while ($showtraderads = mysql_fetch_array($gettraderads)) {
include('trader-ad-cell.php'); 
 }
?>
    
    <!-- Get premium adverts -->
    
    <?php 
 $getads = mysql_query("SELECT * FROM `adverts` WHERE categoryid = 1 AND type = 'premium' AND paid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 while ($showads = mysql_fetch_array($getads)) {
 include('ad-cell.php'); 
 }
?>
    
    <!-- Get standard adverts -->
   
    <?php 
 $getads = mysql_query("SELECT * FROM `adverts` WHERE categoryid = 1 AND type = 'standard' AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 while ($showads = mysql_fetch_array($getads)) {
 include('standard-ad-cell.php'); 
 }
?>

 

Many thanks for your help

 

Pete

 

 

[Maq]

So what's the problem?

[petenaylor]

Hi there

 

At the moment it brings all results back, not 10 per page.

 

Thanks

Pete

[Drummin]

I don't see any "pagination" in your scripting, but as far as showing only ten results add limit 10 to the end of your query.  Also keep those variable names unique for each query, i.e. $gettraderads, $gettraderads2 etc.

$gettraderads = mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'premium' AND paid = 1 AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC limit 10");

[Maq]

You need to check out mysql LIMIT, and pagination:

http://www.phpfreaks.com/tutorial/basic-pagination

[QuickOldCar]

I think the first thing to get do is to set up each of your select queries in if/elseif/else depending what ad type need to display.

http://php.net/manual/en/control-structures.elseif.php

 

Then you can move onto reading about pagination for that one select result.

http://www.phpfreaks.com/tutorial/basic-pagination