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.

Answer 1

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.

Answer 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.