Verifying existing record in table

mlcheatham

I am trying to detect whether a record exists in a table. If so, I need to update it. If not, I new to create it. My code is below.

If the record already exists, everything works fine. I can't detect whether the record doesn't exist however.

Any help would be GREATLY appreciated.

MLC

$sql = "SELECT * from Scores_raw WHERE idAbstract='$idAbstract' AND reviewerID='$reviewerID'";
$result = mysql_query($sql);

if(!$result)
{
$sql = "insert into Scores_raw (idAbstract, reviewerID) values ('$idAbstract','$reviewerID')";
$result = mysql_query($sql);
}

$sqld = "UPDATE Scores_raw SET introduction='$introduction',methods='$methods',results='$results',conclusion='$conclusion',overall='$overall',comments='$comments',total='$total' WHERE idAbstract='$idAbstract' AND reviewerID='$reviewerID'";
$result = mysql_query($sqld);

laserlight

You might want to read this thread on a portable REPLACE INTO (that apparently does not exist).

jwitte

mlcheatham wrote:
I am trying to detect whether a record exists in a table. If so, I need to update it. If not, I new to create it. My code is below.

If the record already exists, everything works fine. I can't detect whether the record doesn't exist however.

Any help would be GREATLY appreciated.

MLC

$sql = "SELECT * from Scores_raw WHERE idAbstract='$idAbstract' AND reviewerID='$reviewerID'";
$result = mysql_query($sql);

if(!$result)
{
$sql = "insert into Scores_raw (idAbstract, reviewerID) values ('$idAbstract','$reviewerID')";
$result = mysql_query($sql);
}

$sqld = "UPDATE Scores_raw SET introduction='$introduction',methods='$methods',results='$results',conclusion='$conclusion',overall='$overall',comments='$comments',total='$total' WHERE idAbstract='$idAbstract' AND reviewerID='$reviewerID'";
$result = mysql_query($sqld);

Hey MLC! I am a total newbie to PHP but I had this EXACT same issue and {although I hate to upset my new friend Piranha who has helped me more then he knows} I found a very simple solution that worked for me. Here is what I would do:

$insertSQL = INSERT INTO Scores_raw (idAbstract, reviewerID) VALUES('$idAbstract','$reviewerID') ON DUPLICATE KEY UPDATE introduction='$introduction',methods='$methods',results='$results',conclusion='$conclusion',overall='$overall',comments='$comments',total='$total' ;
          mysql_select_db($db_database, $connection);
	$result= mysql_query($insertSQL, $connection);

What this does is it is saying insert (add a new record) if it does not exist but if those 2 id's already exist (they would be duplicated), then only update do not insert. ****Although - I must confess I have only used it with one variable - I have not tried this with 2 variables (idAbstract, reviewerID) but give it a try.

We are having a discussion on this topic right now in the thread http://www.phpbuilder.com/board/showthread.php?t=10356990 and of course not everyone agrees on the use, but it works very well in certain instances for me and is a really easy way to solve this problem IMO but I am still learning!