Hi Rick, > many people have a very shallow understanding of SQL - particularly > [he says generalising like crazy] people who 'fall into it' from > (say) PHP programming... This shallow understanding means that > 'they' will tend to do too much in PHP (assuming they know it better) > in preference to SQL - at a cost of efficiency/execution time.
WOW! I was beginning to think I was the only one around here who thought that way. Here is a sample query that does most of the work on generating invoices for a job search site. Lines in the code that don't have "" around them are comments I just added... =Thank you for the kind words. It has been an interesting day, and Brian's problem has certainly caused me to revisit the above claim. I'm hoping I've got a solution for him (dependent upon assumptions about 'business rules'), but he will be the judge of that. Thereafter, knowing the db/tbl is so badly set up, it would be quite interesting to run a comparison between 'problem fixed using PHP' and 'problem fixed using MySQL' to see what the relative efficiencies/speed really is. Of course we would then have to 'correct' the db structure and re-try the tests, too. If you're interested perhaps we could cook up some ideas between us? =I have read through the impressive query below. It is an excellent example of code that (I suspect) many would have implemented partly/substantially in PHP. Well done! =I think that it causes me to suggest that my earlier comment (per top of this msg) was only partly thought through, in that having clearly thought out/documented 'business rules' coming out of your design phase has enabled you to collect together the query. In other words, it is likely to be all-but impossible to conceive of such a query unless one uses a top-down design approach. What would be your comment? =I particularly liked the way that the 'business rules' have been embodied into the query. You say "comments I just added", do you mean that these are not actually present in the PHP code? Many people don't realise that you can embed comments in MySQL queries, both the /*...*/ and the #-from-here-to-the-end-of-the-line conventions work. Accordingly all of those comments could be permanently built-in as in-line documentation. =Well done! =dn PS there's a small offering at the end of this msg $R = query( "SELECT Managers.ManagerID, NameFirst, NameLast, " . " Managers.Email, Managers.Phone, Managers.Fax, " . " PayDesc, PayApproved, " . " Employers.EmployerID, Name, Motif, " . " Positions.PositionID, PONumber, Memo, " . "IF( Title = '', 'No Title', Title ) AS Title, " . if the title field is blank, replace it with 'No Title' "IF( ''=Managers.Mail, Managers.Phys, Managers.Mail ) " . " AS Address, " . if the manager has a mailing address use it, else use the physical address. "DATE_FORMAT( DateActive, '%b-%e-%y' ) AS DateActive, " . "IF( DateClosed, " . " DATE_FORMAT( DateClosed, '%b-%e-%y' ), " . " '--Cont--' ) AS DateClosed, " . If DateClosed is blank, say the position is continuing in the closed field. "TO_DAYS( DateActive ) AS Active, " . "TO_DAYS( DateClosed ) AS Closed " . "COUNT(*) AS Count " . Count how many job seekers have responded to the ad so we can brag about it on the bill. "FROM PaymentMethods " . "LEFT JOIN Managers USING( PayMethod ) " . "LEFT JOIN Employers USING( ManagerID ) " . "LEFT JOIN Positions USING( EmployerID ) " . "LEFT JOIN Links USING( PositionID ) " . "WHERE PayMethod = 1 " . Don't bill credit card custmers. (Bill Me only) " AND (( TO_DAYS( Positions.DateCreate ) <= $EOM " . " AND TO_DAYS( DateClosed ) >= $BOM ) " . Only bill for positions that were visible this month. BOM = TO_DAYS() of the first of this month, EOM = end of month. I do a query before this one just to get these values as I didn't want to try to reverse engineer the TO_DAYS() function in MySQL. " OR ( PositionID IS NULL ) " . Ignore entries which have no positions at all " OR ( Employers.EmployerID IS NULL )) "; Ignore entries with no employer record "GROUP BY NameLast, NameFirst, Name, Title " . "ORDER BY NameLast, NameFirst, Name, Title " . '' ); I like the way this is coded into PHP because it allows me to look at the SQL and ignore the PHP code around it very easily. After this query I run thru the data with control breaks on Manager (NameLast, NameFirst), the employers they manage (Name) and the name of the position I am billing on. The data is formatted as an IIF file for import into QuickBooks which prints fancy invoices for each manager. Earlier when we billed by the days the ad was up and calculated the billing amount in the query, but we changed to billing with a two week block followed by exess days and it ended up being easier to figure that out with PHP. I wish I could find a copy of the old query, it was about a page and a half (36 lines) long. It sure makes the PHP code simple! Note: managers can have more than one Employer they control, and Employers can have more than one position on the site, and they can create and close them at any time they want. Query() is a little function I wrote that wraps mysql_query() and error handling code so I don't have to look at it whem I'm writing programs. ----- =With reference to this section:- ----- " AND (( TO_DAYS( Positions.DateCreate ) <= $EOM " . " AND TO_DAYS( DateClosed ) >= $BOM ) " . Only bill for positions that were visible this month. BOM = TO_DAYS() of the first of this month, EOM = end of month. I do a query before this one just to get these values as I didn't want to try to reverse engineer the TO_DAYS() function in MySQL. ----- =I thought, why let these two 'little' things get in the way of something more complicated? So for your consideration please review: mysql> SELECT CURRENT_DATE AS RUN_DATE, -> DAYOFMONTH( CURRENT_DATE ) AS DOM, -> DATE_SUB( CURRENT_DATE, INTERVAL DAYOFMONTH( CURRENT_DATE ) - 1 DAY ) AS BOM, -> DATE_ADD( CURRENT_DATE, INTERVAL 1 MONTH ) AS PLUS1M, -> DATE_SUB( DATE_ADD( CURRENT_DATE, INTERVAL 1 MONTH ), INTERVAL DAYOFMONTH( CURRENT_DATE ) DAY ) AS EOM -> ; +------------+------+------------+------------+------------+ | RUN_DATE | DOM | BOM | PLUS1M | EOM | +------------+------+------------+------------+------------+ | 2002-01-10 | 10 | 2002-01-01 | 2002-02-10 | 2002-01-31 | +------------+------+------------+------------+------------+ 1 row in set (0.00 sec) (apologies for what email will do to the formatting) =I haven't spent a lot of time tinkering with dates within months, so I can't help but feel there must be 'a better way' - of course PHP offers some neat stuff, but this works for now. My next interest would be to see just how optimal MySQL's handling of such calculations would be. If you run your existing query (on a decent chunk of data) and then run it modified per the code in this one, is there much of a time delay? =Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]