Running SQL Queries with db_update.php

[Thoul]

When customizing your forum, you may occasionally need to add or alter things in your database. Many hacks, for instance, have SQL sections in their instructions, and these sections contain a number of SQL queries that should be run on the database. There are four ways you can run these queries on your database.

The first three ways - using author supplied scripts and two uses of phpMyAdmin - are discussed in JohnMck's tutorial, Installing phpBB Hacks. In this tutorial, we will focus on the fourth method and some common problems encountered when using this method. The fourth method is to create your own script to run the SQL queries for you.

This type of script can be created using our DB Update Generator form. In most cases, you can simply copy and paste the SQL queries into this form, and it will create a downloadable db_update.php file for you. You can then upload this file to your forums, log in as the forum administrator, and visit the db_update.php file. It automatically processes the queries for you. Once you are finished, delete the db_update.php from the server. Viola - you've completed the SQL instructions you needed.

[Thoul]

Occasionally, there are a few problems that you might have with the files created by the DB Update Generator. Here is a quick summary of some errors you might encounter and how to fix them.

• "You are not authorised to access this page"
  If you see this message when you try to access the db_update.php page on your server, you are not logged in as an administrator. Simply log in as an administrator and return to the page.
• Parse error: parse error in /your/path/db_update.php on line xx
  Parse errors can occur in a generated db_update.php if there is a problem with the SQL instructions you entered into the generator. The most common cause is an SQL instruction containing a quotation mark, like this one. The quotation marks here have been emphasized for clarity.
  

  Quote:

  ALTER TABLE phpbb_users ADD user_gender TINYINT not null DEFAULT "0";

  
  The DB Update Generator encloses queries in quotation marks so that the db_update.php file will be able to add them to your database. This is what creates the parse error. To fix this, change the quotation marks to apostrophes (or single quotes), as has been done in this example.
  

  Quote:

  ALTER TABLE phpbb_users ADD user_gender TINYINT not null DEFAULT '0';

  
  
• Error: Table 'phpbb_table_name' already exists
  Error: Duplicate entry 'config_entry' for key 1
  Duplicate column name 'user_newcolumn'
  These three errors are essentially all saying the same thing: the db_update.php tried to add something from the SQL queries, but that thing already exists in your database. This usually means that the item in question has already been installed in another way and you can just ignore the error message. Sometimes it may mean that there is a conflict with a previously installed hack or feature, but you will likely see different errors elsewhere if this is the case.

If you encounter other problems with a db_update.php file, feel free to post a support request in the appropriate forum.
_________________
Phantasy Star: Fringes of Algo

70+ Listings @ phpBBHacks.com