a few mysql tricks

Here are a 2 mySQL tricks i write down here for myself, hopefully it is of some use to you.

have a date formatted correctly via mysql, no need for php to do that:

To have a mysql formated date (2007-02-17) rendered as 17-02-2007, you would do this:

SELECT field1, date_format(dateFieldName,'%d-%m-%Y') as nice_date FROM tableName

(see here for more date formatting masks).

Select only a part of a text (for abstract or excerpts)

For instance, if you want to get only the 50 first words of a text stored in a “body” field, you’d do this:

SELECT SUBSTRING_INDEX(body,' ',50) as excerpt from ...

Using select queries inside a main select

Case: I have a table “images” and an “album” . I would like to display the list of available albums, showing the album name, the number of images inside, and say, a thumbnail of the last uploaded image. This is how i achieve it:

SELECT a.album_id
, a.album_name
, ( SELECT COUNT(image_id)
FROM images as i1
WHERE i1.album_id = a.album_id
) AS total_images
, ( SELECT thumb_file_path
FROM images as i2
WHERE i2.album_id = a.album_id
ORDER BY i2.image_id DESC LIMIT 0,1 ) AS last_image
from albums as a
WHERE project_id='2'