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@xxxxxxxxxxxxxxxxxxx> To: "Ignatius Reilly" <ignatius.reilly@xxxxxxx> Sent: Tuesday, August 03, 2004 1:46 PM Subject: Re: 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@xxxxxxxxxxxxxxxxxxx> > >To: "php DB" <php-db@xxxxxxxxxxxxx> > >Sent: Tuesday, August 03, 2004 12:52 PM > >Subject: 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@xxxxxxxxxxxxxxxxxxx > . 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
