up vote 0 down vote favorite

Using PHP PDO query to execute a mySQL query. The query is made up of a multitude of information inputted from foreach(); so I have echo'd out the sql query. The problem lies here but I cannot see it.

This is the output of $sql

CREATE TABLE IF NOT EXISTS `page` (
  `page_ID` INT AUTO_INCREMENT NOT NULL,
  `url` varchar(200) NOT NULL,
  `title` varchar(200),
  `subtitle` TEXT,
  `content` TEXT,
  `parent` varchar(10),
  `privacy` varchar(1),
  `status` varchar(1),
  `creation` varchar(30)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

FYI the query is executed like this:

function createdbtable($table,$fields){
    global $fsdbh;
    $sql = "CREATE TABLE IF NOT EXISTS `$table` (";
    foreach($fields as $field => $type){ $sql.= "`$field` $type,"; }
    $sql = rtrim($sql,',');
    $sql .= ") CHARACTER SET utf8 COLLATE utf8_general_ci"; return $sql;
    if($fsdbh->exec($sql) !== false) { return 1; }
}

This is the error:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

share|improve this question
57% accept rate
4  
And... what is the problem? – Daniel M Aug 23 at 15:37
In the SQL query as stated – Robin Knight Aug 23 at 15:38
What is the error? – chantheman Aug 23 at 15:38
Do you get errors...? Have you checked? – deceze Aug 23 at 15:38
And... what is the problem? – Matt Aug 23 at 15:38
show 3 more comments
feedback

1 Answer

up vote 4 down vote accepted

You forgot the primary key:

CREATE TABLE IF NOT EXISTS `page` (
`page_ID` INT AUTO_INCREMENT NOT NULL,
`url` varchar(200) NOT NULL,
`title` varchar(200),
`subtitle` TEXT,
`content` TEXT,
`parent` varchar(10),
`privacy` varchar(1),
`status` varchar(1),
`creation` varchar(30),
PRIMARY KEY (`page_ID`)) 
CHARACTER SET utf8 COLLATE utf8_general_ci

Error was quite explicit:

Schema Creation Failed: Incorrect table definition; there can be only one auto column and it must be defined as a key

You must specify the auto increment key as key.

edit:

And for the PHP code, I will go to something like that:

function createdbtable($table,$fields)
{
    global $fsdbh;

    $sql = "CREATE TABLE IF NOT EXISTS `$table` (";
    $pk  = '';

    foreach($fields as $field => $type)
    {
      $sql.= "`$field` $type,";

      if (preg_match('/AUTO_INCREMENT/i', $type))
      {
        $pk = $field;
      }
    }

    $sql = rtrim($sql,',') . ' PRIMARY KEY (`'.$pk.'`)';

    $sql .= ") CHARACTER SET utf8 COLLATE utf8_general_ci"; return $sql;
    if($fsdbh->exec($sql) !== false) { return 1; }
}
share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.