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

Wow. I had no idea how little I knew about sql (well, I kind of did really). I've always found it easier to stick to the basics and rely on program logic to do anything involved. I'll take this on board and give it a go. FYI table design is way out of my control - I'm just building a report on someone elses data (you are right, it is ugly). It's nearly mid-night here now so I might get on to it tomorrow. Thank you heaps for you help ... Ross At 11:10 PM 3/08/2004, you wrote:

With MySQL 4.1: try this (untested) SELECT SM.key, SM.stock_on_hand COALESCE( SH.tally, 0 ) AS hist_sales FROM stock_master AS SM LEFT JOIN ( SELECT

     SH.key
     SUM( SH.jan )
     + ...
     + SUM( SH.dec )
     AS tally

FROM stock_history AS SH WHERE SH.year = 2004 GROUP BY key ) AS H ON H.key = SM.key HAVING stock_on_hand > hist_sale With earlier versions, you must create a temporary table with the table appearing in the subquery. Notes: 2 ugly things about your design: - dashes in table and column names - one column per month: you must have one column "month" that becomes part of the primary key HTH Ignatius _________________________ ----- Original Message ----- From: "Ross Honniball" <ross@bookshop.jcu.edu.au> To: "php DB" <php-db@lists.php.net> Sent: Tuesday, August 03, 2004 2:54 PM Subject: Re: [PHP-DB] 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. . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

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