Grouping MySQL entries in half hour increments

One of my hobbies at work is creating page-long MySQL queries to reduce the number of calls to the database from php. The following query is very mild in size but took a few weeks of brainstorming to develop. It is part of a reporting feature I built into an asterisk phone server. Calls are logged with an entry time, hold time, agent talk time, and whether the call was abandoned before an agent answered.

$query = "SELECT concat(if(hour(enter)>12,concat(hour(enter)-12),
concat(hour(enter))),':',
if(minute(ENTER) < 30,'00','30')) as `hour`,
concat(HOUR(ENTER),if(minute(ENTER) < 30,'00','30')) as `group`,
if(COMPLETE_CODE='ABANDON','ABANDON','SUCCESS') as `status`,
count(call_id) as `calls`,
SEC_TO_TIME(avg( time_to_sec(hold_time ))) as `average hold time`,
SEC_TO_TIME(avg( time_to_sec(agent_time ))) as `average agent time`
FROM `queue_data`
WHERE date(ENTER) = '".$date."'
GROUP BY HOUR(ENTER),
if(minute(ENTER) < 30,'ABANDON','SUCCESS'),
if(COMPLETE_CODE='ABANDON','ABANDON','SUCCESS')";

Fun queries like this can improve performance by reducing queries and using MySQL's processing engine instead of PHP's and make code cleaner. Happy MySQL'ing

Today's linux hint

I've had an annoying hiccup on a server at work. It's my first production linux server for an outside company for web serving. PHP, Apache, MySQL, and Sendmail are all working great by themselves, but php pages will not use the mail() command. The page goes through immediately, but the mail is never sent.

I tried adding apache as a trusted user in sendmail. I tried making sure that the path to sendmail was correct. Then I tried running a test by typing php mail.php as root from SSH... and it worked.

So I logged into SSH as another user, ran the command again and got

WARNING: RunAsUser for MSP ignored, check group ids (egid=506, want=51)
can not chdir(/var/spool/clientmqueue/): Permission denied
Program mode requires special privileges, e.g., root or TrustedUser.

A simple chmod -R 777 /var/spool/clientmqueue/ cleared up the problem.

So that's my linux hint for the day.