Snippets / MySQL

Exclude "The", "An" & "A" from MYSQL Ordered list

The aim was to display results from my database in alphabetical order and not having words such "an" or "the" to impact on the alphabetical order (similar to iTunes album order).

So for example

The Doors would be displayed in the "D" artists

A Solution (Slow one)

One way of doing this I thought we could trim the appending name of the artist if it matched any preset criteria. This solution is a temporary one until I find a quicker method!

SELECT artist_name
FROM artists
ORDER BY TRIM( 
LEADING "A "
FROM TRIM( 
LEADING "An "
FROM TRIM( 
LEADING "The "
FROM LOWER( artist_name ) ) ) ) 
LIMIT 30

A Problem

When getting the results, the query does seem to take a little while

100 rows in set (0.29 sec)

13 years ago / Read More

Snippets / MySQL

Delete Duplicate Rows in MySQL

Over time tables can contain duplicate entries, one of the main reasons is the system inserting the data is either not preparing it correctly, or not checking against existing records.

The Statement

Before you run the query make sure you backup the table as it will remove data, an extra check you could always do a SELECT query instead of DELETE.

DELETE FROM table1
USING table1, table1 AS vtable
WHERE vtable.id > table1.id
AND table1.field_name = vtable.field_name

13 years ago / Read More

Snippets / MySQL

Backup all MySQL databases into seperate files with crontab

It's highly recommended to keep a backup of all your data and with the following script you can setup an automated task to create a weekly backup of your data without having to manually run it; with linux!

The Shell Script "db-backup.sh"

Next Step Add to Crontab

In order for the script that we have created to be executed automatically we need to add this to the crontab task scheduler.

View existing crontab scheduled tasks

crontab -l

Edit the crontabs

crontab -e

Normally you specify the exact minute, hour, day, week, month with the either with an asteric character * (which means every) or a numeric character (which represents moments in time

The crontab also has special keywords which are predfined values

string         meaning
------         -------
@reboot        Run once, at startup.
@yearly        Run once a year, "0 0 1 1 *".
@annually      (same as @yearly)
@monthly       Run once a month, "0 0 1 * *".
@weekly        Run once a week, "0 0 * * 0".
@daily         Run once a day, "0 0 * * *".
@midnight      (same as @daily)
@hourly        Run once an hour, "0 * * * *".
@daily

Add the backup script to the crontab

@weekly /path/to/backup-script.sh

13 years ago / Read More

Snippets / MySQL

Filter results by todays date - MySQL

MySQL is a very powerful open source database and there was no suprise when I found there was a way to retrieve records from a database that expired at @ 11.59 of the "end_date" field. You can alternatively do this via php date() function as well.

The Solution

The Query

SELECT id, title FROM meetings WHERE start_date < NOW() AND end_date > DATE(NOW()) ORDER BY id DESC 

13 years ago / Read More