MySQL, Backup Database with Cronjob with Date

/usr/bin/mysqldump -u USER -pYOURPASSWORD DBNAME | gzip > /BACKUP/PATH/$(/bin/date +\%m\%d\%Y-\%H\%S).gz

MySQL how to add a new column/field in a table

MySQL how to add a new column/field in a table

ALTER TABLE table_name ADD coupon_new_column DATETIME

e.g.

ALTER TABLE coupons_coupons ADD coupon_expiredate DATETIME

SQL, Many-to-Many Relationship

To implement many-to-many relationship, we first need a junction table. The schema of this table has at least two column: primary key both original tables which have many-to-many relationship with. In short,

1. Create a junction table
2. Put primary key from both table into this new table

Now the junction table became the Many side of the other two tables. And the primary key of the junction table is the 2 primary key combine from the other two tables.

Movie_table
movie_id movie
1........Titanic
2........Aliens

Category_table
category_id category
1...........Love Story
2...........Adventure
3...........Drama
4...........Sci Fi
5...........Horror

Movie_Category_table
movie_id category_id
1.........1
1.........2
1.........3
2.........4
2.........5 

http://www.webmasterworld.com/databases_sql_mysql/3749320.htm

SQL, One-to-Many Relationship

1-to-many relationship is obvious if you think about it. This can be achieved by putting the primary key of another table (1) to the current table (many) as the foreign key.

In other words, put the foreign key in the "many" side.

SQL, Union Operator

Sometimes you need to to combine several tables together, and make them a bigger table. Like combine January data (table) with February data (table).

But what if you do not want to create this big table in the database? You can use UNION operator.

SELECT 1, 'January' UNION ALL
SELECT 2, 'February' UNION ALL
...
SELECT 12, 'December'

Or make them a stored procedure.

http://stackoverflow.com/questions/1376361/returning-data-without-accesing-a-table