PHP PDO mySQL query create table if not exist
18,468
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";
if($fsdbh->exec($sql) !== false) { return 1; }
}
Author by
RIK
Updated on July 29, 2022Comments
-
RIK almost 2 years
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