Hello all, I have a database table and I’m trying to:
- insert a record if it does not exist
- update the record if it does exist
My method to do this was to create 3 php functions.
A function find the record,
A function to add a record,
A function to update the record,
Here is my function to find the record:
function FindRecord($filename)
{
$sql = 'SELECT COUNT(*) AS rows FROM media_captions WHERE filename =:filename';
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(':filename', $filename, PDO::PARAM_STR);
$stmt->execute();
$row = $stmt->fetch();
return $row['rows'];
}
My add record function
function AddRecord($filename, $caption = '', $line_drawing = true)
{
$sql = 'INSERT INTO media_captions (id, filename, caption, line_drawing) VALUES (:id, :filename, :caption, :line_drawing)';
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->bindParam(':filename', $filename);
$stmt->bindParam(':caption', $caption);
$stmt->bindParam(':line_drawing', $line_drawing);
$stmt->execute();
}
My update record function
function UpdateRecord($filename)
{
if ($this->FindRecord($filename) == 1)
{
$sql = 'UPDATE media_captions SET line_drawing = 1 WHERE filename = :filename';
$stmt = $this->dbh->prepare($sql);
$stmt->bindParam(':filename', $filename);
$stmt->execute();
}
else {$this->AddRecord($filename);}
}
My UpdateRecord function is the only function call I really make. UpdateRecord checks for the record to see if it exists, if the record exists it updates the table… if the record does not exist it runs the AddRecord() function.
Although my method works, it is very tedious. Is there an SQL syntax that checks for a record and updates it, and if the record does not exist, it just adds the record? I suspect that having 3 php functions to do this work is highly inefficient.
Thank you in advance,