473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Result set not being Limited

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\">&nbsp;$i&nbsp;</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";
?>
Jul 17 '05 #1
3 1616
I noticed that Message-ID: <40********@funnel.arach.net.au> from Margo
contained the following:
However, the paginating works great,
but my result set is ALL the records on one page....


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/
Jul 17 '05 #2
Geoff Berrow wrote:
I noticed that Message-ID: <40********@funnel.arach.net.au> from Margo
contained the following:

However, the paginating works great,
but my result set is ALL the records on one page....

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


Thank you.

* runs outside and hits head against brick wall*

I'm amazed I didn't see that!

cya

Margo
Jul 17 '05 #3
I noticed that Message-ID: <40********@funnel.arach.net.au> from Margo
contained the following:
You need to run the query again after you add the LIMIT.


Thank you.

* runs outside and hits head against brick wall*

I'm amazed I didn't see that!

<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/
Jul 17 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: dogu | last post by:
Noob alert. Code is below. File is saved as a .php. What I'm trying to do: User uses 'select' box drop down list to pick a value. Value ($site) is derived from a db query. This works fine....
7
by: Tao Wang | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, I saw cuj's conformance roundup, but the result is quite old. I think many people like me want to know newer c++ standard conformance test...
6
by: Rudolf Bargholz | last post by:
Hi , I have the following tables ------------- PAX: Id Order_Id Name Position
8
by: Karen Hill | last post by:
I would like to do something like this using VBA in MS Access 2000: Me.num = "SELECT MAX(num) FROM mytable;" How does one do that in MS Access correctly? Aggregates in SQL are SUM, MAX, MIN...
13
by: David W. Fenton | last post by:
I've been struggling the last two days with something I thought was very easy, which is to open a web page with a form on it and populate the form with data passed in a query string (either POST or...
8
by: wmaple | last post by:
The code as follows: #include <stdio.h> int fun( int x, int y) { if ( x < y ) return y; } int main()
11
by: kinaxx | last post by:
Hello, now I'm learning progamming language in university. but i have some question. in textbook. says there are four passing Mechanism 1) pass by value (inother words : call by value) 2)...
8
siridyal
by: siridyal | last post by:
I have a wholesale website that i'm working on that shows hundreds of items that are updated from time to time. These items are kept in a mysql database with several tables. I want to let the...
4
by: =?Utf-8?B?UmF5IE1pdGNoZWxs?= | last post by:
Hello, I have a multiline RichTextBox that I use for data logging. I'm concerned about how the AppendText method reacts when over time the maximum number of characters have been added. Do the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.