Pagination - multiple rows and columns?

[jimberland]

hi there,
i have found lots of useful stuff on this site in the past so am hoping someone can help...
i would like to paginate a set of results.
i would like 12 results per page, ideally 4 columns over 3 rows.
i can do the pagination and rows part no problem but am struggling with writing the columns.
could any point me to a good tutorial or provide some sample code?
thank you in advance.

[Habtom]

Welcome to WebmasterWorld,

i would like 12 results per page, ideally 4 columns over 3 rows. i can do the pagination and rows part no problem but am struggling with writing the columns.

This is not a working code, but to write in a number of columns you need a loop and conditions inside to decide to which cell the data should be written to. It can go something like the following:

for loop
if condition
<tr>
<td>$value</td>
elseif condition
<td>$value</td>
</tr>
end loop

Habtom

[henry0]

What are you looking for:
The PHP
or HTML
it looks like your problem lies in formating the results
let us know
and welcome!

[jimberland]

the php
html isn't really a problem.
i guess i'm struggling with the loop part.
i.e how it knows to write 4 tds over 3 rows, but if for example there are only 11 results it writes a blank td
if that makes sense?
thanks for the response

[jimberland]

this is the code i have that doesn't take in to account number of html rows:

if($result > 0) {
$x=0;
print("<table cellspacing=0 cellspacing=0 width=500 border=0><tr>");

foreach ($news_array as $item){
$id = $item[id];
$cAdvertiser = $item[cAdvertiser];
$cCname = $item[cCname];
$cLocId=$item['cLocId'];
$cImage = $item[cImage];

{
$x=$x+1;

if ($x % 4 == 0) {
print("<td width=100 align=left valign=top><img src=../images/upload/$cImage border=0 width=100 height=100 /><br>
$cAdvertiser<br>
Campaign: $cCname<br>
<a href=campaign.php?id=$cLocId&cId=$id>See more</a>
<br><br></td></tr><tr>");
}else{
print("<td width=100 align=left valign=top><img src=../images/upload/$cImage border=0 width=100 height=100 /><br>
$cAdvertiser<br>
Campaign: $cCname<br>
<a href=campaign.php?id=$cLocId&cId=$id>See more</a>
<br><br></td>");
}

}
}
print("</table>");
}

i think it could be a bit prettier though...

[henry0]

You are not showing a pagination system
but since you mentionned num of rows
here is one that takes care of pagination based on your choice of row number
pay attention to ceil() [us2.php.net]

if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}

// count rows.

$query = "SELECT count(*) FROM table WHERE ...";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

//number of $lastpage

// ceil() is important for dividing and taking care of "leftovers"

$rows_per_page = 15;
$lastpage = ceil($numrows/$rows_per_page);

// check that the value of $pageno is an integer between 1 and $lastpage.

$pageno = (int)$pageno;
if ($pageno < 1) {
$pageno = 1;
} elseif ($pageno > $lastpage) {
$pageno = $lastpage;
}

// LIMIT sql statement.

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = "SELECT * FROM table $limit";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);

//hyperlinks

if ($pageno == 1) {
echo " FIRST PREV ";
} else {
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
$prevpage = $pageno-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
}

echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
echo " NEXT LAST ";
} else {
$nextpage = $pageno+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
}

[jimberland]

thanks henry,
i didn't show a pagination system because i have that working fine.
the part i am struglling with is writing the result set across the columns.

i can write the rows and paginate, no problem

i can write the columns with my code listed in my previous post, no problem.

i just can't work out how to combine the two.

for example, from the code you listed, how would you make the 15 rows display across a html table of 5 columns and 3 rows, before paginating to the next page?

[henry0]

Well a row is an horizontal display of data
if you run a query for your 5 <td>
and limit it to 3 (rows)
you will have it
then the next page will be the following 3 rows etc...

$query = "SELECT id, this, that, more, and more FROM table $limit";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);

[jimberland]

if i limit it to 3 rows then i will have 2 empty <td>'s?
each <td> contains 1 record from the database
each page can contain up to 15 database records (5 cols, 3 rows)

E.g (where the number is a databse record)
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

Next page >

[henry0]

Sorry, maybe someone else could understand your problem.
I am lost :)
if empty then it is empty.. that's where I do not follow you.

You might possibly need a table col "default" instead of "empty"?
but again I do not get it.

<<<
E.g (where the number is a databse record)
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
>>>
How do want to fill the missing ones?

[jimberland]

no problem.
thanks for trying to help.

i can't see why it is so difficult.

the sql is so simple:
SELECT * FROM table LIMIT 15;

if there 15 records it will populate all cells in the table
if there are not 15 records it will put a nbsp; in the empty table cells

[omoutop]

you just answered this yourself :)

<td>
if (var holds value) { echo value}
else { echo " ";}
</td>

[jimberland]

ok here is my code:

/////// CODE START /////////
<?
include_once("../functions/general.php");

$adcat=urldecode($_REQUEST["dhtmlgoodies_country"]);
$advertiser=$_REQUEST["dhtmlgoodies_city"];
$campaign=$_REQUEST["dhtmlgoodies_city2"];
$product=$_REQUEST["dhtmlgoodies_city3"];
$month=$_REQUEST["month"];
$year=$_REQUEST["year"];

$page=$_REQUEST["page"];
$limit=$_REQUEST["limit"];

