MySQL to write row if doesn't already exist

And preform automatic calculations

[Jeremy_H]

Hello,

I'm trying to create a MySQL command where it will look at the value alpha of table A to see if the ip address has already been written.

If it has not been written I would like to create a new row with that ip and values alpha and beta.

If it has already been written I would like to increase value alpha by one and perform the calculation alpha/beta and set gamma to equal that.

I know how to read and write statements, but I have no clue how to conditional conditional statement ("if ip already exists") or perform calculations inside the table.

Is there an efficient way in MySQL to do this?

Thanks

What I have below is a simple write command to log the ip address. It does not check to see if it already exists, and currently creates duplicate records.

$link=mysql_connect("site","db","pass");
mysql_select_db("db");
mysql_query("INSERT INTO a (ip, alpha, beta, gamma) VALUES ('$REMOTE_ADDR', 'B', 'C', 'D')");
mysql_close($link);

[figment88]

If the IP has a unique index, you can try the insert.

If you already have a record with that index, the insert will fail and return an error code saying something like duplicate entry. You can then test to see if you got this error and if so do the update query.

I'm certainly no database guru, but I have done this type of logic successfully in a number of places.

[Jeremy_H]

Thanks for the feedback,

Might somebody know if using an OR or DIE statement be along the right lines? Would it try the first, then kick into the second if the first doesn't work without outputting an error and killing the script?

mysql_query("UPDATE a SET alpha=alpha+1 WHERE ip='$ip'") or mysql_query("INSERT INTO a (ip, alpha) VALUES ('$ip', '1')");

Thanks

[Nutter]

I'm pretty sure the OR command wouldn't work since that query won't throw an error, it just won't update anything.

What about this? (Not 100% that it will work, but it seems like it should)

$return = mysql_query("UPDATE table SET field=field+1 WHERE ip='ip.address'");
if (mysql_affected_rows() == 0)
{
// No records updated, so add it
$return = mysql_query("INSERT INTO table (field, ip) VALUES (1, 'ip.address')");
}

[Jeremy_H]

Thank you Nutter, that works great!