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