mysql 有无此记录,MySQL：如果此IP没有任何记录，则插入

I use:

INSERT INTO `rating` (`name`, `user`, `rating`, `section`, `ip`)

VALUES ('$name', '{$_SESSION['user']}', '$rate', '$section',

'{$_SERVER['REMOTE_ADDR']}');";

I would like to add an if condition in the IF statement so that.

IF SELECT ip from rating

where ip={$_SERVER['REMOTE_ADDR']} AND section=$section AND name=$name

then update ELSE INSERT new row

is it doable or I better code it in php ?

thank you very much

P.s: I know how to do it with php, I want to learn it with MySQL.

Also i require that all 3 name,section,ip matchs not only ip

解决方案

To expand on Eric's excellent answer.

To add a unique constraint on each of the columns ip, name, section run the following code on the database

ALTER TABLE `test`.`rating`

ADD UNIQUE INDEX `name`(`name`),

ADD UNIQUE INDEX `section`(`section`),

ADD UNIQUE INDEX `ip`(`ip`);

To run a unique constraint on the combination of columns ip+name+section do:

ALTER TABLE `test`.`rating`

ADD UNIQUE INDEX `name_section_ip`(`name`, `section`, `ip`);

The last thing is probably what you want.

One last thing

I'm not 100% sure, but this usage of {$_SERVER['REMOTE_ADDR']} in the query does not look SQL-injection safe.

Consider changing it into:

$remote_adr = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);

$session = mysql_real_escape_string($_SESSION['user']);

$query = "INSERT INTO `rating` (`name`, `user`, `rating`, `section`, `ip`)

VALUES ('$name', '$session', '$rate', '$section','$remote_adr')";

Finally putting a ";" in a mysql_query() like so mysql_query("select * from x;"); does not work mysql_query() will only ever execute one query and will reject your ;.