[SOLVED] IF ... THEN ... ELSE ... END IF

[The Little Guy]

How do I get this if statement to work? I have never used mysql if's before...

 

$query = "SELECT * FROM friends_friends,users 
WHERE
friends_friends.friend = '1'
AND
(friends_friends.myFriend = '$id' OR friends_friends.theirFriend = '$id')
AND
((friends_friends.theirFriend = users.id AND friends_friends.theirFriend != '$id') OR (friends_friends.myFriend = users.id AND friends_friends.myFriend !='$id'))
IF users.displayName != NULL THEN users.displayName = '$letter' 
ELSE users.fname = '$letter' 
END IF
ORDER BY addDate";

 

Error Message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF users.displayName != NULL THEN users.displayName = 'j' ELSE users.fname ' at line 8

[fenway]

I don't know what makes you think you can use an IF statement a) there and b) with the wrong syntax.  IF( expr, if true, if false).

[The Little Guy]

I can not find the correct syntax, and I don't know where they are supposed to go.... any help?

[Wildbug]

I think that form of IF is only used in stored procedures.  Try the IF(case,then,else) version.

 

(Are you trying to set those values?)

[The Little Guy]

I am trying to get the value, but if the value for displayName is null, I want to use the column fname instead, because that field is never null

 

IF users.displayName <> NULL THEN
users.displayName = '$letter%' 
ELSE
users.fname = '$letter%' 
END IF

[Wildbug]

Oh, so you want to run a conditional comparison.

 

...WHERE IFNULL(users.displayName LIKE '$letter%', users.fname LIKE '$letter%')...

 

...should work.  IFNULL(exp1,exp2) returns exp1 if it's not NULL, exp2 otherwise.

[The Little Guy]

IT WORKS!!!! but.... I heard that IFNULL shouldn't be used on joins.... because it takes way too long

 

From comments section:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Don't use IFNULL for comparisons (especially not for Joins)

 

(example:

select aa from a left join b ON IFNULL(a.col,1)=IFNULL(b.col,1)

)

 

It's terrible slow (ran for days on two tables with approx 250k rows).

 

Use <=> (NULL-safe comparison) instead. It did the same job in less than 15 minutes!!

[Wildbug]

Interesting, but that's not what you're doing.  <=> is a NULL-safe equality operator; IFNULL() is a conditional statement/function.

[The Little Guy]

that only seemed to work for displayName, and not fname....

 

There is someone who doesn't have a display name (displayName), and their first name (fname) starts with an M, so I put:

http://publicsize.com/friends/viewFriends?friendID=1&action=letter&lett=m

 

an no results returned, but if they do have a display name, i can get results for them???

[The Little Guy]

OK... got it, had to make displayName a null field...