Cross table pagination

[The_Chancer]

Yet another post about pagination.... <sigh>

But this one is different... honest !!

I can do the pagination setting the limit and offset for each page, etc, HOWEVER, can this be done cross tables ??

The answer of course is yes, but the answer is eluding me at this present moment...

For example...

<?php
$limit=8; // rows to return
if (empty($offset)) {
    $offset=1;
}
$query=mysql_query("SELECT company FROM suppliers");
$company=mysql_fetch_array($company);
foreach ($company as $table)
{
     $query2=mysql_query("SELECT COUNT(product) as product FROM $table");
     $row=mysql_fetch_array($query2); //so $row[product] is the number of rows.
     $numrows=$row[product];
     $query2=mysql_query("SELECT product,price FROM $table LIMIT $offset,$limit");
     while(list($product,$price)=mysql_fetch_row($query2))
     {
          echo "<tr><td>$product</td><td>$price</td></tr>";
     }
}
print("<tr><td align=center>");
if ($offset!=1) { // bypass PREV link if offset is 0
     $prevoffset=$offset-9;
     print "<a href=\"$PHP_SELF?offset=$prevoffset\" title=\"Previous page\"><<</a> &nbsp; \n";
}
// calculate number of pages needing links
$pages=intval($numrows2/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows2%$limit) {
     // has remainder so add one page
     $pages++;
}

for ($i=1;$i<=$pages;$i++) { // loop thru
     $newoffset=(($limit*($i-1))+1);
     print "<a href=\"$PHP_SELF?offset=$newoffset\" title=\"Page $i\">$i</a> &nbsp; \n";
}

// check to see if last page
if (($offset+$limit)<$numrows2) {
     // not last page so give NEXT link
     $newoffset=$offset+$limit;
     print "<a href=\"$PHP_SELF?offset=$newoffset\" title=\"Next page\">>></a><p></td></tr>\n";
}
?>

Credits to whoever made the bottom code originally, amended but quite similar non the less.

This code will only do the offset for the first table in the sequence, and not the rest (it stops dead - but doesn't give an error).

If anyone has any experience with this and can push me along the right track, then please give us a hand..

Cheers,

Update:
The pagination works, but becasue it cycles through each of the tables, it tries to limit it per table not overall... this seems to be the stumbling block... any thoughts ???

[The_Chancer]

Noone willing to have a go / suggestion on this ?

Really starting to peck my head now...

[rklapwijk]

Looks like this is not the full code...

I don't see $numrows2 set anywhere, but you use it in your code.

$pages=intval($numrows2/$limit);

I don't see the problem really, i need to see it in action to fid it out i guess. Anyways, to get the num or record in your db you use this:

[PHP
<?
$query2=mysql_query("SELECT COUNT(product) as product FROM $table");
$row=mysql_fetch_array($query2); //so $row[product] is the number of rows.
$numrows=$row[product];

?>
[/code]

you just could use this aswell:

[PHP
<?
$query2=mysql_query("SELECT product FROM $table");
$numrows=mysql_numrows($query2);
?>
[/code]

[The_Chancer]

Sorry - must have missed the line when I was typing...

should be

<?php
$limit=8; // rows to return
if (empty($offset)) {
    $offset=1;
}
$query=mysql_query("SELECT company FROM suppliers");
$company=mysql_fetch_array($company);
foreach ($company as $table)
{
     $query2=mysql_query("SELECT COUNT(product) as product FROM $table");
     $row=mysql_fetch_array($query2); //so $row[product] is the number of rows.
     $numrows=$row[product];
     $numrows2=$munrows2+$numrows;
     $query2=mysql_query("SELECT product,price FROM $table LIMIT $offset,$limit");
      while(list($product,$price)=mysql_fetch_row($query2))
     {
          echo "<tr><td>$product</td><td>$price</td></tr>";
     }
}
print("<tr><td align=center>");
if ($offset!=1) { // bypass PREV link if offset is 0
     $prevoffset=$offset-9;
     print "<a href=\"$PHP_SELF?offset=$prevoffset\" title=\"Previous page\"><<</a> &nbsp; \n";
}
// calculate number of pages needing links
$pages=intval($numrows2/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows2%$limit) {
     // has remainder so add one page
     $pages++;
}

for ($i=1;$i<=$pages;$i++) { // loop thru
     $newoffset=(($limit*($i-1))+1);
     print "<a href=\"$PHP_SELF?offset=$newoffset\" title=\"Page $i\">$i</a> &nbsp; \n";
}

// check to see if last page
if (($offset+$limit)<$numrows2) {
     // not last page so give NEXT link
     $newoffset=$offset+$limit;
     print "<a href=\"$PHP_SELF?offset=$newoffset\" title=\"Next page\">>></a><p></td></tr>\n";
}
?>

For an explanation this happens....

The initial LIMIT on the query works fine like this
Table 1 has 11 rows, only 8 are given (all OK so far)
BUT
Table 2 has 5 rows - and these are then shown under the initial 8 from Table 1.

What I would like to happen is that 8 rows from Table1 are shown, then on the next page, the next 3 from table 1 and the 5 from table 2.

Does that make it any clearer ? (I am behind a firewall at present so you can't see it in action.... )

[rklapwijk]

Is this what you mean (my english isn't my first language, i can't get the picture right, sorry)

table1 table2

---

result1 - result2
result3 - result4
result5 - result6
etc

is that what you mean?

[rklapwijk]

If thats what you want you might find this usefull.
This also work with several pages that you use, only some var names are different. This code can be written shorter, but now it's reable for everyone.

<table>
    <tr>
      <td>

<?php
// get all rows
    $filescount = mysql_query("SELECT field FROM table");
    $filescount = mysql_numrows($filescount);
// split the results in 2 and round it up for the left side so if the amount isn't equel, the leftside gets 1 more
    $leftside = round($filescount/2);
// now give the right side his num results to display (leftover)
    $rightside = $filescount-$leftside;

// lets put 4 results on each side max
    if ($leftside > "4" && $rightside > "4") {$lfilelimit = "4";$rfilelimit = "4";}
    else {$lfilelimit = $leftside;$rfilelimit = $rightside;}
    if ($page == "") {$filespage = "0";}
    else {$filespage = $page;}

$pages = intval($filescount/$lfilelimit);
$lshowfiles = $filespage * $lfilelimit;

$pages = $leftside/$lfilelimit;

// now display only the record that should be placed on the left
        $lfiledata = mysql_query("SELECT field FROM table ORDER BY id DESC limit $lshowfiles,$lfilelimit");
        while($lfilerow = mysql_fetch_row($lfiledata)) {
?>
<table>
<tr><td><?php echo $lfilerow[0];?></td></tr>
<tr><td><?php echo $lfilerow[1];?></td></tr>
</table>
<br>
<?php
        }
?>
      </td>
      <td>
<?php
// now show the next result on the right where the left side stopped.

    $rfiledata = mysql_query("SELECT field FROM table WHERE ORDER BY id DESC limit $lfilelimit,$rfilelimit");
    while($rfilerow = mysql_fetch_row($rfiledata)) {
?>
<table>
<tr><td><?php echo $lfilerow[0];?></td></tr>
<tr><td><?php echo $lfilerow[1];?></td></tr>
</table>
<?php
       }
?>
      </td>
    </tr>
</table>

[The_Chancer]

Not really what I was after.... something like this

Database layout:

Table 1

row1
row2
...
row 10

Table 2

row1
row2
row3

Eventual on screen layout

Page 1

Table1 row1
table1 row2
table1 row3
.....
table 1 row 8

Page 2

table1 row9
table1 row10
table2 row1
table2 row2
table2 row3

Is that any clearer ?

[rklapwijk]

Now i get it, took some time though.

on the first page just show 1 - 8, when the next page is asked, show the remaining by getting all rows, removing the first 8 (were displayed on the first page) from the query. Then make a new query that displays the rows from table2.

I'm going to catch some sleep now, so i'm not going to code anymore today. Lets see if someone else wants to help you out.

[The_Chancer]

I think this may well be straying fomr the point in this forum - and possible better off in the DATABASE section soon.... as I have had a thought, and my SQL isn't that great...

Is it possible to "add" the tables together using some form of join statement... ?

(Never used Join before...)

Just a thought..

Cheers,

[The_Chancer]

All hail the UNION....

Using

$query="(SELECT * FROM [table]) UNION (SELECT * FROM [table2]) LIMIT $offset, $limit";
$result=mysql_query($query);

Works..

UNION treats the entire previous statement as one record set not as individuals.

If you wanted to add a WHERE clause this has to be done within each parentheses rather than at the end like either the ORDER BY or LIMIT options..

NB This can only be done in MYSQL if you have version 4.0 and above....