Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    137

    Unanswered: paging through records

    I am using this code to read from the database but i wanted to know how can page through records, like just view 10 records per page and then use a link for Next and Previous..




    <?
    error_reporting(E_ALL);

    session_start();

    $pg_connection = pg_connect("dbname=ivr_db_ro user=ivr_fakhro host=www.2connect.com password=xxxxxxx");
    $accounts_sql = "SELECT * FROM accounts where customer_id = '".$_SESSION['customer_id']."'";
    $accounts_result = pg_exec($pg_connection, $accounts_sql);

    if(!$accounts_result || (pg_num_rows($accounts_result) < 1))
    {
    header('Location: index.php');
    }
    ?>

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    <html>
    <head>
    <link rel="STYLESHEET" type="text/css" href="2connect_stylesheet.css">
    <title>my2connect2.com</title>
    </head>

    <body dir="ltr" lang="en-us" topmargin="0" leftmargin="0" bottommargin="0" rightmargin="0">

    <table dir="ltr" lang="en-us" align="left" width="960" cellspacing="0" cellpadding="0" border="0">
    <tr>
    <td align="left"><img src="images/page_header.jpg" alt="" width="950" height="122" border="0"><br><br></td>
    </tr>
    <tr>
    <td align="left">
    <table dir="ltr" lang="en-us" align="center" width="100%" cellspacing="0" cellpadding="0" border="0">
    <tr>
    <td align="left" valign="top" style="padding-top: 10px; padding-bottom: 10px; padding-right: 10px; padding-left: 10px">
    <img src="images/my2connect_title.jpg" alt="" border="0"><br>
    <img src="images/my2connect_banner.jpg" alt="" border="0">
    <br><br>
    <table width="500" cellspacing="0" cellpadding="0" border="0">
    <tr>
    <td align="center" valign="top"><img src="images/personal_profile.jpg" alt="" width="48" height="48" border="0"><br><br><font size="-1">Personal Profile</font></td>
    <td align="center" valign="top"><img src="images/refill_account.jpg" alt="" width="48" height="48" border="0"><br><br><font size="-1">Refill your Account</font></td>
    </tr>
    <tr>
    <td align="center" valign="top"><br></td>
    <td align="center" valign="top"><br></td>
    </tr>
    <tr>
    <td align="center" valign="top"><img src="images/technical_support.jpg" alt="" width="48" height="48" border="0"><br><br><font size="-1">Technical Support</font></td>
    <td align="center" valign="top"><img src="images/change_password.jpg" alt="" width="48" height="48" border="0"><br><br><font size="-1">Change your Password</font></td>
    </tr>
    </table>
    </td>
    <td align="left" valign="top">
    <img src="images/my_account_details_title.jpg" alt="" width="320" border="0">
    <table dir="ltr" lang="en-us" width="320" cellspacing="0" cellpadding="0" border="0">
    <tr style="padding-top: 5px; padding-bottom: 5px; padding-right: 5px; padding-left: 5px">
    <td valign="top" width="100%">
    <table dir="ltr" lang="en-us" width="100%" cellspacing="0" cellpadding="0" border="1" bordercolorlight="#C0C0C0">
    <tr>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">2Call Accounts</font></td>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">Credit (BD)</font></td>
    </tr>
    <?
    // process results
    for ($i = 0; $i < pg_num_rows($accounts_result); $i++)
    {
    $account_id = pg_result($accounts_result, $i, 0);
    $account_number = pg_result($accounts_result, $i, 1);
    $sequence_number = pg_result($accounts_result, $i, 6);
    $account_balance = pg_result($accounts_result, $i, 15);
    ?>
    <tr>
    <td align="center"><font size="-1"><a href="calls.php?id=<?= $account_id ?>"><? print("$account_number"); ?></a></font></td>
    <td align="center"><font size="-1"><? print(number_format("$account_balance", 3)); ?></font></td>
    </tr>
    <?
    }
    ?>
    </table>
    </td>
    </tr>
    <tr style="padding-top: 5px; padding-bottom: 5px; padding-right: 5px; padding-left: 5px">
    <td valign="top" width="100%">
    <table dir="ltr" lang="en-us" width="100%" cellspacing="0" cellpadding="0" border="1" bordercolorlight="#C0C0C0">
    <tr>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">Recent Calls</font></td>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">Date</font></td>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">Amount (BD)</font></td>
    </tr>
    <?
    // if ($HTTP_GET_VARS['account'])
    // {
    // $billing_sql = "SELECT billing.* FROM billing WHERE account_id = ".$_GET['account']." AND amount >= 0 ORDER BY start_date_time desc LIMIT 12";
    // {
    // else
    // {
    // $billing_sql = "SELECT billing.* FROM billing WHERE account_id = 105 AND amount >= 0 ORDER BY start_date_time desc LIMIT 12";
    // }

    // $billing_sql = "SELECT billing.billing_id, billing.detail, billing.start_date_time, billing.amount FROM customers INNER JOIN (accounts INNER JOIN billing ON accounts.account_id = billing.account_id) ON customers.customer_id = accounts.customer_id WHERE customers.customer_id = 23 AND amount >= 0 ORDER BY start_date_time desc LIMIT 12";
    $billing_sql = "SELECT billing.billing_id, billing.detail, billing.start_date_time, billing.amount FROM customers INNER JOIN (accounts INNER JOIN billing ON accounts.account_id = billing.account_id) ON customers.customer_id = accounts.customer_id WHERE customers.customer_id = '".$_SESSION['customer_id']."' AND amount >= 0 ORDER BY start_date_time desc LIMIT 10";
    $billing_result = pg_exec($pg_connection, $billing_sql);

    if(!$billing_result || (pg_num_rows($billing_result) < 1))
    {
    header('Location: index.php');
    }

    for ($i = 0; $i < pg_num_rows($billing_result); $i++)
    {
    $billing_id = pg_result($billing_result, $i, 0);
    $billing_detail = pg_result($billing_result, $i, 1);
    $billing_date_time = pg_result($billing_result, $i, 2);
    $billing_amount = pg_result($billing_result, $i, 3);
    ?>
    <tr>
    <td align="center"><font size="-1"><a href="calls.php?id=<?= $billing_id ?>"><? print("$billing_detail"); ?></a></font></td>
    <td align="center"><font size="-1"><? print(substr($billing_date_time, 8, 2)."/".substr($billing_date_time, 5, 2)."/".substr($billing_date_time, 0, 4)." ".substr($billing_date_time, 11, 5)); ?></font></td>
    <td align="center"><font size="-1"><? print(number_format("$billing_amount", 3)); ?></font></td>
    </tr>
    <?
    }
    ?>
    <tr>
    <td align="right" colspan="3" style="border-bottom: none; border-left: none; border-right: none; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px;"><img src="images/more_icon.jpg" alt="" border="0"></td>
    </tr>
    </table>
    </td>
    </tr>
    <tr style="padding-top: 5px; padding-bottom: 5px; padding-right: 5px; padding-left: 5px">
    <td valign="top" width="100%">
    <table dir="ltr" lang="en-us" width="100%" cellspacing="0" cellpadding="0" border="1" bordercolorlight="#C0C0C0">
    <tr>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">Recent Payments</font></td>
    <td align="center" bgcolor="#C0C0C0"><font size="-1">Amount (BD)</font></td>
    </tr>
    <?
    $billing_sql = "SELECT billing.billing_id, billing.detail, billing.start_date_time, billing.amount FROM customers INNER JOIN (accounts INNER JOIN billing ON accounts.account_id = billing.account_id) ON customers.customer_id = accounts.customer_id WHERE customers.customer_id = '".$_SESSION['customer_id']."' AND amount < 0 ORDER BY start_date_time desc LIMIT 4";
    $billing_result = pg_exec($pg_connection, $billing_sql);

    if(!$billing_result || (pg_num_rows($billing_result) < 1))
    {
    header('Location: index.php');
    }

    for ($i = 0; $i < pg_num_rows($billing_result); $i++)
    {
    $billing_id = pg_result($billing_result, $i, 0);
    $billing_detail = pg_result($billing_result, $i, 1);
    $billing_date_time = pg_result($billing_result, $i, 2);
    $billing_amount = pg_result($billing_result, $i, 3) / 1000;
    ?>
    <tr>
    <td align="center"><font size="-1"><? print(substr($billing_date_time, 8, 2)."/".substr($billing_date_time, 5, 2)."/".substr($billing_date_time, 0, 4)); ?></font></td>
    <td align="center"><font size="-1"><a href="payment.php?id=<?= $billing_id ?>"><? print(number_format("$billing_amount", 3)); ?></a></font></td>

    </tr>
    <?
    }
    ?>
    <tr>
    <td align="right" colspan="2" style="border-bottom: none; border-left: none; border-right: none; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px;"><img src="images/more_icon.jpg" alt="" border="0"></td>
    </tr>
    </table>
    </td>
    </tr>
    </table>
    </td>
    </tr>
    </table>
    </td>
    </tr>
    <tr>
    <td align="center"><br><img src="images/footer.jpg" alt="" border="0"></td>
    </tr>
    </table>

    </body>
    </html>

  2. #2
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    The way that you are currently going through you result set is:

    for ($i = 0; $i < pg_num_rows($accounts_result); $i++)
    {
    $account_id = pg_result($accounts_result, $i, 0);
    $account_number = pg_result($accounts_result, $i, 1);
    $sequence_number = pg_result($accounts_result, $i, 6);
    $account_balance = pg_result($accounts_result, $i, 15);
    }

    for ($i = 0; $i < pg_num_rows($billing_result); $i++)
    {
    $billing_id = pg_result($billing_result, $i, 0);
    $billing_detail = pg_result($billing_result, $i, 1);
    $billing_date_time = pg_result($billing_result, $i, 2);
    $billing_amount = pg_result($billing_result, $i, 3);
    }

    for ($i = 0; $i < pg_num_rows($billing_result); $i++)
    {
    $billing_id = pg_result($billing_result, $i, 0);
    $billing_detail = pg_result($billing_result, $i, 1);
    $billing_date_time = pg_result($billing_result, $i, 2);
    $billing_amount = pg_result($billing_result, $i, 3) / 1000;
    }

    So all you have to do is instead of “$i < pg_num_rows($billing_result)” do something like $i =< 10. This will limit your results to the first 10. Then after you click on the next button on the bottom of the page to see the next 10 results just set $i = 10; $i =< 20 …” Of course you should first do an if statement to make sure you there are still results and you aren’t trying to display nothing. These suggestions might not be the best way to do it, but for what you have already coded it should work. A better idea might be stepping through the results and sending the object results to the next page instead of re-running the query again

    Good luck
    Reghardt

  3. #3
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    I would like to remind everyone posting code in the PHP forum to use the PHP wrappers (the small php page shaped icon located near the left top edge of the web form used to write posts.)

    This does two things - it causes the PHP code to appear in a scrollable window so that the post is not the length of the code, and it highlights the code so that it is easier to read and this also helps finding errors, such as missing or mismatched quotes...

Posting Permissions

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