Jump to content

Archived

This topic is now archived and is closed to further replies.

kharbat

display records in separated pages

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
Thank you Jeremy for your reply..

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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\" /]

Share this post


Link to post
Share on other sites
the keyword you want to search for is 'pagination' there is a good pagination tutorial in the tutorial section.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.