CODEWITHSUNDEEP
phpmysql
Jason
Jason

Reputation: 2727

check if a row in mysql exists

I have a table in a mySQL database we'll call 'tbl' where the fields are:

id, userID, favorite, emailID

The id is auto incremental. The userID stores an integer. The favorite is either yes or no. The emailID stores an integer.

I am programing in PHP and would like to have one query used to query the database that checks if the userID and emailID combination exists. If it does then update the favorite field with a yes or no value that comes from a form that is passed into the query dynamically. If it does not exist then insert the combination into the database.

Therefore if I had:

<?php
$userID = 34;
$emailID = 395;
$favorite = "yes"; // could be yes or no.

I don't believe the query below is correct but gets the idea of what I am trying to do:

IF NOT EXISTS 
(SELECT userID, favorite, emailID 
FROM tbl 
WHERE ((userID = '$userID')and(emailID = '$emailID')) 
INSERT INTO tbl (userID, favorite, emailID) VALUES ('$userID', '$favorite', '$emailID')
ELSE UPDATE tbl
SET favorite = '$favorite'
WHERE ((userID = '$userID')and(emailID = '$emailID'))

(please know that I know that entering values into a query like this is a security risk, this is only to help explain my question.)

What is the best way to write the query? Can it be written with one query?

Upvotes: 0

Views: 874

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332571

It can't be written as a single query, because the primary key isn't the two columns you're looking for - it's the id column only.

If userid and emailid are unique pairs, they should be the primary key for the table -- not the id value. ORMs typically prefer that a primary key be single column for making query construction easier but the approach suffers on performance. Anything requiring more than one columns is generally referred to as a "composite" - primary key, unique constraint/index, foreign key, etc.

The ANSI means of doing this would be a MERGE statement, but MySQL doesn't support the syntax because it already has the ON DUPLICATE KEY UPDATE and REPLACE INTO to provide the same functionality.

Upvotes: 3

Jon Black
Jon Black

Reputation: 16559

you can do it really simply in a single call - but sprocs are bad aren't they ??

drop procedure if exists insert_update_tbl;
delimiter #

create procedure insert_update_tbl
(
in p_userID int unsigned,
in p_emailID int unsigned,
out p_id int unsigned
)
proc_main:begin

    set p_id  = 0;

    if exists (select 1 from tbl where userID = p_userID and emailID = p_emailID) then
        update tbl set favorite = 1 where userID = p_userID and emailID = p_emailID;
        leave proc_main;
    end if;

    insert into tbl (userID, emailID, favorite) values (p_userID, p_emailID, 0);

    set p_id  = last_insert_id();

end proc_main #

delimiter ;

Upvotes: 0

grahamparks
grahamparks

Reputation: 16296

I would drop the redundant id column and define the primary key as the combination of (userID, emailID), which means you have exactly one record for each different combination of the two fields, which seems to be what you want. Then you can use the REPLACE INTO command, which is equivalent to an INSERT but deletes any existing record if the primary key matches.

Upvotes: 2

Related Questions