homepage Welcome to WebmasterWorld Guest from 54.235.20.17
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library : Charter : Moderators: physics & whoisgregg

Databases Forum

    
MySQL to write row if doesn't already exist
And preform automatic calculations
Jeremy_H




msg:1580538		 4:11 pm on Jun 1, 2006 (gmt 0)

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




msg:1580539		 4:16 pm on Jun 1, 2006 (gmt 0)

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




msg:1580540		 6:01 pm on Jun 1, 2006 (gmt 0)

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




msg:1580541		 6:05 pm on Jun 1, 2006 (gmt 0)

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




msg:1580542		 9:45 pm on Jun 1, 2006 (gmt 0)

Thank you Nutter, that works great!
Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved