MySQL UPDATE: Little help!? :D

[greyhat]

I am using the following query to update a field in a mysql database:

$query="UPDATE fbs_members_info SET website = '$newsite' WHERE memberID ='$updateid'";

My question is - Since the UPDATE statement only works if there is already data there. How can i edit this statment to add new data if none already exists, but still update if it does exist. I did try and search a bit but didnt come up with much...

Thanks in advance.

 

[imkazu]

do you mean like adding a new field ?
assuming your member id is auto increment

insert into fbs_members_info (website) values ('$newsite');

 

[MadMaxNine]

I don't know if you can manage that in one statement. I think you need logic to query the db first (select memberID where memberID = '$memberid') and then get_num_rows, if 0 = run the insert statement, else run the update statement.

Edit: Actually if the id is a key you may be able to use :
INSERT INTO FOO
(ID, BAR)
VALUES(1,2),(3,4)
ON DUPLICATE KEY UPDATE BAR=VALUES(BAR)

(instead of values, try it with SET)

 

[greyhat]

Thx will report back.

 

[jz1297]

You can also use "REPLACE INTO". From the MySQL manual:

"REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMAY KEY or UNIQUE index, the old row is deleted before the new row is inserted."