Check if a row with given values exists

Question

I have a query for users to create a follower / following relationship within my application:

INSERT INTO following(IdUser,followingID) VALUES('%d','%d')", $id, $followingId

What would be the query to check if a relationship between two users already exists?

Or, how do I check if a relationship between two users doesn't exist?

Typically, I return the results in an iOS application like so:

    if (!$result['error']) {
    // if no error occured, print out the JSON data of the 
    // fetched photo data
    print json_encode($result);
} else {
    //there was an error, print out to the iPhone app
    errorJson('Follow is broken');
}

Any help would be appreciated.

EresDev · Accepted Answer · 2013-08-07T00:32:53.547

You have to make a transaction for this.

First perform select.

SELECT * FROM following where idUser = $id AND followingID = $followingId

Now if above query selects one or more rows then there already exists a relation and if no row (zero rows) then there is no relation. For both situations take action accordingly.

Make sure to use the query according to PHP and SQL syntax. If ID's are char or varchar then in query these should be surrounded by single or double commas.

Like this: if ID's are varchar or char then

$query = "SELECT * FROM following where idUser = '".$id."' AND followingID = '". $followingId."'";

and if ID's are int or any other digit values (according to column of table)

$query = "SELECT * FROM following where idUser = ".$id." AND followingID = ". $followingId."";

Also make sure the column names and table name I've used are correct.

Now let's first execute the query

$result = mysql_query($query); // this method works but is not preferred. better if insert query here instead of variable $query

Now let's check how many rows it has selected and take appropriate action.

if(mysql_num_rows($result) > 0){
    //do something here or show error because relation already exists
}
else{
   //relation already do not exists. so you can insert the record here
}

NOTE: the functions mysql_query and mysql_num_rows are deprecated as of PHP 5.5.0. Because I coded in php long ago, so I remember these right now. Alternate you can find on php website. But these functions still work. http://php.net/manual/en/function.mysql-query.php and http://php.net/manual/en/function.mysql-num-rows.php

Getting an error when trying that. Could it be printing the error because the relationship exists? — , Aug 06 '13 at 21:10
Well this is for an iOS application interacting with a PHP API. See my code edits. — , Aug 07 '13 at 00:41

score 1 · Answer 2 · answered Aug 06 '13 at 20:54

1

You could select the count to see if a record exists already

SELECT count(*) FROM following WHERE idUser = <id> AND followingID = <followingId>

If the count is greater than zero, then a relationship exists.

answered Aug 06 '13 at 20:54

Andy Jones

6,001
4
29
45

webcoder · Answer 3 · 2013-08-06T20:59:50.100

1

If you define a unique index for IdUser and followingId you could use REPLACE instead of INSERT to update it instead of inserting a new.

HTH (Hope that helps)

edited Aug 06 '13 at 20:59

answered Aug 06 '13 at 20:54

webcoder

56
4

score 1 · Answer 4 · answered Aug 06 '13 at 20:56

You could make an additional query to check if any of the two is "following" the other or viceversa:

SELECT 1 FROM following
WHERE
(IdUser = <id> AND followingID = <following id>)
OR
(IdUser = <following id> AND followingID = <id>)

If the query returns '1' someone of the two is already following the other.

score 0 · Answer 5 · answered Aug 06 '13 at 20:56

I would use the num_rows function to return if the relationship already exists. I would recommend using the mysqli version of this function since the mysql_* commands are slowly becoming deprecated, however here is the link for it regardless.

In order to create a query that will check if they already exist you will want to use a SELECT statement.

SELECT * FROM following WHERE IdUser = '$id' AND followingID = '$following'

If you get a 1 you will know that this relationship does indeed occur, if you get a 0 though the relationship is false.

Check if a row with given values exists

5 Answers5

Linked