Query and query navigation/paging | PHP Mysql

Use the PEAR pager package

http://pear.php.net/package/Pager/redirected


Hope this helps.
Thanks
Addy

here is a solution for you. make sure that you have pear Pager calls installed

read following code. and modify / use accordingly

<?php

// connect with db etc

function getPagedData($qry_rs, $pager_options=array()) {
    global $conf;

    $pager_options['totalItems']            = mysql_num_rows($qry_rs);

    $pager_options['mode']                  = 'Sliding';
    $pager_options['delta']                 = 3;
    $pager_options['perPage']               = 20;
    $pager_options['spacesBeforeSeparator'] = 0;
    $pager_options['spacesAfterSeparator']  = 0;
    $pager_options['curPageSpanPre']        = '<span class="currentPage">';
    $pager_options['curPageSpanPost']       = '</span>';

    $pager_options['curPageSpanPost']       = '</span>';
    $pager_options['curPageSpanPost']       = '</span>';

    $pager_options['altPrev'] = 'Previous';
    $pager_options['altNext'] = 'Next';
    $pager_options['altPage'] = 'Page';
    $pager_options['prevImg'] = '&laquo; previous';
    $pager_options['nextImg'] = 'next &raquo;';

    require_once 'Pager/Pager.php';
    $pager = Pager::factory($pager_options);

    $page = array();
    $page['totalItems'] = $pager_options['totalItems'];
    $page['links'] = str_replace("/pageID/".$pager->getCurrentPageID()."/", "/", $pager->links);
    $page['page_numbers'] = array(
        'current' => $pager->getCurrentPageID(),
        'total'   => $pager->numPages()
    );
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();

    $page['data'] = array();
    mysql_data_seek($qry_rs, $page['from'] - 1); // -1 ? cos mysql row number start from zero

    for ($i = 1; $i <= $pager_options['perPage']; $i++) {
        if ($row = mysql_fetch_assoc($qry_rs)) {
            $page['data'][] = $row;
        }
    }

    return $page;
}


$query  = "SELECT * FROM tbl_table";
$result = mysql_query($query);
$cPage  = getPagedData($result)
    
?>
<!-- use code below where you want to display navigation -->
<span class="results">Displaying results <?php echo $cPage['from']; ?> to <?php echo $cPage['to']; ?> from a total of <?php echo $cPage['totalItems']; ?></span>
<?php echo $cPage['links']; ?>

<!-- use code below where you want to put lines/rows
<?php foreach ($cPage['data'] as $key => $aValue) { ?>
    <td><?php echo $aValue['column_name1'] ?></td>
<?php } ?>

Select allOpen in new window

SELECT * FROM movies LIMIT 0,20;  # Retrieve rows 1-20
..............
SELECT * FROM movies LIMIT 80,100;  # Retrieve rows 81-100

i.e. use SELECT * FROM movies LIMIT m,n; and substitute different values for different ranges in your code on loading page as well as on clicking on NEXT button.

Sorry, minor correction:

SELECT * FROM movies LIMIT 0,20 will retrieve records 1-20
..............
SELECT * FROM movies LIMIT 80,20;  # Retrieve rows 81-100

Here is how to do in PHP using MySQL:
Pagination of MySQL Query Results
http://php.about.com/od/phpwithmysql/ss/php_pagination.htm
Pagination of Data (Paging) using PHP (Hyper Text Pre Processor) and MySQL (the great database)
http://usefulscripts.wordpress.com/2007/10/26/pagination-using-php-mysql/
Pagination - what it is and how to do it step by step
http://www.tonymarston.net/php-mysql/pagination.html
PHP Pagination Script
http://phpsense.com/php/php-pagination-script.html

Great stuff

alex_code,

Not the exact one I searched but it's useful and if u got any idea about what I searched(paging like in DIGG) please suggest me a one. Btw seems there's nothing to ask here every question has been asked and all we have to do is search them :P. Again alex_code I owe you a one.

And in ur answer in that thread,

$qSearch =mysql_query("SELECT * FROM mytable order by updated ASC");
   

     $maxres=mysql_num_rows($qSearch);
 
    $qSearch = mysql_query("SELECT * FROM mytable order by updated ASC LIMIT ".($page-1)*$Limit.",$Limit");


y 2 queries ?

One reason is to be more clear and 2nd (important) if you see i get the num rows into the 1st result of the query and then Limit them to use it after for the paging numbers.

@alex,

I have this query,

$qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='".$_GET['g']."'".($page-1)*$Limit.",$Limit");

can u help me to integrate that with the script ?

OK,

$qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'");

if I do - mysql_fetch_assoc($qSearch);  - no porbs

BUT

$qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'".($page-1)*$Limit.",$Limit");

if I do - mysql_fetch_assoc($qSearch);  - no porbs

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL res.....

Whay is that

This warning has to do when a variable is empty, i think that you don't set anywhere the $page.

Before the query put this:

        $page=$_GET["page"];
        If($page == "") $page=1;

Also set and the limit var

$Limit=5;;

Sorry i put one more ; by accident :-(

$Limit=5;

Hello,

Yea I have those.

Here's the code

$gen = $_GET['g'];


//mysql_select_db("video_db") or die(mysql_error());
//$res = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'");
//$final = mysql_fetch_assoc($res);

$Limit=4;//here is the max num of results...

$page=$_GET["p"];

If($page == "") $page=1;

$link = mysql_connect("localhost", "root", "") or die(mysql_errno() . ": ". mysql_error());

mysql_select_db("video_db", $link) or die(mysql_errno() . ": ". mysql_error());

$qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'");
        
$maxres = mysql_num_rows($qSearch);
 
$qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'".($page-1)*$Limit.",$Limit");


-------------------------------------------------------------------------------------------------------
when I run it  "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource....."

@alex_code

I noticed that 2nd query has "LIMIT"

This is my 1st query -  $qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'");

This is my 2nd (this gives the prob) - $qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='". $gen ."'".($page-1)*$Limit.",$Limit");

Where should I type LIMIT ?  

You have syntax mistakes to the 2nd query and you didn't put the LIMIT.

Here is the right query.

  $qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where genre_movies.genre_id=$gen LIMIT ".($page-1)*$Limit.",$Limit");

Tell me if this works because i think you have mistakes into the JOIN syntax also but i don't know the structure of your database and i am not sure about that.

Hi, worked this,

$qSearch = mysql_query("select * from movies movies LEFT JOIN genre_movies  genre_movies ON movies.id= genre_movies.movie_id where  genre_movies.genre_id='".$gen."' LIMIT ".($page-1)*$Limit.",$Limit");

Sorry i erase with the paste of your var the quotes :-(

The LIMIT works?It is everything ok now?

Yep, Works well  Thanks

Glad to help you!!