Re: SQL Readability.. (was Re: most powerful php editor)

----- Original Message ----- From: "Jochem Maas" <jochem@iamjochem.com> To: "Larry Garfield" <larry@garfieldtech.com> Cc: <php-general@lists.php.net> Sent: Sunday, January 28, 2007 12:55 PM Subject: Re: [PHP] SQL Readability.. (was Re: most powerful php editor)

Larry Garfield wrote:

On Saturday 27 January 2007 1:14 pm, Jochem Maas wrote:

query builders are alot more fiddly to get 'right' than one might imagine, dealing with NULLs, booleans and dates for example (as Satyam pointed out) can be a right PITA.

I actually almost never use native date types in the SQL database. I just store unix timestamps and do the math in PHP. Dates are completely unportable anyway. I also tend to use ints for booleans, too, although beefing up the switch statements in the code to handle native booleans should be trivial.

mysql doesn't have booleans does it? at least not versions I have to use. with regard to date stuff, many people take the opposite approach and do most of the date math inside SQL - most DBs have kickass date calculation functions btw. and for the times when you need/want unix timestamps, mysql atleast, gives you UNIX_TIMSTAMP().

At least as of MySQL 4.1 (haven't played with MySQL 5 much yet), yes, MySQL has no native boolean data type that I know of. The standard alternative is TINYINT(1), which technically gives you values 0-9. And yes, I agree that MySQL has fairly decent date manipulation routines. But at work we do try for database independence when possible, so except on specific projects we try to avoid it.

again we differ :-) I have never bought the 'data independence' story - in practice it's of little value imho most of the time (granted certain products do benefit - but what I build doesn't fall into that category) and I find it crazy to end up with a situation where the most advanced peice of data manipulation software in a given stack is dumbed down to the lowest common denominator [of DB engines]. On more complex project I try to cram as much of the data intregity and business logic in to the database itself (for which I use firebird mostly) because it means being able to create different clients to the data without replicating [as much] business logic (e.g. website and desktop app). besides which the required stored procedures and triggers are usually hundreds of lines less than their php equivalent AND more importantly they are intrinsically atomic (in the sense that database transaction 'should' be). rgds :-)

Hear!, hear! (or something to that effect) Satyam

-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php