Page 1 of 1

Calculations / Formatting with MySql or PHP ?

Posted: Fri Sep 08, 2006 3:13 pm
by tdnxxx444
When I first started programming, I kept hearing that when doing queries and needing to do calulcations and formatting the data, you should always let MySQL do the work instead of PHP. Is this true and why or why not?

Posted: Fri Sep 08, 2006 3:31 pm
by kendall
tdnxxx444,

Well i dont know about that...but there are advantages when doing calculation with either language...probably more depends on the data type for instance date formating and date/time periods...i usually prefer performing that on MYSQL date columns thant trying to do it in PHP

Posted: Fri Sep 08, 2006 3:57 pm
by RobertGonzalez
From what I understand, complex math is better handled in PHP. Regular math and calculations that can be handled by the DB are better suited for the DB. It balances the timing of the execution of the script and allows the DB to use some resource instead of the code (so your code can be busy doing other things).

Posted: Fri Sep 08, 2006 4:31 pm
by DrTom
mySQL is most certainly faster at msot string formatting and basic math, but there are other things to consider.

For instance, at my company we have 2 services. A real time data feed to several companies (Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome). Also, several websites that are the same back end but are hosted for various companies (generally offering loans in exchange for certain information and permission to sell your information). The database for the real time data feed, is massive. It's comprised of several tables in the hundreds of millions of rows as we're required to keep TONS of legacy data, and we receive about 1.5 million entries into said system every day. The same database server hosts an entirely seperate database process that hosts databases for a couple of the aforementioned websites. If I were to query a 300,000,000 row table, and throw in a bunch of date conversions and string formatting and even the most basic of math, I'd be fired for lagging dozens of websites. Querying those tables is just insanely intensive(They really need a redesign, but that part of the system was here way before i was, and they are happy with it the way it is). Anyway the point of the matter is, there are situations where you'll want PHP doing as much of the work as possible, simply because there are other people using the database server that are, quite frankly, more important than you. However, I doubt you'll find many of those situations on your typical website and in those cases then you'll want mySQL doing it.

Posted: Fri Sep 08, 2006 4:37 pm
by Luke
DrTom wrote:(Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome)
?? :evil: you are evil.

Posted: Fri Sep 08, 2006 4:43 pm
by DrTom
The Ninja Space Goat wrote:
DrTom wrote:(Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome)
?? :evil: you are evil.
lol oddly enough it was one of the most interesting projects I ever worked on. I'd never written anything that just stays running all the time, and sleeps most of the time but as soon as new stuff arrives just does it's thing and sends it where it needs to go, let alone written something like that in PHP. Of course, I thought it was dumb to write it in PHP, but that's what the boss man said, since all of our developers here are PHP and they wanted other people to be able to work with it. Not to mention this project generates well over $3 million in revenue a month, and it's kinda cool know something I wrote makes THAT much money for the company. Now I think I need a raise come to think of it

Posted: Fri Sep 08, 2006 5:08 pm
by tdnxxx444
Reason I ask is, what if you are using the same query in seperate places for your application. What if you SELECT a date, and in one place you want to format by 01-01-2001? What if in another place you want to format it 01-01-01? How about another, you want do, January 1, 2001?

Also, what if you have another query where you SELECT all the items bought today.
i.e.

Code: Select all

SELECT items 
FROM items
WHERE date = '2001-01-01'
What if you want to get the sum of number of items bought that day, would you use a SUM in the MySQL, or do it with PHP? Also, what if the items cost $5 and you want to get the total dollar amount sold that day? Would you then write a seperate query and do the calculation in the query, or would you use the same query and just do it with PHP?

Posted: Fri Sep 08, 2006 6:13 pm
by RobertGonzalez
Logically, if you are going to reuse data from the same query result set you are going to want to handle manipulation/calculation on the code side. But if it is a matter of calculating/manipulating for one instance, let the DB do it.

Posted: Sat Sep 09, 2006 2:18 am
by matthijs
DrTom wrote:Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome
Sorry, but don't we have a policy here to what people can say?

Posted: Sat Sep 09, 2006 3:23 am
by RobertGonzalez
Yes, but there is really nothing that says he can't post that.