if ($adcat == "All" && $advertiser == "" && $campaign == "" && $product == ""){
$news_array = search();
$result=count($news_array);
$complete="Your search produced $result result(s).";

} elseif ($adcat && $advertiser == "" && $campaign == "" && $product == ""){
$news_array = search1($adcat);
$result=count($news_array);
$complete="Your search produced $result result(s).";

} elseif ($adcat && $advertiser && $campaign == "" && $product == "") {
$news_array = search2($adcat,$advertiser);
$result=count($news_array);
$complete="Your search produced $result result(s).";

} elseif ($adcat && $advertiser && $campaign && $product == "") {
$news_array = search3($adcat,$advertiser,$campaign);
$result=count($news_array);
$complete="Your search produced $result result(s).";

} elseif ($adcat && $advertiser && $campaign && $product) {
$news_array = search4($adcat,$advertiser,$campaign,$product);
$result=count($news_array);
$complete="Your search produced $result result(s).";
}

$display_columns = 4;
$padding = ($display_columns-1)-(($result-1)%$display_columns);

if (!($limit)){
$limit = 12;} // Default results per-page.
if (!($page)){
$page = 0;} // Default page value.
$numrows = $result; // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query - $query"); // bah, modify the "Not Found" error for your needs.
exit();}

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows%$limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) ¦¦ ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages!= 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

//escape from PHP mode.
?>

<div id="col2">
<? print "$complete";?>
<br><br>
<?
if($result > 0) {
$x=0;
print("<table cellspacing=0 cellspacing=0 width=500 border=0><tr>");
/// This is where i need to get it to limit ////

foreach ($news_array as $item){
$id = $item['id'];
$cAdvertiser = $item['cAdvertiser'];
$cCname = $item['cCname'];
$cLocId=$item['cLocId'];
$cImage = $item['cImage'];

{
$x=$x+1;

if ($x % 4 == 0) {
print("<td width=100 align=left valign=top><img src=../images/upload/$cImage border=0 width=100 height=100 /><br>
$cAdvertiser<br>
Campaign: $cCname<br>
<a href=campaign.php?id=$cLocId&cId=$id>See more</a>
<br><br></td></tr><tr>");
}else{
print("<td width=100 align=left valign=top><img src=../images/upload/$cImage border=0 width=100 height=100 /><br>
$cAdvertiser<br>
Campaign: $cCname<br>
<a href=campaign.php?id=$cLocId&cId=$id>See more</a>
<br><br></td>");
}

}
}
print("</table>");
}
?>

Page <b><?=$current?></b> of <b><?=$total?></b> ¦
<?
if ($page!= 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?adcat=$adcat&advertiser=$advertiser&campaign=$campaign&product=$product&page=$back_page&limit=$limit\">< Back</a> \n");}

if (!((($page+$limit) / $limit) >= $pages) && $pages!= 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo(" <a href=\"$PHP_SELF?adcat=$adcat&advertiser=$advertiser&campaign=$campaign&product=$product&page=$next_page&limit=$limit\">Next ></a>\n");}
?>
</div>

//////////// SEARCH FUNCTIONS ////////////////

function search(){

$query = "SELECT * FROM campaigns ORDER BY cAdvertiser ASC";

$data = get_data($query, array("id", "cAdvertiser", "cCname", "cLocations", "cLocId", "cFormat", "cAdCat", "cDate", "cImage"));

return $data;

}

function search1($adcat){

$query = "SELECT * FROM campaigns WHERE cAdCat = '$adcat' ORDER BY cAdvertiser ASC";

$data = get_data($query, array("id", "cAdvertiser", "cCname", "cLocations", "cLocId", "cFormat", "cAdCat", "cDate", "cImage"));

return $data;

}

function search2($adcat,$advertiser){

$query = "SELECT * FROM campaigns WHERE cAdCat = '$adcat' AND cAdvertiser = '$advertiser' ORDER BY cAdvertiser ASC";

$data = get_data($query, array("id", "cAdvertiser", "cCname", "cLocations", "cLocId", "cFormat", "cAdCat", "cDate", "cImage"));

return $data;

}

function search3($adcat,$advertiser,$campaign){

$query = "SELECT * FROM campaigns WHERE cAdCat = '$adcat' AND cAdvertiser = '$advertiser' AND cCname = '$campaign' ORDER BY cAdvertiser ASC";

$data = get_data($query, array("id", "cAdvertiser", "cCname", "cLocations", "cLocId", "cFormat", "cAdCat", "cDate", "cImage"));

return $data;

}

function search4($adcat,$advertiser,$campaign,$product){

$query = "SELECT * FROM campaigns WHERE cAdCat = '$adcat' AND cAdvertiser = '$advertiser' AND cCname = '$campaign' AND cFormat = '$product' ORDER BY cAdvertiser ASC";

$data = get_data($query, array("id", "cAdvertiser", "cCname", "cLocations", "cLocId", "cFormat", "cAdCat", "cDate", "cImage"));

return $data;

}

//////// CODE END ////////////

i have marked in where i think i need to limit the result set
any ideas?
thanks

[coopster]

Our PHP Forum Library [webmasterworld.com] has a lot of useful tips and tricks, including one to create rows and columns as you mention here.

Good PHP solutions to small problems [webmasterworld.com]
The 4th tip down describes how to Create a Dynamic Table from mysql result