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:[EMAIL PROTECTED]]
  Sent: January 7, 2002 1:41 AM
  To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
  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 <[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