RE: [PHP] IF in mysql query results problem

Thanks for your reply... I thought about making that join however in this case, the coach data is only needed for the player info. Jason. -----Original Message----- From: Mehmet Kamil ERISEN [mailto:erisen@yahoo.com] Sent: January 7, 2002 1:41 AM To: jdulberg@sympatico.ca; php-general@lists.php.net Subject: Re: [PHP] IF in mysql query results problem 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 <jdulberg@nas.net> 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: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net Mehmet Erisen http://www.erisen.com ---------------------------------------------------------------------------- -- Do You Yahoo!? Send FREE video emails in Yahoo! Mail.