2 Query of LIMIT [int, int] on one page, with 1st int auto c

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
y2yang
Forum Newbie
Posts: 1
Joined: Sun May 03, 2009 4:48 am

2 Query of LIMIT [int, int] on one page, with 1st int auto c

Post by y2yang »

I am running two query with LIMIT on the same page, which I pulled from my MySQL with field id.

Code: Select all

$query = "SELECT * FROM thumb LIMIT 0, 4";
and

Code: Select all

$query = "SELECT * FROM thumb LIMIT 4, 4";
Would it be possible to have this programmed to change automatically with page id.

Example, on ?id=1, it would have the above query but on ?id=2, it would run a query like the one below.

Code: Select all

$query = "SELECT * FROM thumb LIMIT 8, 4";
and

Code: Select all

$query = "SELECT * FROM thumb LIMIT 12, 4";

To put it simple:

First query on ?id=1 would start pulling data from row 1 to 4 and the second query on ?id=1 to start pulling data from row 5 to row 8.

While query 1 on ?id=2 start pulling data from row 9 to row 12 and query 2 on ?id=2 from row 13 to row 16.

Here is example of code:

Code: Select all

<?php
// database connection info
 
$query = "SELECT * FROM thumb LIMIT 0, 4";
$result =  mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
?>
   <div style="width:132px;height:132px;margin:0 5px 0 5px;float:left;">
        <table width="132" style="background:url('http://image.vickizhao.net/starhome/photo/list/img_pic_bg.gif') no-repeat;">
        <tr style="height:132px;">
            <td><a href="http://<?php echo $_SERVER['SERVER_NAME']; ?>/starhome/photo/read/read.php?Pid=<?php print $row['id']; ?>"><img id="reSizeImg1" src="<?php print $row['imgpath']; ?>" style="border:solid 1px #ffffff;" alt=""  /></a></td>
        </tr>
        </table>
    </div>
<?php
}
?>
 
            <div style="clear:left;overflow:hidden;width:0;height:0;"></div>
             <p style="margin:20px;"></p>
            <div class="dotHLine1"></div>
            <p style="margin:20px;"></p>
               
<?php
$query = "SELECT * FROM thumb LIMIT 4, 4";
$result =  mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
?>
   <div style="width:132px;height:132px;margin:0 5px 0 5px;float:left;">
        <table width="132" style="background:url('http://image.vickizhao.net/starhome/photo/list/img_pic_bg.gif') no-repeat;">
        <tr style="height:132px;">
            <td><a href="http://<?php echo $_SERVER['SERVER_NAME']; ?>/starhome/photo/read/read.php?Pid=<?php print $row['id']; ?>"><img id="reSizeImg1" src="<?php print $row['imgpath']; ?>" style="border:solid 1px #ffffff;" alt=""  /></a></td>
        </tr>
        </table>
    </div>
<?php
}
?>
The main reason why I wanted to perform 2 LIMIT on one page is that I wanted 8 images to show on one page with a dotted line in between my two row of image.

The code to go between the 2 row of images is:

Code: Select all

           <div style="clear:left;overflow:hidden;width:0;height:0;"></div>
             <p style="margin:20px;"></p>
            <div class="dotHLine1"></div>
            <p style="margin:20px;"></p>
The reason I just didn't do LIMIT 0, 8 is because I wanted a dotted line in between the two rows:
1 2 3 4
-------
5 6 7 8

But I don't know how to get it there, yet.
Last edited by Benjamin on Sun May 03, 2009 6:08 am, edited 2 times in total.
Reason: Added code tags
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: 2 Query of LIMIT [int, int] on one page, with 1st int au

Post by McInfo »

What you are asking about is called "pagination". I'm sure you can find information about it online. Wait a second, this is online. Here is an example for you and anyone else who is interested in pagination. I have added lots of comments which I hope will make it easy to follow the code.

Code: Select all

<?php
// [Configurable] Sets the default page number (first page is 0)
$page = 0;
 
// [Configurable] Sets the default number of columns to display (width)
$cols = 5;
 
// [Configurable] Sets the default number of rows to display (height)
$rows = 2;
 
/* Confirms that a page number has been requested and
 * verifies that the content of the request is legitimate (all digits).
 * The requested page number is $page + 1 because the first $page is 0
 */
if (isset($_GET['page']) && !preg_match('/[^0-9]/', $_GET['page'])) {
    $page = (int) ($_GET['page'] - 1);
}
 
/* Confirms that the number of columns has been requested and
 * verifies that the content of the request is legitimate (all digits).
 * This feature can be disabled by commenting out the next block.
 */
if (isset($_GET['cols']) && !preg_match('/[^0-9]/', $_GET['cols'])) {
    $cols = (int) $_GET['cols'];
}
 
/* Confirms that the number of rows has been requested and
 * verifies that the content of the request is legitimate (all digits).
 * This feature can be disabled by commenting out the next block.
 */
