Jump to content

display records in separated pages


kharbat

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.