Edmonds Commerce Blog

If you find yourself laboriously building SQL queries by typing each field = 'value' statement... think again.

Imagine this:

This will generate a valid SQL insert statement with all of your field, value pairs set up.

Time saver or what?

MySQL dumps are often used as a quick and easy way of backing up an entire MySQL database in one go. However, they are only really designed to restore whole databases as well. So what if you only want to restore certain tables?

I have written this nice little PHP script which will take your dump file and split it into lots of little sql files - one for each table. You can then restore which ever tables you want.

Simply create a php file in the same directory as your dump file. Name it something easy to remember. Then go to it in your web browser, input the name of the dump file and click go.

As this is a fairly massive task (depending on the size of your dump file) you may find it works better on your local test server with some generous php_ini settings for max_execution_time etc.

If you are using windows (with XAMPP) to develop your MySQL applications, you might currently be using phpMyAdmin for your DBA tasks. The phpMyAdmin package is an awesome tool and is a must have, however due to the fact that it runs under Apache and PHP - it can be tricky to use when dealing with big database dumps or other heavy processing.

At this stage, you may decide that the best thing to do is to hit the command line. That's fair enough if you don't mind using command line applications. If however you fancy keeping it GUI, then I can highly recommend using the free community edition of SQLyog.

This open source community edition is a free version of their main Enterprise Edition software which is paid for. If you are doing a lot of DBA work then you may want to consider supporting this company by handing over some cash for the fully featured software. The enterprise edition can handle external as well as local MySQL database administration, using SSH, SSL or HTTP/HTTPS tunnelling. It features all kinds of GUI functionality for building your database schema and designing queries. For the serious DBA's out there it may well be worth a look.

For the occasional big DB import / export session on the local windows host though, the community edition does seem to suffice.

You can download both the community and the enterprise edition here:

http://www.webyog.com/en/downloads.php

To compare functionalty check out this page:

http://www.webyog.com/en/sqlyog_feature_matrix.php

This is a nice little function which I struggled to find elsewhere on the web.

As discussed elsewhere this is not the kind of thing that should be included in a public facing script, but for administration tools etc its pretty handy:

This function grabs the column information for the table, then it loops through the info looking for the column. If it finds the column then it acknowledges that it exists and ceases the loop.

After this stage, the function checks to see if exists is true or not, and if it is not true then it adds the column with the attributes defined when calling the function.

The default attributes are for a VARCHAR field up to 255 characters in length and NULL enabled.

Sometime in MySQL you want to copy an entire table from one database to a separate database. One example of when you would want to do this is if you are making a backup of a table before you apply some kind of processing to that table. There is nothing worse than ruining a database table and losing valuable information. To make an instant backup is so easy that there really is no excuse not to make backups at important or risky stages.

Here is how to do it:

This will delete your existing backup table completely, then will remake it copying all structural information and content from the live_table in the live_db.

Related Blogs
http://www.phpclasses.org/browse/package/4017.html
http://www.sematopia.com/?p=61
http://www.yinfor.com/blog/archives/2008/02/mysql_backup_and_recovery_meth.html

One of the most common things that needs to be done when tidying up a database is to bulk find and replace data in MySQL tables. This can be for things like spelling mistakes, changing categories of products or any other value held in a MySQL database table.

Its an easy thing to do though and here is the code:

note - nice syntax highlighting plugin used is mentioned here