if (isset($_GET['rows']) && !preg_match('/[^0-9]/', $_GET['rows'])) {
    $rows = (int) $_GET['rows'];
}
 
/* Fills the $all_records array with artificial records
 * to simulate data that might be stored in a database.
 *
 * $all_records = array
 * (  0 => array
 *    (  'f1' => 'rec0-fld1'
 *    ,  'f2' => 'rec0-fld2'
 *    ,  'f3' => 'rec0-fld3'
 *    )
 * ,  ...
 * );
 */
$all_records = array();
for ($r = 0; $r < 33; $r ++) {
    // Fills the same number of fields for each record
    for ($f = 0; $f < 3; $f++) {
        $all_records[$r]['f'.$f] = 'rec'.$r.'-fld'.$f;
    }
}
 
// Simulates counting the number of records in the database
// Equivalent query: SELECT COUNT(*) FROM `table`
$count_all_records = count($all_records);
 
// Calculates the number of records per page to retrieve from the database
// Also the number of cells in the HTML table
$count_cells = $rows * $cols;
 
// Calculates the number of pages in the data set
// Also the highest page number for links
$count_pages = ceil($count_all_records / $count_cells);
 
// If the page number is negative, the last page is used instead
if ($page < 0) {
    $page = $count_pages - 1;
}
// If the page number is greater than the upper limit, the first page is used
elseif ($page >= $count_pages) {
    $page = 0;
}
 
// Calculates the record number to start at
$start = $page * $count_cells;
 
// Simulates retrieving records from the database
// Equivalent query: SELECT * FROM `table` LIMIT $start, $count_cells
$records = array_slice($all_records, $start, $count_cells);
 
// Stores navigation links for available page numbers so the
// link bar can be used at both the top and bottom of the table
// without looping twice
$cache_nav_links = '<p>';
for ($p = 1; $p <= $count_pages; $p++) {
    $cache_nav_links .=
        '<a href="?page='.$p.'&cols='.$cols.'&rows='.$rows.'">'
        .$p.'</a>&nbsp;';
}
$cache_nav_links .= '</p>';
 
// Calculates the number of visible records
$count_visible_records = $count_cells;
// If this is the last page...
if ($page == ($count_pages - 1)) {
    // ...the number of visible records is the remainder of dividing
    // $count_all_records by the number of records per page,
    // but only if the remainder is not zero
    if (0 < ($cvr = ($count_all_records % $count_cells))) {
        $count_visible_records = $cvr;
    }
}
?>
 
<h1>Page <?php echo ($page + 1).' ('.$page.')'; ?></h1>
 
<p>
    Displaying <?php echo $count_visible_records; ?>
    of <?php echo $count_all_records; ?> records,
    starting at record <?php echo ($start + 1).' ('.$start.')'; ?>
</p>
 
<?php echo $cache_nav_links; ?>
 
<table border="1" cellspacing="1" cellpadding="3">
<?php
 
// Initializes the current index for the $records array
$i = 0;
 
// Used for breaking the row loop if no more records exist
$break_row = false;
 
// For each table row...
for ($r = 0; $r < $rows; $r++) {
   
    // Doesn't start a new row if there is no record to fill it
    if ($break_row || empty($records[$i])) {
        break;
    }
   
    // Starts a new row
    echo '<tr>';
   
    // For each table column...
    for ($c = 0; $c < $cols; $c++) {
       
        // Checks for a record to retrieve data from
        if (!empty($records[$i])) {
            // The record holds data
           
            // Starts a new cell (column in row)
            echo '<td>';
       
            // Displays data for fields f0, f1, and f2
            echo '<div>'.$records[$i]['f0'].'</div>';
            echo '<div>'.$records[$i]['f1'].'</div>';
            echo '<div>'.$records[$i]['f2'].'</div>';
           
            // Ends the current cell (column in row)
            echo '</td>';
           
        } else {
            // The inspected record is empty
           
            // Doesn't start a new row after the current row
            $break_row = true;
           
            if ($r == 0) {
                // This is the first row
               
                // Stops displaying columns
                break;
               
            } else {
                // This is not the first row
               
                // Displays a cell with a non-breaking space
                // so the table cell does not collapse
                echo '<td>&nbsp;</td>';
            }
        }
       
        // Increments the current $records array index
        $i++;
    }
   
    // Ends the current row
    echo '</tr>';
}
?>
</table>
 
<?php echo $cache_nav_links; ?>
 
<ul>
    <li><a href="?page=<?php echo $page; ?>&cols=4&rows=3">Try 4 Columns, 3 Rows</a></li>
    <li><a href="?page=<?php echo $page; ?>&cols=3&rows=7">Try 3 Columns, 7 Rows</a></li>
    <li><a href="?">Default</a></li>
</ul>
Related post: (broken) 572047 (page navigation demo with a limited range of visible page links)

Edit: This post was recovered from search engine cache.
Post Reply