Re: Web page paginator that doesn't rely on the LIMIT clause

At 10:22 PM 3/08/2004, you wrote:

1. please answer to the list, not to only me (you break the thread)

whoops. sorry.

2. show us your data model if you need further help

AS AN EXERCISE, I would be interested to know if you can do this in one query, but I've pretty much decided to either code up or find a paginator class that will resolve my present issues. I figure even if this particular query can be done in SQL, I'm going to eventually run against a problem that can't. Roughly, data model looks like this: Table : Stock-Master Key (key) title etc. stock-on-hand | | /\ / \ Table : Stock-History Key (key) Year(key) Sales-Jan Sales-Feb Sales-Mar ... Sales-Dec And what I need to do is loop through master records, grab history records for various years (which may or may not exist for any given key), add up multiple sales field within each history record, and compare this against stock-on-hand to decide if I want to 'output' the record or not. Let me know if you want more detail. It would resolve the present issue if it can be done in a query(and improve my sql knowledge).

_________________________ ----- Original Message ----- From: "Ross Honniball" <ross@bookshop.jcu.edu.au> To: "Ignatius Reilly" <ignatius.reilly@free.fr> Sent: Tuesday, August 03, 2004 1:46 PM Subject: Re: [PHP-DB] Web page paginator that doesn't rely on the LIMIT clause thanks ignatius. not sure i'm out of the woods though. I didn't know sql had CASE and IF. I just had a quick look in the manual and aren't sure they will help. The specific logic I'm doing is this: total_sales = 0; select * from master while more master records if (select * from history record 1) // MAY NOT EVEN EXIST

      loop through a dozen monthly sales fields adding into total_sales;

endif if (select * from history record 2) // MAY NOT EVEN EXIST

      loop through a dozen monthly sales fields adding into total_sales;

endif //********* the 'output' test if total_sales > some_amount

      output record

endif end-while So it's not real complex to do in code, but I really wouldn't know where to start to try and screw it all in to a single sql statement. I also had a look at HTML_Pager but, at a glance, it looks like some kind of web-page presenter? At 09:14 PM 3/08/2004, you wrote:

1. What prevents you from implementing the conditions directly in SQL?

You

can achieve a lot with CASE and IF. 2. For your paging needs, you may benefit from investigating the PEAR HTML_Pager class. Ignatius _________________________ ----- Original Message ----- From: "Ross Honniball" <ross@bookshop.jcu.edu.au> To: "php DB" <php-db@lists.php.net> Sent: Tuesday, August 03, 2004 12:52 PM Subject: [PHP-DB] Web page paginator that doesn't rely on the LIMIT

clause

Hi all, I use the LIMIT statement to control paging of data, which works

really

well.

I now have a situation where I need to use some logic in addition to

the

query that will result in NOT ALL records being spat out. ie. select * from x where y; foreach result

     if (some condition)
         output;
     endif

endfor So problem is I can't say LIMIT 20,20 on the query as logic may result

in

less than 20 records being spat out to screen. I was planning on coding up a solution so that i just keep a count

myself

of how many records I have output myself and break at appropriate

paging

points, but I am probably re-inventing the wheel. Anyone know of any good classes they are using that do this sort of

thing

already? . . Ross Honniball. JCU Bookshop Cairns, Qld, Australia. . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

. Ross Honniball                  JCU Bookshop Cairns Supervisor

. James Cook Uni, McGregor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:ross@bookshop.jcu.edu.au . There are no problems. Only solutions.

. . Ross Honniball. JCU Bookshop Cairns, Qld, Australia. .