I use MySQL all the time when building websites. I have a self-built CMS and a number of clients all running off of the system. Here are a few SQL commands that I use regularly:

Dump databases

> mysqldump --quick -all-databases -uadmin -p > alldatabases.db

Dumps all of the databases to a single file. Along with regular server backups I use this to keep copies of everything for my own reference.

–quick == forces MySQL to retrieve and dump the tables a row at a time rather than buffering. Useful for large tables

–all-databases == self explanatory I hope

-uadmin == username admin

-p == ask for password. My installation needs the password to perform any actions

To dump a single database:

> mysqldump --quick -uadmin-p database_name > filename.db

I personally use .db at the end of my files, but they’re simply text files of INSERT instructions and can be named however you like. From a dumped database, you can import all of that content into a new database:

> mysql -uadmin -p database_name < filename.db

This will fail if for example, the keys for a table already exist in both the database and the import file.

Search and Replace

I find this really useful. To search and replace words across a table, in a particular field:

mysql> UPDATE table_name SET field_name=REPLACE(field_name_again,

'look for me','change to me) WHERE..

The WHERE part is optional. Leave out any WHERE filter if you want to apply to all records in the table.

More to come…

Why does it have to be so hard to work with!!!

Web development using a Mac, in my opinion, is better from a standards point of view. I build and test on Firefox primarily, using the web developer tools (and other great add-ons). Then when I’m generally happy with the templates and CSS I try them out in IE and keep a separate style sheet to ‘fix’ what IE broke.

This week I’ve been plagued with an ongoing issue of IE freezing on printing. I’ve built an application form module for a client website (all PHP) and through the CMS they can view all completed application forms using a print stylesheet so they can print it out. For some strange reason IE would hang when processing this page, but not always.

Well I’ve discovered what was causing it (at least in my CSS). Here it is:

#dform textarea {
    font-size:12px;
    }

Take that font definition out and all is well. But WHY??