PHP & MYSQL Is this MySQL Syntax correct for UPDATE/INSERT IF EXISTS

Question

My plan is to update/insert existing/new records into a database from an external, i have the information set to go into the database, but cannot check if it exists already.

my first attempted at doing database stuff

    $link = resdb::connect();
    $q = "IF ( EXISTS ( SELECT * FROM property AS this WHERE this.name = $propertyname ) )"
         ."begin"
            // UPDATE PROPERTY IF IT EXISTS
            ."UPDATE property (name, propertylocation, propertyrating, propertytype)" 
            ."SET ($propertyname, $locationid, $ratingid, $typeid)"          
            ."WHERE name = $propertyname"
         ."end"
         ."ELSE"
         ."begin"
            ."INSERT INTO property (name, propertylocation, propertyrating, propertytype)" 
            ."VALUES ($propertyname, $locationid, $ratingid, $typeid)"
         ."end";
    $r = mysqli_query($link, $q);
    if($r > 0){
        return true;
    }

A unique id is made on completion of each row.

The Scrum Meister · Accepted Answer · 2011-02-10 05:29:46Z

2

First create a unique index on the name column:

ALTER TABLE property ADD UNIQUE KEY(name)

Now you can use the MySql INSERT ... ON DUPLICATE KEY UPDATE...

INSERT property (name, propertylocation, propertyrating, propertytype)
VALUES ($propertyname, $locationid, $ratingid, $typeid)
ON DUPLICATE KEY UPDATE 
    propertylocation = VALUES(propertylocation),
    propertyrating = VALUES(propertyrating),
    propertytype = VALUES(propertytype)

Docs: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Don't forget to sanitize database inputs.

answered Feb 10, 2011 at 5:29

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JS1986 Over a year ago

great easier than TSQL method. upvote and answered. thank you

Collectives™ on Stack Overflow

PHP & MYSQL Is this MySQL Syntax correct for UPDATE/INSERT IF EXISTS

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related