Query and query navigation/paging | PHP Mysql

Hello,

Movies Table
[id]        [name]         [info]
 1          name1         info 1
 2          name2         info 2
 3          name3         info 3
                   .
                   .
                   .

I have a query(mysql)... .

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$sql = "SELECT * FROM movies";
mysql_query($sql,$con);

Let's say I have 100 records in this query. I want to show them like Google results, I mean paged, 20 results per page.

and the page navigation has to be something like this http://tinyurl.com/ykywb33
[Take a look at it's navigation for data]

Need the full code please.

Thanks
sahanlakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Avinash ZalaWeb ExpertCommented:
Use the PEAR pager package

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


Hope this helps.
Thanks
Addy
0
nasirbestCommented:
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 } ?>

Open in new window

0
k_murli_krishnaCommented:
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

k_murli_krishnaCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sahanlakAuthor Commented:
Great stuff
0
sahanlakAuthor Commented:
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.
0
sahanlakAuthor Commented:
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 ?
0
AlexSoftware EngineerCommented:
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.
0
sahanlakAuthor Commented:
@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 ?
0
sahanlakAuthor Commented:
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

0
AlexSoftware EngineerCommented:
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;
0
AlexSoftware EngineerCommented:
Also set and the limit var

$Limit=5;;
0
AlexSoftware EngineerCommented:
Sorry i put one more ; by accident :-(

$Limit=5;
0
sahanlakAuthor Commented:
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....."
0
sahanlakAuthor Commented:
@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 ?  
0
AlexSoftware EngineerCommented:
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.
0
sahanlakAuthor Commented:
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");
0
AlexSoftware EngineerCommented:
Sorry i erase with the paste of your var the quotes :-(

The LIMIT works?It is everything ok now?
0
sahanlakAuthor Commented:
Yep, Works well  Thanks
0
AlexSoftware EngineerCommented:
Glad to help you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.