Archive for the 'Cookbook' Category

INTERVAL quirkyness

Monday, March 13th, 2006

MySQL date and time commands annoy me almost as much as the MySQL online manual. I’m moving my office so my MySQL reference manual was in some box… and I had to resort to digging through the manual.
I don’t know why I always have a hard time remembering
SELECT DATE_SUB( NOW( ) , INTERVAL 7 […]

Read full post...

Starting the Command Line MySQL client

Wednesday, March 8th, 2006

For some reason, I had a hard time connecting to an external server through my command line client. I’ve done it before, but I had to dig up the manual to find the answer…
mysql -uusername -ppassword -hhostname
I’m sure that’s what I was doing but it wasn’t working… funny thing. Anyway, I’m posting this so […]

Read full post...

Killing 1NF for fun :)

Friday, February 24th, 2006

An client wanted to add an arbitrary ordering to a set of rows which were being displayed in some legacy code, I had this functionality in newer versions of the software that he was running but due to “technical issues” couldn’t upgrade the software at the time.
The correct way to do this would obviously […]

Read full post...

Select Last Entries from MySQL

Monday, January 30th, 2006

I have a log and I want to list the last time an entry was made by each of the users
SELECT user_id, MAX( log_date ) AS date,
DATE_FORMAT( MAX( log_date ) , “%M, %Y” ) AS english_date
FROM log_table
GROUP BY user_id
I’m not sure if this is […]

Read full post...

SQL to Select a random row from a database table

Monday, September 19th, 2005

SQL to Select a random row from a database table
SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Read full post...

MYSQL: Find a Range

Thursday, June 9th, 2005

Want to find users whose lastnames start with the letters between A and O?
SELECT * FROM users
WHERE SUBSTR(lastname, 1,1)
BETWEEN ‘A’ AND ‘O’;
Notice SUBSTR is 1 based… annoying!
Second observation, SUBSTR is a 4.1 alias for SUBSTRING so if you aren’t using 4.1+ you’ll want to use SUBSTRING rather than SUBSTR

Read full post...