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]
