[thelist] PHP: Insert or Replace (upon duplicate IP) - Alter and mySQL syntax

[rudy]

> MySQL said: alter command denied to user: 'atdt1991 at localhost'
> for table 'ptmratings'

bummer, scott, looks like you're stuck with drop/recreate

your datatypes are too large

   CREATE TABLE testing
     ( id SMALLINT UNSIGNED  not null AUTO_INCREMENT
     , ip TEXT  not null
     , host TEXT  not null
     , email TEXT  not null
     , comment TEXT  not null
     , rating TINYINT UNSIGNED  not null
     , user_agent TEXT  not null
     , PRIMARY KEY (id)
     , INDEX (id)
     , UNIQUE (id, ip)) comment = 'test table'

ip and email should probably be varchar(255) unless you seriously expect
email address longer than that

host and user_agent too, probably

as for UNIQUE (id, ip), that doesn't require a unique constraint, because
since id is auto_increment, every value is perforce going to be different,
eh?

the only reason to INDEX(id) is if you are joining this table to another on
this table's id

PRIMARY KEY(id) used to be superfluous syntax (doesn't really do anything
[hi matt]), unless they've added relational integrity with a recent release
of mysql -- leave it in anyway

so all you need is UNIQUE(ip)

however, that may still cause grief, as it accepts only one entry from
possibly hundreds of people on a shared connection, and does not prevent
someone from submitting subsequent entries after getting a new (temporary)
ip assigned, e.g. by a dialup connection


hope that helps

rudy