** Reply to note from "DL Neil" <[EMAIL PROTECTED]> Thu, 10 Jan 2002 14:48:02 -0000 > 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... $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. Rick Rick Widmer Internet Marketing Specialists http://www.developersdesk.com -- 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]