My Blog

Better than using PHP to time format date times stored in your database for easier human readability is using MySQLs built in functions:

To turn a date time stamp into a readable time on the 12 hour clock with an AM/PM signifier
TIME_FORMAT(date_time,’%h:%I:%l %p’) as time

Returning just the date time portion of date time stamp
DATE(date_time) as date

Make a date pretty
Unlike the actual dates I go on, in MySQL you can actually make her pretty, the following code would convert 2008-01-01 into Jan 01, 2008.
DATE_FORMAT(tour.date_time,’%b %d, %Y’) as date

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

From the MySQL command line use the database your SQL file will be affecting.

use database;

To execute the file.

source /path-to-file/file.sql

I always forget this one.

(SELECT invoice
FROM orders
WHERE date=NOW())
UNION
(SELECT invoice
FROM other_orders
WHERE date=NOW())
ORDER BY date

The database I was working on the other day is FAR from normalized. It stores shipping types as regular VARCHAR strings. We needed to orderby Overnight and International orders so they print first in the batch. Using a case statement I was able to create a temporary sane column in this query, using the integer values in this temporary shipType column I was then easily able to orderby and it only added 1/10 of a second to the query’s execution time. Here is an example of the MySQL case statement:

				CASE SUBSTRING_INDEX( shipping, '|', 1 )
				     WHEN 'Overnight' THEN 1
				     WHEN 'Overnight Delivery' THEN 2
				     WHEN 'International' THEN 3
				     WHEN 'USPS International Shipping' THEN 4
				     WHEN '' THEN 5
				     WHEN ' * Product(s) qualify for Free shipping * ' THEN 6
				     ELSE SUBSTRING_INDEX( shipping, '|', 1 )
				    END AS shipType 

At work we interact with a MS SQL server that our shipping software runs on. We are a MySQL shop so don’t use MS SQL too much and the database in this proprietary shipping system is pretty large (about 100 tables). I needed to find a table that another table had a relation too and only knew the column name. So I ran this

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = ‘Alias_ID’ )

in MS SQL 2000 Query Analyzer and returned a list of 6 tables.

A lot better to troll through 6 tables than 100 tables.