sql statement with condition

[mbarandao]

Hello,

I'm trying to write a sql statement that would update a record only if a particular row column is empty; else, insert a new record.

Is this doable?

Currently, I have this update statement:

//query for additional cars 1
$mysql_query=("UPDATE additional_cars SET  clientID='$edit_clientID', registeredstate='$edit_registeredstate', 
dhtmlgoodies_category='$dhtmlgoodies_categoryupdate', dhtmlgoodies_subcategory='$dhtmlgoodies_subcategoryupdate',caryear='$edit_caryear', servicearea='$lastservice',
currentmileage='$edit_currentmileage', todays_date='$date2', time='$time2', servicedescription='$inputbox2', service='$edit_servicearea_str', servicedesc =CONCAT('$inputbox', servicedesc) WHERE id='$edit_sel_id'");
$result=mysql_query($mysql_query);
if(!$result){die("Error: ".mysql_error());
$id=LAST_INSERT_ID();

How can I modify the above to achieve the outcome mentioned.

[urtrivedi]

I assume that you have defined your primary key or unique key for you table. If yes then you can use following single query. search more "on duplicate key mysql"

insert into tablename(col1,col2, col3) values('1','2','3') on duplicate key update set col2='22', col3='33'

[mbarandao]

I assume that you have defined your primary key or unique key for you table. If yes then you can use following single query. search more "on duplicate key mysql"

insert into tablename(col1,col2, col3) values('1','2','3') on duplicate key update set col2='22', col3='33'

Thanks for the reply! following the example above, I've written the following:

$mysql_query = ("INSERT INTO additional_cars (clientID, registeredstate, dhtmlgoodies_category, dhtmlgoodies_subcategory,caryear, servicearea,currentmileage,todays_date, time, servicedescription, service, servicedesc) VALUES ('$edit_clientID','$edit_registeredstate', 
'$dhtmlgoodies_categoryupdate', '$dhtmlgoodies_subcategoryupdate','$edit_caryear','$lastservice','$edit_currentmileage','$date2','$time2','$inputbox2','$edit_servicearea_str', CONCAT('$inputbox', servicedesc)) WHERE id='$edit_sel_id' on duplicate key UPDATE SET 
clientID='$edit_clientID', registeredstate='$edit_registeredstate', dhtmlgoodies_category='$dhtmlgoodies_categoryupdate', dhtmlgoodies_subcategory='$dhtmlgoodies_subcategoryupdate',caryear='$edit_caryear', servicearea='$lastservice',
currentmileage='$edit_currentmileage', todays_date='$date2', time='$time2', servicedescription='$inputbox2', service='$edit_servicearea_str', servicedesc=CONCAT('$inputbox', servicedesc)");
$result=mysql_query($mysql_query);
if(!$result){die("Error: ".mysql_error());

I'm afraid something is incorrect, I'm getting the following error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id='4' on duplicate key UPDATE SET clientID='2345TF', registeredstate='FL' at line 2

[urtrivedi]

You can not write where condition in insert statement, remove following part from query. Query will itself find id when u try to insert duplicate row

WHERE id='$edit_sel_id'

If your table have auto_increment then this query will not work. It will only work when you pass all columns in insert statement. If you have another indexes and unique columns, then this query will respond properly.

[mbarandao]

You can not write where condition in insert statement, remove following part from query. Query will itself find id when u try to insert duplicate row

WHERE id='$edit_sel_id'

If your table have auto_increment then this query will not work. It will only work when you pass all columns in insert statement. If you have another indexes and unique columns, then this query will respond properly.

Thanks for the reply; I'm attempting suggestion. will advise shortly...

[mbarandao]

I was not able to resolve the issue with the suggestion. So I went with a completely different strategy. So, all is well!

Thank you for the help!
Mossa