SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best method for updating/inserting a record

    Hello all, I have a database table and I'm trying to:
    1. insert a record if it does not exist
    2. 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:
    PHP Code:
        function FindRecord($filename)
        {
        
    $sql 'SELECT COUNT(*) AS rows FROM media_captions WHERE filename =:filename';
        
    $stmt $this->dbh->prepare($sql);
        
    $stmt->bindParam(':filename'$filenamePDO::PARAM_STR);
        
    $stmt->execute();
        
    $row $stmt->fetch();
        return 
    $row['rows'];
        } 
    My add record function

    PHP Code:
         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
    PHP Code:
        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,

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hypernetware View Post
    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?
    yes, there is
    Code:
    INSERT
      INTO tablename
    ...
    ON DUPLICATE KEY
    UPDATE ...
    one statement, one operation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •