Back Up Your MySQL Database with PHP

Obviously the ideal way to back up a MySQL database is to use mysqldump. Failing that, a lot of people will use PHPMyAdmin. Unfortunately, however, not everyone has command-line access to a server; and even fewer are able to execute mysqldump from a script. If PHPMyAdmin isn’t already installed, it can be difficult to get it installed and configured on a shared hosting account. Therefore, it’s sometimes necessary to use a PHP script to back up a MySQL database.

David Walsh has done a nice job of putting together a PHP script to backup your MySQL databases. Below is a copy of the code he provides in his blog article:

AES Encryption with PHP and MySQL

Occasionally, you may find the need to encrypt information within a database. One of the standards for encryption is AES (Advanced Encryption Standard). In fact, in a lot of government institutions, AES is the required data encryption method.

At this point, I feel I need to make an important distinction. Encryption is a reversible method of masking data; not to be confused with hashing, which is supposed to be a one-way encoding method (though, many hash methods can be cracked through various types of attacks).

If you do need to encrypt your data, you have a few options when working with PHP and MySQL.

The first option is a pair of built-in MySQL functions. AES_ENCRYPT() and AES_DECRYPT() make it easy to encrypt and decrypt your data directly through a MySQL query. In order to use the AES_ENCRYPT() and AES_DECRYPT() functions, you will need to provide the data (original data should be provided to the encryption function, the encrypted data should be provided to the decryption function) as the first parameter and a 16-bit key as the second parameter. The same key will need to be used for both functions (otherwise, the decryption won’t work properly).

Finding the ID of a Newly Inserted MySQL Row

If you’re anything like me, you generally set up an auto-incrementing integer field in most of your MySQL tables that can be used as the primary key for your records. A lot of times, after inserting new information into the database, you need a way to find out what ID was assigned to your data when you created the new row. With MySQL, it’s really simple to get that information. MySQL offers a function called “LAST_INSERT_ID” that does just that.

As soon as you finish inserting the new row, if you run another SQL query that looks like “SELECT LAST_INSERT_ID()” on your database, the result you get back will be the most recently added auto-increment number in your MySQL installation.

PHP also has a built-in function to perform this operation. Instead of setting up a separate MySQL query, executing it and retrieving the results from it, you can simply use the mysql_insert_id() function, which will automatically perform those steps and simply return the ID number.

Choosing Random Items from a MySQL Table

At one time or another, most of us find the need to display a random item from a batch of items on our Web site. Whether you are displaying a random banner or promotional item on your home page, or displaying a random post on your blog, you need to know how to do so effectively and efficiently. This article will show you two ways to do so.

Copying Data from SQL Server to MySQL

In my last post, I showed you a way to copy the structure of an SQL Server table into a new MySQL table. In this post, I will show you how you can fairly easily copy the data from that SQL Server table into your new MySQL table.

Before we start, we should make sure we have a complete list of the columns in our MySQL table (which should, if we did everything right in building the table, be identical to the columns in our MS SQL table).

Using PHP to Copy an SQL Server Table to MySQL

At work, we have a scheduled task run every day that pulls information out of a huge Oracle database and dumps it into a Microsoft SQL table on one of our local IIS Web servers. We then have a script on our remote LAMPP Web server (the one we use to serve up our public Web site) that queries that MSSQL table and displays results on the Web site.

Unfortunately, because the Web server and the MSSQL server are in different locations, the results are sometimes slow and unreliable. For instance, if, for some reason, the network connection to our IIS server is down or if the IIS server itself is down, we can’t pull any data from the MSSQL table.

The other day, I began working on a script that will pull only the information we’ll need for any given day out of the MSSQL table and then create a new table in our MySQL installation that resides on the same server as our Web site.

Following are some good tips for you if you decide to do the same thing.