Jump to content


Photo

display records in separated pages


  • Please log in to reply
10 replies to this topic

#1 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 11 June 2006 - 08:52 AM

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:


$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);
}




#2 jeremywesselman

jeremywesselman
  • Members
  • PipPipPip
  • Advanced Member
  • 154 posts
  • LocationIndependence, KY

Posted 11 June 2006 - 10:09 AM

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 FROM inbox LIMIT 0, 20 [!--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 FROM inbox LIMIT 21, 20 [!--sql2--][/div][!--sql3--]

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

#3 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 11 June 2006 - 11:01 AM

Thank you Jeremy for your reply..

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

#4 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 11 June 2006 - 03:14 PM

Thanks again Jeremy... It works

Best regards

#5 maexus

maexus
  • Members
  • PipPipPip
  • Advanced Member
  • 191 posts

Posted 11 June 2006 - 03:38 PM

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.

#6 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 11 June 2006 - 05:55 PM

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\" /]


#7 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 11 June 2006 - 06:03 PM

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

#8 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 11 June 2006 - 06:12 PM

the keyword you want to search for is 'pagination' there is a good pagination tutorial in the tutorial section.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#9 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 11 June 2006 - 06:15 PM

email me on rberbe2002@msn.com and i tell you

#10 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 11 June 2006 - 06:22 PM

There you go another idear ok

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>"; 



Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#11 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 11 June 2006 - 07:13 PM

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

    // 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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users