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
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
