Update a row if exists, if not create a new one MySQL

Question

I am inserting a name, number, and company into a DB.

My table is simply:

id(primary key)
name
slideView
company

I need to update this information if a name passed to it exists, if not create a new row with this data. I have looked at REPLACE INTO but I dont think that will work for me... as I dont touch the ID at all.

My code is:

insertData($name,$count,$company);

function insertData($name, $count, $company) {

    #Try/Catch statement to connect to DB, and insert data
    try {
        #DB username/password   
        $usernameDB = '****';
        $passwordDB = '****';

        #Create new PHP Database Object with the address, username, and password as parameters
        $pdo = new PDO('mysql:host=localhost;dbname=*****', $usernameDB, $passwordDB);

        #Set pdo attributes to handle errors 
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        #assign the sth variable (sth means statement handle) to insert the data
        $sth = $pdo->prepare("REPLACE INTO ***** SET name = ?, slideView = ?, company = ?");

        #Execute the insert
        $sth->execute(array($name,$count,$company));

    #Error if can't connect or insert
    } catch(PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }#/try
}

I'm new to SQL and havnt found a good way to do this yet.

Fluffeh · Accepted Answer · 2012-09-19 23:25:12Z

5

You might be better of using the insert ... on duplicate update syntax for this, although it will mean passing a few extra params but it can quash certain problems in the replace into syntax that seem to keep cropping up.

REPLACE INTO ***** SET name = ?, slideView = ?, company = ?

Could be written as:

insert into yourTableName (name, slideView, company)
    values (:name, :slideView, :company)
    on duplicate key update set
        slideView=:$slideView2, company=:company2

and then the execute is done like this:

$sth->execute(array(':name' => $name, ':slideView' => $count,
    ':company' => $company, ':slideView2' => $count, ':company2' => $company));

The format above uses named paramaters (they are ever so much easier to read/debug) and will insert a new row into the database - or if the unique/primary key column name already has the value, then update the row with the remainder of the information.

You do have to use paramaters twice here (even though slideView and company will contain the same information) as no parameter can be used twice in a query.

edited Sep 19, 2012 at 23:25

answered Sep 19, 2012 at 23:16

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

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

4 Comments

mdance Over a year ago

This seems correct. Although it is throwing this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET slideView=NULL, company=NULL' at line 1

mdance Over a year ago

Also, I am running MySQL version 5.1.61

mdance Over a year ago

I removed SET and the query ran, although it didn't update anything - just kept adding new rows each time.

Fluffeh Over a year ago

@mdance You do have name in your table set as a unique row yes?

cincodenada · Accepted Answer · 2012-09-19 23:09:15Z

2

REPLACE INTO should work just fine - it also checks for columns with UNIQUE constraints. So you would just need to mark your name column as unique so that REPLACE INTO will identify it as duplicate.

I haven't tried this particular use case, but the documentation seems to allow it.

edited Sep 19, 2012 at 23:09

answered Sep 19, 2012 at 23:01

cincodenada

3,13929 silver badges36 bronze badges

1 Comment

mdance Over a year ago

I chose to go back to what I originally had. It worked once I marked my name column unique. Thanks!

Collectives™ on Stack Overflow

Update a row if exists, if not create a new one MySQL

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related