A Day in Paradise

Some code that I am reworking has a table that looks like this:

---------------------------
| uid |   key   |   val   |
---------------------------
| 1   | name    | Aaron   |
| 1   | age     | 28      |
| 1   | iq      | 50      |
| 2   | name    | John    |
| 2   | age     | 17      |
| 2   | iq      | 110     |
| 2   | geekfu  | 100     |
---------------------------

And I want to create a view that looks like this:

---------------------------------------
| uid |   name    | age | iq | geekfu |
---------------------------------------
| 1   | Aaron     | 28  | 50 | null   |
| 2   | John      | 17  | 110| 100    |
---------------------------------------

The PHP code I’m looking at is running through loops to organize the code, I am thinking there must be a simpler way. Any pointers to put start me in the right direction?

Cross posting this nice article from Mike Kruckenberg into my PHP section, since it has some great things to keep in mind.

I recently went through this on a platform I was maintaining. The thing that bit me in the ass was some very silly ways previous programmers were parsing timestamp fields. Lesson: do it the right way the first time. The MySQL date/time functions are able to do whatever you need done, there is no need to do date parsing in PHP.

The other issues are mostly minor, and if you do need to change them are relatively easy todo with a global search and replace, or configuration option. Those hours of digging through code looking for timestamp parsing code taught me to RESPECT THE TIMESTAMP!

type:google engEDU – Google Video

I’m saving this for when I have a couple of days free

MySQL AB :: Coding and Indexing Strategies for Optimal Performance

Must watch sometime

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 DAY );

2006-03-06 17:03:21

SELECT NOW( ) - INTERVAL 7 DAY;

2006-03-06 17:06:54

I’m not sure what confused me… maybe the fact that I was trying to do INTERVAL 1 WEEK

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 that I can remember to read the article I linked to above.

Just bumped into this cool contest. Find a bug, blog about it and add your feed to Planet MySQL and you can be eligible to win a weekly iPod Nano!

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 to add a “weight” field to the database and use an ORDER BY to sort the rows, however this would mean changing code in the input/edit feilds which I didn’t have time for. My simplified 5-minute solution is as follows:


SELECT name, SUBSTRING(position FROM 3) as position, biography, image
FROM members
ORDER BY SUBSTRING(position, 1,2) ASC


Yes it broke 1NF and will probably end up on an SQLWTF some day, but it was a quick and easy solution which only required a change in the SQL string and didn’t touch the code.

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 the best way. The other options is to use WITH ROLLUP, I didn’t look into that option. This one seems to work, the only iffy part is the MAX(log_date), I may be pushing it just a bit

SQL to Select a random row from a database table

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

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