display records in separated pages

[kharbat]

Hi there,

Assuming that we have 100 row in a table..
can we display them in 5 pages?

first page shows the first 20 records (rows) , the second page shows the second 20 records and so on..

it is exactly like the inbox idea.. to orgnize mesages and separate each amount of message in a page

the technical question is how to let the query understand that at the first page we need to retrieve rows from 0 until 20

and the second page should retrieve rows 20 until 40.. and so on


example code:

[code]

$sql = "SELECT `Sender`, `Subject` , `Date` FROM `inbox`";
$query = mysql_query($sql,$link);
$total = mysql_num_rows($query);

for( $i = 0; $i < $total; $i++)
{
   $message_row[$i] = mysql_fetch_array($query);
}


[/code]

[jeremywesselman]

You will need to use the 'LIMIT' keyword in MySQL.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] Sender, Subject , Date [color=green]FROM[/color] [color=orange]inbox[/color] [color=green]LIMIT[/color] [color=orange]0, 20[/color] [!--sql2--][/div][!--sql3--]

This query will return from row 0 to row 20. The 0 represents the starting row, and the 20 represents the quantity of rows to be returned. To show records 21 - 40, your query would look something like this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] Sender, Subject , Date [color=green]FROM[/color] [color=orange]inbox[/color] [color=green]LIMIT[/color] [color=orange]21, 20[/color] [!--sql2--][/div][!--sql3--]

[!--coloro:#990000--][span style=\"color:#990000\"][!--/coloro--]Jeremy[!--colorc--][/span][!--/colorc--]

[kharbat]

Thank you Jeremy for your reply..

I will try your solution and reply again to you..

[kharbat]

Thanks again Jeremy... It works

Best regards

[maexus]

Good luck with it. I hated working on pagination so I just keep the code on hand so I don't ever have to do it again.

[kharbat]

Sorry! it didn't work...

I thought it worked because the first page returned correct results, but when i tried the code with different values i returned wierd results

for example.. i have 66 message in the mail inbox should be divided on 4 pages.. while each page show 20 messages.

the first page includes the query: "SELECT * FROM `inbox` LIMIT 0,19"
it returned the first 20 rows correctly..

the second page includes the query: "SELECT * FROM `inbox` LIMIT 20,39"
it returned 40 rows !!!!!!!

the third page includes the query: "SELECT * FROM `inbox` LIMIT 40,59"
it returned 26 rows !!

in fact i don't understand anymore what does what keyword "LIMIT" do ,, and i think that i still need another solution .. [img src=\"style_emoticons/[#EMO_DIR#]/unsure.gif\" style=\"vertical-align:middle\" emoid=\":unsure:\" border=\"0\" alt=\"unsure.gif\" /]

[brown2005]

what is the table name u want and the fields u want to display mate?

[.josh]

the keyword you want to search for is 'pagination' there is a good pagination tutorial in the tutorial section.

[brown2005]

email me on [email protected] and i tell you

[redarrow]

There you go another idear ok
[code]

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// This sets the number of seen results ok.

$max_results = 1;


$from = (($page * $max_results) - $max_results);


// select your correct field
$query="select * from inbox where id='$id' LIMIT $from, $max_results";
$result=mysql_query($query);

loop thorw the database and use assoc to get data out.

while($record=mysql_fetch_assoc($result)){
echo"<table width='200'border='4' bordercolor='black'>
<td align='center'><b>what ever </b>

sender<br>".$record['sender']."<br>  


Subject<br>".$record['subject']." <br>  

date<br>".$record['date']." </td><table>";

}


$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM member_messages

where id='$id'"),0);


$total_pages = ceil($total_results / $max_results);


echo "<center><b>Select What ever!</b><br>";


if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">Previous>></a>";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}


if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
}
echo "</center>";


[/code]

[kharbat]

I am so sorry guys... It seems that i had a missunderstanding [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]
it works actually, but i was using the keyword LIMIT in a wrong way..

thanks all for help! and here is the code i used to show 20 message per page

[code]
    // Setting Page number
    
    if(!isset($_GET['page']))
        $_GET['page'] = 1;
    
// ------------------------------------------------------------------------ END
    
    
    // Determining # of Pages Required
    $sql = "SELECT * FROM `".$_SESSION['username']."` WHERE `MessageType` ='inbox'";
    $query = mysql_query($sql,$Connection); // Global
    $total_inbox = mysql_num_rows($query);
    
    $mod = $total_inbox % 20;
        if( $mod == 0)
            $pages = $total_inbox / 20; // Number of Pages    
        else
            $pages = floor($total_inbox/20)+1; // Number of Pages

// ------------------------------------------------------------------------ END
            

    // Not to show page that exceeds pages number
    if($pages > 1 && $_GET['page'] > $pages)
        $_GET['page'] = $pages;

// ------------------------------------------------------------------------ END
        
        // Determining Messages to Display according to Page Number
        $limit = " LIMIT ".(20*($_GET['page']-1)).",20";
        
// ------------------------------------------------------------------------ END

        
        // Retrieving messages
        
        $messages_sql = "SELECT * FROM `".$_SESSION['username']."` WHERE `MessageType` ='inbox'".$limit;
        $messages_query = mysql_query($messages_sql);
        $rows_num = mysql_num_rows($messages_query); // Number of messages retrieved

[/code]