Hi there
I am fairly new to php & MySQL. I have been trying to set up a query and
limit the result 12 records per page - 2 columns by 6 rows. Each record
contains various fields. I have been writing the code as Dreamweaver
didn't like my select statement and didn't like the 2 columns thing!
development page
http://dev.all-fantasy-reviews.com
so. I put in some paginating code (Google rules) and found some code to
display the results the way I want them. Worked out some separate
functions to display some of the fields based on certain conditions. The
display came out pretty good :). However, the paginating works great,
but my result set is ALL the records on one page....
so connection and paging code is here:
<?php
// Connection details
include('Connections/conFantasy.php');
include('includes/functions.php');
// Open connection
mysql_select_db($database_conFantasy, $conFantasy);
// New releases last 6 months
$columns = 2;
$main_sql = "SELECT books.ISBN, books.BookTitle, books.AuthorID,
authors.Lastname, authors.Firstname, books.author2, books.author3,
books.SeriesID, books.VolNo, DATE_FORMAT(books.Published, '%b-%y') AS
DatePub, tblseries.Series FROM authors INNER JOIN books ON
(authors.AuthorID = books.AuthorID) LEFT OUTER JOIN tblseries ON
(books.SeriesID = tblseries.SeriesID) WHERE books.Published BETWEEN
DATE_SUB(CURDATE(), INTERVAL 180 DAY) and CURDATE() ORDER BY
books.Published DESC";
/*======================================*/
// Creating the Page display
/*======================================*/
// getting the total rows
$query = mysql_query($main_sql, $conFantasy);
$total_rows =(mysql_num_rows($query));
// setting the display variables
$rows_per_page = 12;// this value can be changed
$total_pages = ((ceil(($total_rows/$rows_per_page)+1))-1);
// setting page to 1 if not set
if (!$page) $page =1;
// making the nav bar
$page_disp = "<table class=\"nextlast\"><tr><td>";
if ($page!=1) {
$page_disp .= "<a class=\"main\"
href=\"".$PHP_SELF."?page=".($page-1)."\">";
$page_disp .="<img src=\"/images/Previous.gif\" width=\"14\"
height=\"13\" border=\"0\"></a>";
}
$page_disp .= "</td><td width=\"33%\">";
// page list
if ($total_pages>1) {
for ($i=1;$i<($total_pages+1); $i++) {
if ($i==$page) {
$page_disp .= "[".$i."]";
} else {
$page_disp .= "<a
href=\"$PHP_SELF?page=$i\"> $i </a>";
}
}
}
$page_disp .= "</td><td width=\"33%\">";
// Next
if ($page!=$total_pages) {
$page_disp .= "<a class =\"main\"
href='".$PHP_SELF."?page=".($page+1)."'>";
$page_disp .="<img src=\"/images/Next.gif\" width=\"14\"
height=\"13\" border=0></a>";
}
$page_disp .="</td></tr></table>";
/*======================================*/
// Setting the SQL limits
/*======================================*/
$start_limit = (($page*$rows_per_page)-$rows_per_page);
$limit = $rows_per_page;
$main_sql .= " LIMIT $start_limit, $limit";
?>
and the result table:
<?php
echo $page_disp;
echo "<TABLE BORDER=\"0\" width=\"100%\">\n";
//changed this to a for loop so we can use the number of rows
for($j = 0; $j < $total_rows; $j++) {
$row = mysql_fetch_array($query);
if($j % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
echo "<TD width=\"50%\">
<div id=\"BookDisplay\">
<img src=\"http://images.amazon.com/images/P/" . $row['ISBN'] .
".01.THUMBZZZ.jpg\" hspace=\"5\" vspace=\"5\" border=\"0\" align=\"left\" >
<div class=\"bookdesc\">
<a href=\"/books/bookdetail.php?ISBN=" . $row['ISBN'] .
"\"><strong>" . $row['BookTitle'] . "</strong></a> <span
class=\"SmallText\">(" . $row['DatePub'] . ")</span><br><span
class=\"SmallText\"><a href=\"/authors/biography.php?AuthorID=" .
$row['AuthorID'] . "\">" . $row['Firstname'] . " " . $row['Lastname'] .
"</a></span><br>" . DisplayAuthor($row['author2']) .
DisplayAuthor($row['author3']) . DisplaySeries($row['Series'],
$row['VolNo'], $row['SeriesID']) . "<br></div></TD>\n";
if(($j % $columns) == ($columns - 1) || ($j + 1) == $total_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
}
echo "</TABLE>\n";
?>