Re: Creating tables with PHP script, getting MySQL errors
Posted by:
Don Hanson
Date: December 14, 2005 02:37AM
I think I found the answer. It seems to be in the way I'm declaring VARCHAR columns and INDEX columns.
The VARCHAR needs the (length) attribute and INDEX has to be declared as a seperate entry.
Another problem I came across was that the AUTO_INCREMENT column also HAS to be declared as KEY. though I'd rather have 'talent_name' being the PRIMARY KEY for the table, though I guess INDEXing it works just as well.
changing the query to this works properly.
### talents table
$talqry = "CREATE TABLE IF NOT EXISTS talents (";
$talqry .= "id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL KEY, ";
$talqry .= "talent_name VARCHAR(255), ";
$talqry .= "talent_step VARCHAR(255), ";
$talqry .= "action BOOL, ";
$talqry .= "karma BOOL, ";
$talqry .= "skill BOOL, ";
$talqry .= "skill_only BOOL, ";
$talqry .= "skill_cat VARCHAR(255), ";
$talqry .= "strain TINYINT UNSIGNED, ";
$talqry .= "source VARCHAR(255), ";
$talqry .= "description TEXT,";
$talqry .= "INDEX(talent_name))";
$talres = mysql_query($talqry);
if (!$talres) {
$message = 'Invalid query: ' . mysql_error() . "<BR><BR>\n\n";
$message .= 'Whole query: ' . $talqry . "<BR><BR>\n\n";
die($message);
}
Talents and Skills Table created successfully.
CREATE TABLE IF NOT EXISTS talents (id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL KEY, talent_name VARCHAR(255), talent_step VARCHAR(255), action BOOL, karma BOOL, skill BOOL, skill_only BOOL, skill_cat VARCHAR(255), strain TINYINT UNSIGNED, source VARCHAR(255), description TEXT, INDEX(talent_name))