** 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]
