Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    limiting 50 records per page & links to go subsequent pages

    i have a table which populates the data.

    i would like to show 50 records per page, and have buttons or links to click on to view the next 50 records and so on.

    how should i go about doing it??
    [=cHeRyL=]

  • #2
    Regular Coder
    Join Date
    Sep 2005
    Posts
    394
    Thanks
    1
    Thanked 0 Times in 0 Posts
    you'd use the LIMIT of mysql. You would simply have a $_GET variable of the page number, e.g. ?page=3. You would get this out and multiply it by 50 and -50, e.g. $limitStart = $_GET['page'] - 50;

    You would then pass this into your SQL query as the start point...

    $result = mysql_query("SELECT `field1`, `field2` FROM `table` WHERE `whatever`='whatever' ORDER BY `whatever` DESC LIMIT $limitStart,50");

    This will select 50 records of your chosen fields from your table starting from $limitStart.

    ~Phil~

  • #3
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts

  • #4
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by lavinpj1
    you'd use the LIMIT of mysql. You would simply have a $_GET variable of the page number, e.g. ?page=3. You would get this out and multiply it by 50 and -50, e.g. $limitStart = $_GET['page'] - 50;

    You would then pass this into your SQL query as the start point...

    $result = mysql_query("SELECT `field1`, `field2` FROM `table` WHERE `whatever`='whatever' ORDER BY `whatever` DESC LIMIT $limitStart,50");

    This will select 50 records of your chosen fields from your table starting from $limitStart.

    ~Phil~
    attached is the file that i want to display 50 records per page and to have subsquent pages to view the next 50 records and so on.

    will someone please assist in guiding me how to dump the codes to set the limit??

    thank you

    edited

    i've tried, but it gives me an syntax error about LOGICAL something something...

    what is the problem? the class files should be able to work, right??
    Attached Files Attached Files
    Last edited by cheryl; 06-10-2006 at 10:35 AM.
    [=cHeRyL=]

  • #5
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Read this thread...I posted a code & someone made it a little better, but it is Pagination & this is what you need.

    Create new page

  • #6
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by lansing
    Read this thread...I posted a code & someone made it a little better, but it is Pagination & this is what you need.

    Create new page


    er okie... after which line of code should i dump the codes at then??

    i shall put the full display page here. please assist in how do i modify and add the pagination in.

    thanks

    Code:
       <?php
    session_save_path("../servertemp");
    session_start();
    if(empty($_SESSION[login]))
    //check if the user has logged in, if not redirect the user to login page
    {
    	$_SESSION[error]="Access Denied! You need to login first.";
    	header("Location: ../login.php");
    }
    else
    {
    ?>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Participant Listing</title>
    <style type="text/css">
    <!--
    .style1 {
    	font-family: Tahoma, sans-serif;
    	font-size: small;
    }
    -->
    </style>
    </head>
    
    <body>
    <?php $eventName = $_POST["eventName"]; ?>
    <form name="listParticipants" method="get" action="<? $PHP_SELF?>">
      <table width="469" border="0" cellpadding="0" cellspacing="0">
        <tr>
          <td width="469"><span class="style1">Show:</span>        <select name="status" id="status"> View : 
            
            <option <?php if($status =="All") {print "selected";} ?> value="All">All</option>
    		<option <?php if($status =="Approved") {print "selected";} ?> value="Approved">Approved</option>
            <option <?php if($status =="Pending") {print "selected";} ?> value="Pending">Pending</option>
            <option <?php if($status =="Unapproved") {print "selected";} ?> value="Unapproved">Unapproved</option>
          </select> 
            <span class="style1">Sort by:</span>        <select name="sort" id="select2">
              <option <?php if($sort =="partName") {print "selected";} ?> value="partName">Participant's Name</option>
    		  <option <?php if($sort =="partUID") {print "selected";} ?> value="partUID">NRIC/Passport No</option>
    		  <option <?php if($sort =="partMemType") {print "selected";} ?> value="partMemType">Membership Type</option>
    		  <option <?php if($sort =="TotalAmtDue") {print "selected";} ?> value="TotalAmtDue">Total Amount Payable</option>
              <option <?php if($sort =="paymentMode") {print "selected";} ?> value="paymentMode">Mode Of Payment</option>
    		  <option <?php if($sort =="partRegApprove") {print "selected";} ?> value="partRegApprove">Status</option>
    		  
    		  <option <?php if($sort =="partRegTimeStamp") {print "selected";} ?> value="partRegTimeStamp">Date of Registration</option>
            </select>      
          <input type="submit" name="filter" value="Filter"> </td>
        </tr>
      </table>
      <p>
        <?php
    	include("../lib/db_connect.php");
    	include("common.php");
    	$link = @mysql_connect("localhost", $user, $pass);	
    
    	if (!$link)
    		die ("Couldn't connect to MySQL");
    	mysql_select_db($db, $link)
    		or die ("Couldn't open $db".mysql_error());
    	
    	$sql="SELECT * from event where eventCode='$eventCode'";
    	$result=mysql_query($sql)
    				or die ("Couldn't execute query.".mysql_error());
    	$num=mysql_num_rows($result);
    	while($row = mysql_fetch_array($result)) 
    	{
    	$eventName = $row["mainTitle"];
    	}
    	//sql statement for event listing
    	$status = $_GET["status"];
    	$sort = $_GET["sort"];
    	
    	if(!empty($status))
    	{
    		if($status == "All")
    		{
    			if(!empty($sort))
    			{
    				$selectsql="select * from Temp_Participants ORDER BY '$sort'";
    			}
    			else
    			{
    				$selectsql="select * from Temp_Participants";
    			}
    		}
    		else
    		{
    			if(!empty($sort))
    			{
    				$selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status' ORDER BY '$sort'";
    			}
    			else
    			{
    				$selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status'";
    			}
    		}
    	}
    	else
    	{
    		$selectsql="select * from Temp_Participants ORDER BY partRegTimeStamp DESC";
    	}
    	
    		$selectlist_result=mysql_query($selectsql)
    			or die ("Couldn't execute query.".mysql_error()."".$selectsql);
    		$num=mysql_num_rows($selectlist_result);
    		
    		/*include_once("class/pager.class.php");
    	
        $selectsql=str_replace('*', 'COUNT(1)', $selectsql); 
        $max=mysql_query($selectsql); 
        $num=50; 
        $pager = new pager( $max, $num, @$_GET['_p']);  
    
        $selectlist_result=mysql_query($selectsql.' LIMIT '.$pager->get_limit()); 
                or die ("Couldn't execute query.".mysql_error()."".$selectsql); 
    /*if it worked ...*/ 
        //echo $pager->get_title('Page {CURRENT} of {MAX}<br />Results {FROM} to {TO} of {TOTAL}'); 
    	
    		print "<font face=Tahoma,sans-serif size=1>";
    		print "<center><strong><h4>".$eventName."</h4></strong></center>";
    		print "<center><table width=825 border=1 bordercolor=black cellspacing=0 cellpadding=5>";
    		print "<tr bgcolor=orange>";
    		print "<td colspan=1 align=left width=200><b>Participant's Name</b></td>";
    		print "<td colspan=1 align=left width=60><b>NRIC/Passport no</b></td>";
    		print "<td colspan=1 align=left width=50><b>Membership</b></td>";
    		print "<td colspan=1 align=left width=60><b>Total Amount Payable</b></td>";
    		print "<td colspan=1 align=left width=50><b>Payment Mode</b></td>";
    		print "<td colspan=1 align=left width=20><b>Registration Approval</b></td>";
    		print "<td colspan=1 align=left width=20><b>Registration Timestamp</b></td>";
    		print "<td colspan=3 width=100><b>Action</b></td>";
    		print "</tr>";
    		
    		while ($cl_row = mysql_fetch_array($selectlist_result))
    		{
    			$_SESSION[eventCode]=$cl_row['eventCode'];
    			echo "<tr>";
    			echo "<td colspan=1 align=left width=90>".$cl_row['partName']."</td>";
    			echo "<td colspan=1 align=left width=200>".$cl_row['partUID']."</td>";
    			echo "<td colspan=1 align=left width=200>".$cl_row['partMemType']."</td>";
    			echo "<td colspan=1 align=left width=200>$".$cl_row['TotalAmtDue']."</td>";
    			echo "<td colspan=1 align=left width=60>".$cl_row['paymentMode']."</td>";
    			echo "<td colspan=1 align=left width=60>".$cl_row['partRegApprove']."</td>";
    			$friendlysdate = date('j F Y H:i:s', strtotime($cl_row['partRegTimeStamp']));
    			echo "<td colspan=1 align=left width=60>".$friendlysdate."</td>";
    			echo "<td colspan=1 width=30><a href='Partsummary.php?partCode=" .$cl_row['partCode']."'>View</a></td>";
    			echo "<td colspan=1 width=30><a href='editparticipants.php?partCode=" .$cl_row['partCode']."'>Edit</a></td>";
    			echo "<td colspan=1 width=40><a href='confirmdeleteParticipant.php?partCode=" .$cl_row['partCode']."'>Delete</a></td>";
    			echo "</tr>";
    		}
    		
    		print "</table></center></font>";
    		
    ?>
      </p>
    </form>
    </body>
    <?php
    }
    ?>
    </html>
    Last edited by cheryl; 06-11-2006 at 09:29 AM.
    [=cHeRyL=]

  • #7
    Regular Coder goughy000's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    415
    Thanks
    0
    Thanked 0 Times in 0 Posts
    why dont u show us how you tried.. someone will then help u sort out your mistakes AND you will learn why the way you tried is incorect for further coding.

    Also.. php code should go in [PHP ] and [/PHP ] tags.. not [CODE ] and [/CODE ] there is a sticky on this forum noting this
    PHP Freelancing!
    ByteForums!
    Scriptlance.comNeed a job done quick? post it on scriptlance! OR! your a programmer? sign up and get paid for these jobs!

  • #8
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi... i shall post part by part of the code.

    PHP Code:
    //this is the statements where the dropdown list filters the records
    <? 
    $status 
    $_GET["status"];
        
    $sort $_GET["sort"];
        
        if(!empty(
    $status))
        {
            if(
    $status == "All")
            {
                if(!empty(
    $sort))
                {
                    
    $selectsql="select * from Temp_Participants ORDER BY '$sort'";
                }
                else
                {
                    
    $selectsql="select * from Temp_Participants";
                }
            }
            else
            {
                if(!empty(
    $sort))
                {
                    
    $selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status' ORDER BY '$sort'";
                }
                else
                {
                    
    $selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status'";
                }
            }
        }
        else
        {
            
    $selectsql="select * from Temp_Participants ORDER BY partRegTimeStamp DESC";
        }
        
            
    $selectlist_result=mysql_query($selectsql)
                or die (
    "Couldn't execute query.".mysql_error()."".$selectsql);
            
    $num=mysql_num_rows($selectlist_result);
    PHP Code:
    //here is where the records are printed
    print "<font face=Tahoma,sans-serif size=1>";
            print 
    "<center><strong><h4>".$eventName."</h4></strong></center>";
            print 
    "<center><table width=825 border=1 bordercolor=black cellspacing=0 cellpadding=5>";
            print 
    "<tr bgcolor=orange>";
            print 
    "<td colspan=1 align=left width=200><b>Participant's Name</b></td>";
            print 
    "<td colspan=1 align=left width=60><b>NRIC/Passport no</b></td>";
            print 
    "<td colspan=1 align=left width=50><b>Membership</b></td>";
            print 
    "<td colspan=1 align=left width=60><b>Total Amount Payable</b></td>";
            print 
    "<td colspan=1 align=left width=50><b>Payment Mode</b></td>";
            print 
    "<td colspan=1 align=left width=20><b>Registration Approval</b></td>";
            print 
    "<td colspan=1 align=left width=20><b>Registration Timestamp</b></td>";
            print 
    "<td colspan=3 width=100><b>Action</b></td>";
            print 
    "</tr>";
            
            while (
    $cl_row mysql_fetch_array($selectlist_result))
            {
                
    $_SESSION[eventCode]=$cl_row['eventCode'];
                echo 
    "<tr>";
                echo 
    "<td colspan=1 align=left width=90>".$cl_row['partName']."</td>";
                echo 
    "<td colspan=1 align=left width=200>".$cl_row['partUID']."</td>";
                echo 
    "<td colspan=1 align=left width=200>".$cl_row['partMemType']."</td>";
                echo 
    "<td colspan=1 align=left width=200>$".$cl_row['TotalAmtDue']."</td>";
                echo 
    "<td colspan=1 align=left width=60>".$cl_row['paymentMode']."</td>";
                echo 
    "<td colspan=1 align=left width=60>".$cl_row['partRegApprove']."</td>";
                
    $friendlysdate date('j F Y H:i:s'strtotime($cl_row['partRegTimeStamp']));
                echo 
    "<td colspan=1 align=left width=60>".$friendlysdate."</td>";
                echo 
    "<td colspan=1 width=30><a href='Partsummary.php?partCode=" .$cl_row['partCode']."'>View</a></td>";
                echo 
    "<td colspan=1 width=30><a href='editparticipants.php?partCode=" .$cl_row['partCode']."'>Edit</a></td>";
                echo 
    "<td colspan=1 width=40><a href='confirmdeleteParticipant.php?partCode=" .$cl_row['partCode']."'>Delete</a></td>";
                echo 
    "</tr>";
            }
            
            print 
    "</table></center></font>"
    should i be putting the pagination codes like this??

    i placed the codes before the table is printed.

    like this:

    PHP Code:
    if(isset($_GET['page'])) 
          { 
              $page = $_GET['page']; 
              $page2 = $page; 
              $page2--; 
              $show = 5; // Number of post or records per page 
              $skip = $page2*$show; 
          } 
          else 
          { 
              $page = 1; 
              $page2 = $page; 
              $page2--; 
              $show = 5; // Number of post or records per page if the $_GET['page'] isn't set!! 
              $skip = $page2*$show; 
          } 

          $sql = mysql_query("SELECT * FROM table ORDER BY `column_name` ASC LIMIT $skip,$show"); 
          $sql_all = mysql_query("SELECT * FROM table"); 
          $rows = @mysql_num_rows($sql_all); 
          $maxpage = ceil($rows/$show); 
          $bgcolor = "#FFFFFF"; 
          while($row = @mysql_fetch_assoc($sql)) 
          { 
            $colum_1 = $row["colum_1"]; 
            $colum_2 = $row["colum_2"]; 
            $colum_3 = $row['colum_3']; 
            $colum_4 = $row["colum_4"]; 
            $colum_5 = $row["colum_5"]; 
            if ($bgcolor == "#FFFFFF"){ 
                $bgcolor = "#CACACA"; 
            }else{ 
                $bgcolor = "#FFFFFF"; 
            } 
          echo' 
            <tr bgcolor="'.$bgcolor.'"> 
            <td align="center">' . $colum_1 . '</td> 
            <td align="center">' . $colum_2 . '</td> 
            <td align="center">' . $colum_3 . '</td> 
            <td align="center">' . $colum_4 . '</td> 
            <td align="center">' . $colum_5 . '</td> 
            </tr>'; 
          } 

          ?> 
          Page: 
          <? 
          
    while($x <= $maxpage
          { 
              echo 
    '<a href=" ' $_SERVER['PHP_SELF'] . 'page=' $x '">' $x '</a> '
              
    $x++; 
          } 
            } 

    //here is where the records are printed
    print "<font face=Tahoma,sans-serif size=1>";
            print 
    "<center><strong><h4>".$eventName."</h4></strong></center>";
            print 
    "<center><table width=825 border=1 bordercolor=black cellspacing=0 cellpadding=5>";
            print 
    "<tr bgcolor=orange>";
            print 
    "<td colspan=1 align=left width=200><b>Participant's Name</b></td>";
            print 
    "<td colspan=1 align=left width=60><b>NRIC/Passport no</b></td>";
            print 
    "<td colspan=1 align=left width=50><b>Membership</b></td>";
            print 
    "<td colspan=1 align=left width=60><b>Total Amount Payable</b></td>";
            print 
    "<td colspan=1 align=left width=50><b>Payment Mode</b></td>";
            print 
    "<td colspan=1 align=left width=20><b>Registration Approval</b></td>";
            print 
    "<td colspan=1 align=left width=20><b>Registration Timestamp</b></td>";
            print 
    "<td colspan=3 width=100><b>Action</b></td>";
            print 
    "</tr>";
            
            while (
    $cl_row mysql_fetch_array($selectlist_result))
            {
                
    $_SESSION[eventCode]=$cl_row['eventCode'];
                echo 
    "<tr>";
                echo 
    "<td colspan=1 align=left width=90>".$cl_row['partName']."</td>";
                echo 
    "<td colspan=1 align=left width=200>".$cl_row['partUID']."</td>";
                echo 
    "<td colspan=1 align=left width=200>".$cl_row['partMemType']."</td>";
                echo 
    "<td colspan=1 align=left width=200>$".$cl_row['TotalAmtDue']."</td>";
                echo 
    "<td colspan=1 align=left width=60>".$cl_row['paymentMode']."</td>";
                echo 
    "<td colspan=1 align=left width=60>".$cl_row['partRegApprove']."</td>";
                
    $friendlysdate date('j F Y H:i:s'strtotime($cl_row['partRegTimeStamp']));
                echo 
    "<td colspan=1 align=left width=60>".$friendlysdate."</td>";
                echo 
    "<td colspan=1 width=30><a href='Partsummary.php?partCode=" .$cl_row['partCode']."'>View</a></td>";
                echo 
    "<td colspan=1 width=30><a href='editparticipants.php?partCode=" .$cl_row['partCode']."'>Edit</a></td>";
                echo 
    "<td colspan=1 width=40><a href='confirmdeleteParticipant.php?partCode=" .$cl_row['partCode']."'>Delete</a></td>";
                echo 
    "</tr>";
            }
            
            print 
    "</table></center></font>";
        
    ?>

    do i have to change the sql statements in the pagination codes??

    this is needed rather urgently. i appreciate ur help.
    [=cHeRyL=]


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •