Result set not being Limited

[Margo]

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('Connec tions/conFantasy.php' );
include('includ es/functions.php') ;
// Open connection
mysql_select_db ($database_conF antasy, $conFantasy);

// New releases last 6 months
$columns = 2;

$main_sql = "SELECT books.ISBN, books.BookTitle , books.AuthorID,
authors.Lastnam e, authors.Firstna me, books.author2, books.author3,
books.SeriesID, books.VolNo, DATE_FORMAT(boo ks.Published, '%b-%y') AS
DatePub, tblseries.Serie s FROM authors INNER JOIN books ON
(authors.Author ID = books.AuthorID) LEFT OUTER JOIN tblseries ON
(books.SeriesID = tblseries.Serie sID) WHERE books.Published BETWEEN
DATE_SUB(CURDAT E(), INTERVAL 180 DAY) and CURDATE() ORDER BY
books.Published DESC";

/*============== =============== =========*/
// Creating the Page display
/*============== =============== =========*/
// getting the total rows
$query = mysql_query($ma in_sql, $conFantasy);
$total_rows =(mysql_num_row s($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=\"nextlas t\"><tr><td>" ;
if ($page!=1) {
$page_disp .= "<a class=\"main\"
href=\"".$PHP_S ELF."?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<($tota l_pages+1); $i++) {
if ($i==$page) {
$page_disp .= "[".$i."]";
} else {
$page_disp .= "<a
href=\"$PHP_SEL F?page=$i\">&nb sp;$i&nbsp;</a>";
}
}
}

$page_disp .= "</td><td width=\"33%\">" ;


// Next
if ($page!=$total_ pages) {
$page_disp .= "<a class =\"main\"
href='".$PHP_SE LF."?page=".($p age+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_p er_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_arr ay($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=\"BookDispla y\">
<img src=\"http://images.amazon.c om/images/P/" . $row['ISBN'] .
".01.THUMBZZZ.j pg\" hspace=\"5\" vspace=\"5\" border=\"0\" align=\"left\" >
<div class=\"bookdes c\">
<a href=\"/books/bookdetail.php? ISBN=" . $row['ISBN'] .
"\"><strong >" . $row['BookTitle'] . "</strong></a> <span
class=\"SmallTe xt\">(" . $row['DatePub'] . ")</span><br><span
class=\"SmallTe xt\"><a href=\"/authors/biography.php?A uthorID=" .
$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";
?>

[Geoff Berrow]

Re: Result set not being Limited

I noticed that Message-ID: <409d8fdd$1@fun nel.arach.net.a u> from Margo
contained the following:
[color=blue]
>However, the paginating works great,
>but my result set is ALL the records on one page....[/color]

You need to run the query again after you add the LIMIT.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Margo]

Re: Result set not being Limited

Geoff Berrow wrote:
[color=blue]
> I noticed that Message-ID: <409d8fdd$1@fun nel.arach.net.a u> from Margo
> contained the following:
>
>[color=green]
>>However, the paginating works great,
>>but my result set is ALL the records on one page....[/color]
>
>
> You need to run the query again after you add the LIMIT.
>[/color]

Thank you.

* runs outside and hits head against brick wall*

I'm amazed I didn't see that!

cya

Margo

[Geoff Berrow]

Re: Result set not being Limited

I noticed that Message-ID: <409ded0c$1@fun nel.arach.net.a u> from Margo
contained the following:
[color=blue][color=green]
>> You need to run the query again after you add the LIMIT.
>>[/color]
>
>Thank you.
>
>* runs outside and hits head against brick wall*
>
>I'm amazed I didn't see that![/color]


<g> Fresh eyes.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/