Hello,

I want to introduce you a simple tool for updating the mySQL database of your PHP project. I'll explain how it works, it is dedicated to the developers so they can share the updates between them without much headache.

I'll write a case here about how it's ment to work. Let's sopose you are working also with a version control system like SVN, when you make changes to your database, you run queries on a desktop mysql client or an online one like phpMyAdmin, after that you copy the code you just executed and then save it to a sql file, and send it to other developers and tell them to use it to update their database.

So, this way, you must let them know about your changes and they must remember to run the sql file on their machine or otherwise the application can complain about missing tables or fields in the database.

With this tool what you accomplish is that you still have to manually save the changes into a sql file but when you commit it to subversion and the other developers download it on their machine, then the next move they make in the application it will automatically read the sql file and update the database before any other action. And also, the changes from other developers will be automatically updated on your local database after you download the new code from SVN.

Okay, so now I'll explain how to configure it, it's a single PHP class that must be included and run at the top of every page of your application.

This is an example about how you must use it, place this inside a file that you run when you initializate your application:

<?php

# Include the script.
include_once('AutoDbUp.php');

# Load the script and specify the path where all sql files are saved,
# the files inside that directory must contain numeric names and increasing
# that will be the revision number of your database, example:
# 1.sql, 2.sql, 3.sql, 4.sql, 5.sql and so on, for each update you make.
$autoDbUp = new AutoDbUp('path/to/sql/files');

# Run the script's upgrade method to search all the new sql files and run them.
$autoDbUp->upgrade();

?>

For the correct working of this script, you will have to customize some methods inside the class to make them compatible with your application, because the tool checks the version of your database and after running one sql file it updates that version to the new one, some of the functions that need to be customized are the followng:

• getDbVer() - Command that consults your configuration table and retrieves the current database version, you can also make it write the current version number to a file instead of saving it in a table.
• updateDbVer - Updates the database version after an sql file has been succesfully executed.
• dbQuery() and getMysqlError() - Change this two if you have your own database class for mysql, and also check the last functions that BEGIN, ROLLBACK, and COMMIT a mysql transaction if you need to change them.

That's all, you can download the class here:
http://www.alexandru.es/scripts/AutoDbUp.rar

Let me know if you have installation issues or any feedback! :)

Thanks for the parse_mysql_dump function posted on PHP website:
http://www.php.net/manual/en/function.mysql-query.php#55811

I'll get back with a better explained use and a full downloadable application that implements it.