0

I have a follow system that allow user to follow one another. I'm using php pdo so it doesn't support multiple line query instead I need a long query. Currently I'm using the code below

$query = $db->query("SELECT id FROM follow WHERE user1 = 'kim' AND user2 = 'tim' ");
if($query->rowCount() > 0 ){
    $db->query("DELETE * FROM follow WHERE user1 = 'kim' AND user2 = 'tim' ");
}else{
    $db->query("INSERT INTO follow (user1,user2) VALUES ('kim','tim') ");
}

So here what I want is a long query rather than multiple query statement. Additional with the long query I need get the data that indicate wether the record is insert or delete.(Maybe this question is duplicated but this is the part that wasn't duplicated) How can I acheive it?

user3815506
  • 91
  • 11
  • What do you consider a `long query`? – u_mulder Dec 21 '14 at 18:34
  • @u_mulder mean that just one line of query rather the thing I currently doing like using EXISTS, NOT EXISTS etc – user3815506 Dec 21 '14 at 18:45
  • without get the result from first query its not possible to put `if else` condition ? – Debug Diva Dec 21 '14 at 18:57
  • I think what you're looking for is to insert and delete sub-query in one query based on a condition. You might want to take a look at this : [EXISTS](http://www.techonthenet.com/mysql/exists.php) – Dimitri Dec 21 '14 at 20:07
  • Can't your UI tell whether or not the user is following or unfollowing? Seems not very user friendly to have the user click on something without knowing whether it does one thing or the exact opposite. – sn00k4h Dec 23 '14 at 03:52

1 Answers1

0

Here is the "long query" 

IF EXISTS (SELECT id FROM follow WHERE user1 = 'kim' AND user2 = 'tim' )
BEGIN
    DELETE * FROM follow WHERE user1 = 'kim' AND user2 = 'tim'
END
ELSE
BEGIN
    INSERT INTO follow (user1,user2) VALUES ('kim','tim')
END

If it gets too "long" put it in a stored procedure.

meda
  • 45,103
  • 14
  • 92
  • 122