Do Know Evil - Tyler Mulligan’s Tips and Tricks

More often than not, when I’m doing form to database interactions, I use the same field names. This means there is some redundant information in my code, making my job more difficult. As such, I’ve started to create feather.php, a SQL statement constructor class that works off data sent by the $_POST or $_GET arrays.

It’s not ready for a public release as there are still some security issues to address (see TODO) but I wanted to get some thoughts, opinions and suggestions on this.

For those of you concerned about using the same field names as database columns, I plan to add associative arrays to allow you use different names.

i.e. something like this:

$field_names array('the_first_name','the_last_name','the_zipcode');
$column_names = array('first_name','last_name','zipcode');

To do daily database backups, I use cron jobs. If you aren’t familiar with cron jobs, think of the as scripts that run on a timer. Much like ’scheduled tasks’ on windows. To edit your cron jobs on dreamhost, locate the ‘cron jobs‘ menu item located under ‘goodies‘ on the main menu:

Before we add a cron job though, I want to familiarize you with the script and have you run a test to save you a headache later.

#! /bin/sh
 
# Daily backups on your database with email notification
# Tyler Mulligan
 
# file
dateVar=$(date +%m-%d)          # Date variable to append to filename (default: month-day | 11-24)
savePath="/home/tyler/backups/" # Backups are stored here
fileName="my_db_backup"         # File name minus the date
 
# email
subject="My Database Backup ${dateVar}"
email="db_robot@mydomain.com"
 
# database
username="root"      # username
password=""          # password
hostname="localhost" # hostname
database="database"  # database
 
mysqldump -u $username -p$password -h $hostname $database | gzip  > $savePath$fileName-$dateVar.sql.gz
 
uuencode $savePath$fileName-$dateVar.sql.gz $fileName-$dateVar.sql.gz | mail -s "$subject" $email

I’ve set it up so you fill out your information like any other config file, the last two lines do all the work.

I suggest you create a new text file called ‘dbbackup.txt’, and paste the above code in and saving it for future reference. It’s always good to have a clean slate to start from. Once you have that done, fill in your file, email and database variables and give it a test run.

Create a shell script through ssh and run that.

Copy your personalized database dump code, log into your server and type:

vi my_db_test.sh
[press i]
right click (to paste the copied code)
[press esc][type ":wq"]
[press enter]

Your script is now written to a file called ‘my_db_test.sh’.

chmod +x the file and run it:

chmod +x my_db_test.sh
./my_db_test.sh

Once you receive the email, check your backup directory and verify that the database was properly dumped. If you don’t receive and email, you did something wrong.

If everything worked fine, return to the dreamhost cron job page, add a cron job and paste your working code into your newly created cron job.

Save, wait a day and you should receive your email. It’s a wise idea to grab a copy of your backup every week or so to store locally.

Dreamhost is actually offering a special for their 10th anniversary right now, 500gb disk space, 5TB transfer for only $5.95 a month. I’ve been with them 2 years and I’ve been pleased.

Over the past few days, I’ve been creating some rather large and confusing SQL queries, so I went on a hunt to see if maybe there was a visual aid that would make the selects, joins and sub queries easier. I was a bit disappointed to find that MySQL offered a Query Browser that wasn’t available for download… or was it?

After a little more research I came about a link to a slightly thinner client in the developer zone under the Generally Available (GA) license. I’ve played around with it a bit and while it wasn’t exactly what I was looking for, it’s helped me during those times I don’t feel like waiting on phpMyAdmin.

Check out this screencast to get a better idea of how this tool works. I’m sure I’ll grow to like this tool a lot more in the future, I just need to learn more about it’s functionality. I thought I’d pass this tip onto you.