[RESOLVED] auto_increment field not working

[cmp241]

Hello everyone,

I am trying to create a database and table at once which works fine when I omit the auto increment field, but when I try to create the database, table, and auto increment field, it doesn't work. And its pissing me off because its so simple. so please help

<?php
$con = mysql_connect("localhost","root","password");

mysql_query("CREATE DATABASE $db",$con);

mysql_select_db($db, $con);

$sql = "CREATE TABLE $tname('id' INT(6) NOT NULL AUTO_INCREMENT, 'field2' varchar(25) NOT NULL, UNIQUE('id'))";

mysql_query($sql,$con);

 mysql_close($con);

?>

[aspekt9]

Try this:

CREATE TABLE $tname (
  id mediumint(9) NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE(name)
) TYPE=MyISAM;

[rmbarnes82]

Previous poster correct, the reason for this is that an auto increment column must be the primary key.

[cmp241]

thanks aspekt9 and rmbarnes82, I added the primary key and unique and it worked