How Mysql reduce overhead from PHP!!!!!
I enjoy to play with Mysql queries. what i learn from my past experience is,if you are doing a maintainance project or you are handling a project which is developed by some other developer and he mess up with the database definitions, than you “WILL LEARN A LOT NEW THINGS”…………. How?
As your table is not normalize, you have to fire queries with JOIN,Sub-query or you will explore internet to complete that requirement, and this way you will learned writing “CompleX” queries.
Instead connecting SQL server several times (which increase loading time) I used to have different inbuilt functions available with Mysql. There are several like
STR_TO_DATE, BINARY ,FIND_IN_SET,REPLACE,IF and CASE statements,Date and time functions and many more….
Many people i came across (while taking interview) they don’t use this function and they manipulate values in PHP as per their requirement. Instead i use Mysql to get the desire result itself from Mysql.
It is always better to get the result directly from mysql rather doing same work in PHP.
Take an example, i got a project in which they store date in varchar instead of timestamp. Now i have to show the time in 24 Hours format in browser. I use CASE statement in MySql so that i get the date in required format. This will reduce my overhead writing extra lines in PHP.
SELECT *, CASE WHEN(time > '12:00:00' AND meridian = 'PM') THEN SUBTIME( time, '12:00:00') WHEN(time < '12:00:00' AND meridian = 'PM') THEN ADDTIME( time, '12:00:00' ) ELSE time END as fulltime FROM uplist order by fulltime desc