When you want backup a MySQL database, you don't have many solutions.

You can use PhpMyAdmin to backup the database but you will figured out that the export doesn't work so good: there is some information lacks like the Foreign key or other things like that. You'll also occur timeout problem when trying to backup -and restore- huge database.

You can also read a fex pages in the MySQL doc and you'll find Bash command to do exactly what you want. In only 1 line you'll be able to backup you're whole database in one text file.

Here is the backup command:

mysqldump --user=USER --password=PASS --add-drop-table DATABASENAME

This command show you all the MySQL query to execute to retrieve your entire database: the CREATE instructions, adding the constraints if needed and of course, the INSERT lines. The option --add-drop-table add the instruction to delete the table just before their creation: very useful to don't worry about cleaning the database before the backup.

If you want save more than one database, you have to replace DATABASENAME by --databases DATABASENAME1 DATABASENAME2 DATABASENAME3.

And if you want save all the database in you server, you can use --all-databases instead. Be sure the user used in the command line have access to all the databases you want back up.

Right now the command show you the query. Even if you're Flash Gordon, you can't do anything with that. A simple pipe to a file will save everything for you:

mysqldump --user=USER --password=PASS --add-drop-table DATABASENAME > DUMP.sql

To restore the database -or create it in an other server-, you have simply to upload the file saved just before and run this command in the same place of this file:

mysql -u USER --password=PASS DATABASENAME < DUMP.sql

Let's start with the basis: A normal paging. To do so, you use the LIMIT parameter in the MySQL query:

SELECT * FROM user LIMIT 20,10

Where we show the second page for the users with 10 elements by page.

You will probably order the result to be easier to use. For example:

SELECT * FROM user LIMIT 20,10 ORDER BY name ASC

At this point, everything was pretty easy and you probably already knew that.

Now we want to randomly order the result. Intuitively, you will do something like:

SELECT * FROM user LIMIT 20,10 ORDER BY RAND()

Which is not totally wrong. The problem with this solution will occur when changing the page, the order will be different -for sure, it's random. By changing the page you will probably see some recurrent data, and the visitor will never see all the result by reading all the pages. That's not expected.

The solution consists to generate a random number in PHP, stores it in a session variable, and finally use it in the MySQL query inside the RAND parenthesis. And it's finish. Therefore the random number used is every time the same for the visitor session, and the global order will stay the same in the differents pages.

The PHP code to generate and stored the random number:

$rand = $_SESSION['rand'];
if (empty($rand)) {
  srand((float)microtime()*1000000);
  $rand = "0.".rand();
  $_SESSION['rand'] = $rand;
}

Of course, you have to open the session with session_start() at the top of your PHP script before every out or header send -or you can use ob_start().

Finally the MySQL query becomes:

SELECT * FROM user LIMIT 20,10 ORDER BY RAND($rand)

Voilà, you can make pagination with random order.

Version française de ce billet