Hi Jason,
I am not too familiar with IF statements in SQL, but one thing may be the reason you 
see the incorrect results:

You have three tables, player, coach and co_bookmarks, but you only join player and 
coach.  Shouldn't you join coach with co_bookmarks.

I hope this helps.

erisen


SELECT p.id AS player_id, p.name, p.hs, c.id AS coach_id, co.cid AS college,
IF(co.pid=31,1,0) AS is_bookmarked
FROM player p, coach c, co_bookmarks co
WHERE p.id=31 AND p.hs=c.id AND co.cid=2


  Jason Dulberg <[EMAIL PROTECTED]> wrote: I am working on a program that allows users 
to "bookmark" a particular item
that they view. The bookmarks are stored in the database.

I would like to use an IF statement in the mysql query so when the user logs
in and views the items, the option to bookmark the record has been removed
if they have already bookmarked it.

Here's an example query:

SELECT p.id AS player_id, p.name, p.hs, c.id AS coach_id, co.cid AS college,
IF(co.pid=31,1,0) AS is_bookmarked
FROM player p, coach c, co_bookmarks co
WHERE p.id=31 AND p.hs=c.id AND co.cid=2

When I use the above query in a search, it comes up with 3 results, 1 of
which is correct. If I GROUP BY p.id, there is only 1 result but its
incorrect. The search should only come up with 1 correct result in this
case.

+-----------+-------------+----+----------+---------+---------------+
| player_id | name | hs | coach_id | college | is_bookmarked |
+-----------+-------------+----+----------+---------+---------------+
| 31 | Paul Mantle | 24 | 24 | 2 | 0 |
| 31 | Paul Mantle | 24 | 24 | 2 | 1 |
| 31 | Paul Mantle | 24 | 24 | 2 | 0 |
+-----------+-------------+----+----------+---------+---------------+

+-----------+-------------+----+----------+---------+---------------+
| player_id | name | hs | coach_id | college | is_bookmarked |
+-----------+-------------+----+----------+---------+---------------+
| 31 | Paul Mantle | 24 | 24 | 2 | 0 |
+-----------+-------------+----+----------+---------+---------------+

The row with is_bookmarked = 1 is the only correct one. I am assuming that
for some reason, its displaying 3 results because there are 3 id's
associated with cid=2. But if I group them by the id, it takes the wrong
row:

If anyone has any suggestions as to what I'm doing wrong, please let me know
as I'm tapped out on ideas.

Thanks a TON!!

__________________
Jason Dulberg
Extreme MTB
http://extreme.nas.net


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]


Mehmet Erisen
http://www.erisen.com


---------------------------------
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail.

Reply via email to