...

View Full Version : If no UPDATE....INSERT



StupidRalph
09-09-2005, 04:07 PM
What is the best way to insert a row if it is not able to be updateable?


//Build SQL statement to update an appointment for a specified date.
$updateAppQuery = "UPDATE appointments
SET appTitle ='$title', appTime = '$time', appDetails = '$details'
WHERE appDate = '$date'";
//Execute built SQL statement.
$updateAppResult = mysql_query($updateAppQuery) or die ("Query 'updateAppQuery' failed. <b> Error Code </b> " . mysql_errno() . ' ' . mysql_error());

//Record not found in the database so lets insert it.

if (mysql_affected_rows($updateAppResult) == 0) {

echo "okay insert";

$insertAppQuery = "INSERT INTO appointments (appTitle, appDate, appTime, appDetails)
VALUES ('$title' , '$date' , '$time' , '$details')" ;

$insertAppResult = mysql_query($insertAppQuery) or die ("Query 'insertAppQuery' failed. <b> Error Code </b> " . mysql_errno() . ' ' . mysql_error());
.

SpirtOfGrandeur
09-09-2005, 06:07 PM
LoL... you might not hear anything back on this... then again you might hear alot. I have a function that I have written in ASP to handle this situation. It is based on the assumption that the row you are updating is keyed on the where statment. If it is then you can use RegExp's to get out the info you need and to make it into a valid insert statment. I could show you my ASP code if you think it will help, but I doubt it will help any more then the writeup I just wrote.

GL :)

nikkiH
09-09-2005, 07:38 PM
Personally, I prefer more explicit logic. What's one more sql statement?

pseudocode:

select count(primarykeycol) from tablename where key = keytoupdate
if count == 1 then update
if count == 0 then insert
if count > 1 then there's a real issue here (or it isn't a primary key) :p

missing-score
09-09-2005, 08:03 PM
Or you could use mysql_affected_rows() to find out how many rows were affected in the update query, but the chances are I would use a SELECT statement first, then update/insert depending on the result (Like nikkiH says)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum