[SOLVED] a new auto incriment column in mysql, and another mydql question

[blueman378]

hi guys i have a table that looks like this:

CREATE TABLE `games` (
  `gSwfFile` varchar(100) NOT NULL,
  `gName` varchar(100) NOT NULL,
  `gThumb` varchar(100) NOT NULL,
  `gDescription` varchar(100) NOT NULL,
  `gplays` smallint(5) unsigned NOT NULL default '0',
  `RId` smallint(5) unsigned NOT NULL default '0',
  `category` varchar(100) NOT NULL,
  `keyword` varchar(100) NOT NULL,
  UNIQUE KEY `gName` (`gName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

now what i want is to add in a column called RId and have it auto incriment,

however when i try i get..

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

 

any ideas what i have to do?

[PHP_PhREEEk]

I think if you remove the unique key for `gName`, then it will accept your new field. You don't need to remove gName, just the unique designation. Give it a try...

 

PhREEEk

[blueman378]

thanks, what does the unique key acctually do?

[redarrow]

update that colum with +1

 

 

  `RId` int(5) unsigned NOT NULL default '0',

 

$sql=" update what_ever set Rid=Rid+1 where what_ever='$what_ever'";

 

 

[PHP_PhREEEk]

thanks, what does the unique key acctually do?

 

It requires that every record in the table has a unique value in the gName field.

 

PhREEEk

[blueman378]

hmm i deleted that and tried to set that column as the unique key and get:

SQL query:

ALTER TABLE `games` ADD UNIQUE (
`RId`
)

MySQL said: Documentation
#1062 - Duplicate entry '0' for key 1 

???

[blueman378]

ah got it,

i already had some that info that was created before the column so it meant that they were defined as zero... sily me

[Barand]

Create it with

CREATE TABLE `games` (
  `gSwfFile` varchar(100) NOT NULL,
  `gName` varchar(100) NOT NULL,
  `gThumb` varchar(100) NOT NULL,
  `gDescription` varchar(100) NOT NULL,
  `gplays` smallint(5) unsigned NOT NULL default '0',
  `RId` smallint(5) unsigned NOT NULL auto_increment primary key,
  `category` varchar(100) NOT NULL,
  `keyword` varchar(100) NOT NULL,
  UNIQUE KEY `gName` (`gName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

or mod existing table with

ALTER TABLE `games` MODIFY COLUMN `RId` INTEGER NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(`RId`);

[blueman378]

well i have already created this topic so i might as well ask here,

i am trying out a script and i now get this error:

Fatal error: Function name must be a string in C:\wamp\www\admin\gameinstaller.php on line 173

but line 173 is

$sql("INSERT INTO ".games."

[PHP_PhREEEk]

I hadn't noticed that RId was already in the table...

 

I solved it by creating a Primary Key for RId, then selecting auto-increment for it. Worked fine.

 

Glad you got it solved anyways...

 

PhREEEk

[PHP_PhREEEk]

well i have already created this topic so i might as well ask here,

i am trying out a script and i now get this error:

Fatal error: Function name must be a string in C:\wamp\www\admin\gameinstaller.php on line 173

but line 173 is

$sql("INSERT INTO ".games."

 

You need

 

$sql = "INSERT INTO ".games." // etc

 

PhREEEk

[redarrow]

i didnt even see that there was no primary_key and auto_increment my example was using the update statement as a secound increment dam dam dam....

 

what would we ever do without barand (happy xmas barand and happy new year)

[blueman378]

thanks guys all is solved merry xmas and a ahppy new year to you all,

 

haha i gotta hurry with this site its a bday prezzie 4 someone in feb lol