CODEWITHSUNDEEP
phpmysql
Tiffany
Tiffany

Reputation: 237

Ignore one specific row from MySQL output

I have written an application that contains a users MySQL table. The following code finds users from the table that have a coolness rating within 20% of the current user's coolness rating.

$query = "SELECT USERNAME FROM USERS WHERE RATING BETWEEN ".$coolnessRating." * 0.8 AND ".$coolnessRating." * 1.2";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

    echo $row['USERNAME'];
}

My problem is this: if I have a user and their coolness rating is 20 for example, they themselves are returned in the result set as obviously their own rating is within 20% of their rating if that makes sense.

Is there a way to ignore this specific row but print out the rest of the result set?

Upvotes: 1

Views: 567

Answers (6)

Teaqu
Teaqu

Reputation: 3263

First you need to get the information from the current user such as their id or username.

Then you can do 

$query = "SELECT USERNAME FROM USERS WHERE RATING BETWEEN ".$coolnessRating." * 0.8 AND ".$coolnessRating." * 1.2 AND id != " . $id;

or 

$query = "SELECT USERNAME FROM USERS WHERE RATING BETWEEN ".$coolnessRating." * 0.8 AND ".$coolnessRating." * 1.2 AND id != " . $username;

where $username and $id are the current user's id and username

Upvotes: 1

Brendan Long
Brendan Long

Reputation: 54242

Just filter it out with another part to your WHERE clause:

SELECT USERNAME
FROM USERS
WHERE RATING BETWEEN $coolnessRating * 0.8 AND $coolnessRating * 1.2
AND USER_ID != $current_user_id

(Obviously the last line depends on what the column name is and how you're storing the current user ID)

Upvotes: 1

mb2015
mb2015

Reputation: 168

Why not having your condition < 20% I can't tell though because you didn't mention the expected passed values

Upvotes: 0

WebChemist
WebChemist

Reputation: 4411

Forgoing the usual "Use PDO! Your Code is vulnerable!" spiel, you would include another AND "not this user" condition

$query = "SELECT USERNAME FROM USERS WHERE RATING BETWEEN ".$coolnessRating." * 0.8 AND ".$coolnessRating." * 1.2 AND USERNAME != ".$user."";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

    echo $row['USERNAME'];
}

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Are you just wanting to add not equal to your WHERE criteria:

SELECT USERNAME 
FROM USERS 
WHERE RATING <> ".$coolnessRating." 
    AND RATING BETWEEN ".$coolnessRating." * 0.8 AND ".$coolnessRating." * 1.2"

Upvotes: 1

Visionary Software Solutions
Visionary Software Solutions

Reputation: 3848

"If I have a user" means you have their username or id, right? Add the condition to your where clause with AND.

Upvotes: 2

Related Questions