When is it better to write SQL vs. PHP?

dcardena

Hi.

Is it more of a personal preference to use PHP functions vs. SQL (or vice-versa) or are there measurable advantages to one approach over the other?

Clearly there's some stuff that has to be done in one or the other, but when it comes to tasks like inserting a date into a field, is it better to get the date in PHP and then just insert it into your table, or is it better to let SQL handle it?

That's a very generalized example, but this question comes to mind as a result of seeing a few different approcahes to building apps- some developers seem to want to do as much as possible in SQL, while others (including myself) seem to do as much as possible with PHP, then use SQL when necessary.

What's your take?

-DC

Roger_Ramjet

SQL is a powerfull language, and beautifull in its simplicity. It is based upon 1 consistent mathematical model, Codd's Relational Calculus, and if properly implemented it's operation can be trusted. The database engine is optimised to work upon multiple records in a fast and reliable way. All this adds up to an execution speed across multiple records that is 10 to 20 times faster than anything you can achieve in code, in any language.

So if you need to manipulate multiple records, SQL every time.

What you see here is a divide between backgrounds. Those of us who come to php from a database or backoffice applications programming favour SQL, those who come to PHP from web design and programming feel much happier and more in control usinf code.

Sxooter

Also, there are a few more issues that pop up. For instance, there may be non-standard functions in one or another database that work a charm, but don't in another. If your code needs to be portable, it might be better to just do it in PHP.

One of the most common issues you see posted around here re: this subject is date handling. Since older versions of MySQL didn't have intervals, it was quite often the case that just doing in the math for such in unix timestamp land was the easiest way to go with that database. PostgreSQL, and newer versions of MySQL (as well as most other databases extant) have intervals, and it's better to let the database do the date work then, because it can handle things like time zone automagically.

Roger_Ramjet

Which is also a case for using an database that supports user defined functions. Being able to write your own functions that format date, currency, etc to your own requirements and then call them in the sql query is a godsend. This is especially usefull when, like me, you have to send and receive data to and from people tied to legacy systems like IBM mainframes; or with a panchant for Excell spreadsheets as a means of corrupting the data they send you.