Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    11

    Unanswered: IF in mysql query results problem

    I have been trying to figure out how to fix this problem for about a month now and have posted on some other websites but haven't had any luck. So if you have any suggestions as to how I might fix the problem, please let me know as it becoming critical. Thanks

    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

    (the user bookmarks the player page which uses some coach info. that's why p.hs=c.id)

    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 in advance!!

    Jason

  2. #2
    Join Date
    Jan 2002
    Location
    Carthage, MO
    Posts
    12
    ok, you'd be right conculding that the reason you get multiple results would be because of multiple records it matched in the co table when doing a join on all the tables.

    from what I gather, you really only care about the records in the co table that would actualy be bookmarked, and since those are the only ones you want to evalute how bout not even including the other records.

    The idea here, is that you include all the records that would pertain to items that would be bookmarked, the items that don't match that end up getting NULL fields, which would end up evaluating to 0 in your if statement. But because we exclude all records that wouldn't be bookmarked, you don't get multiple records of unbookmarked records. Now this doesn't take into effect if you get multiple records that are bookmarked from the co table. I wasn't sure if you had to worry about that or not.

    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
    INNER JOIN coach c
    ON p.hs = c.id
    LEFT OUTER JOIN co_bookmarks co
    ON (c.id = co.cid AND (co.pid != 31,1,0))
    WHERE p.id=31
    AND co.cid=2

    I wasn't quite sure what your relationship between the tables was for the co table, so I just asumed that it was c.id = co.cid

    The LEFT OUTER join takes all the records from the left side of the join, and tries to match them up with records from the right. If it can't find a match for a record at the right it leaves NULL fields for the values that would have been represented from the table on the right.

    I'm kind basign this on pure theory, never tried it before... but I thought I'd throw it your way to see if it got any ideas rolling, or maybe it'll help someone else with some ideas on how to better get your results.

    I hope this helps some.

    Matt Johnson

  3. #3
    Join Date
    Jan 2002
    Posts
    11
    Thank you for your great ideas!!

    I should clarify though that I want to display records that are AND aren't bookmarked. Basically I want to take the is_bookmarked variable into the display section and put something like "player already bookmarked" OR "bookmark this player" depending if is_bookmarked is 1 or 0. I want to do this so there are no duplicate bookmarks for a particular player associated with the user logged in.

    If it helps at all, here is the slimmed down, basic structure of the 3 tables involved.

    CREATE TABLE co_bookmarks (
    id tinyint(4) NOT NULL auto_increment,
    cid tinyint(4) NOT NULL default '0',
    pid tinyint(4) NOT NULL default '0',
    KEY id (id)
    )

    cid identifies the person currently logged in.
    pid identifies the player that is bookmarked

    CREATE TABLE coach (
    id int(11) NOT NULL auto_increment,
    PRIMARY KEY (id)
    )

    CREATE TABLE player (
    id int(11) NOT NULL auto_increment,
    hs int(11) NOT NULL default '0',
    name varchar(75) NOT NULL default '',
    PRIMARY KEY (id),
    KEY hs (hs)
    )

    player display takes certain info from the coach table so player.hs links to coach.id

    Again, thank you for your time on this matter

    Jason.

  4. #4
    Join Date
    Jan 2002
    Location
    Carthage, MO
    Posts
    12
    Yeah, that helpd a lot. I created a little test db to play with it, and see why it wasn't working.... the hands on approch helped me understand what was really going on.

    I worked on the query I gave you a little and found out why it wouldn't return records. It was due to having co.cid=2 in the where clause, because if my query didn't find any matches in the co table, then it obviously wasn't going to match co.cid=2, so I put that part up in the join and I think this might work for you.

    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
    INNER JOIN coach c ON p.hs = c.id
    LEFT OUTER JOIN co_bookmarks co ON (p.id = co.pid AND co.cid=2)
    WHERE p.id=31


    What this does, is if it gets a values that is bookmarked it returns all the fields, but if there isn't a match in the co table it leaves the fields from the co table as NULL, because of the LEFT OUTER JOIN. Since the co.pid field would be NULL your IF will evaluate as false, setting the is_bookmarked field to 0, because there wasn't a p.id that matched a co.pid.

    One thing is that the co.cid field comes up NULL, because no records were found, but I have a solution for that too. Since your result is always going to be 2, or whatever you put in the co.cid=? then you could just set that field to that value to begin with, instead of actualy trying to pull it out.


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


    The reason I did the tables joins so different, is so that I had more control on what was joined where, and why.



    In Perl I'd probably write the query like this.

    $college_id = 2;
    $player_selected = 31;

    $sth = $dbh->prepare( q^
    SELECT p.id AS player_id,
    p.name,
    p.hs,
    c.id AS coach_id,
    ? AS college,
    IF(co.pid=?,1,0) AS is_bookmarked
    FROM player p
    INNER JOIN coach c ON p.hs = c.id
    LEFT OUTER JOIN co_bookmarks co ON (p.id = co.pid AND co.cid=?)
    WHERE p.id=?
    ^);

    $sth->execute( $college_id, $player_selected, $college_id, $player_selected);

    The variables in the execute() function are used to replace each ? in the query.



    Let me know what you think.

    Matt Johnson
    CPUweb

  5. #5
    Join Date
    Jan 2002
    Posts
    11
    Matt, you are absolutely AMAZING!!!!!

    I plugged in your second query and it worked like a charm! Your explanation of how it works is great and I certainly learned from it.

    Thank you again for all of your help and time with this query

    Jason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •