SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IF in mysql query

    I am working on a program that allows users to "bookmark" a particular record 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 records, the option to bookmark the record has been removed if they have already bookmarked it.

    Here is the query that I have so far but the IF does not work correctly. The IF results are incorrect.

    SELECT p.*, c.hs_name, IF(co.pid=$pid,1,0) AS is_bookmarked FROM player p, coach c, co_bookmarks co WHERE p.id=$pid AND p.hs=c.id AND co.cid=$current_user

    $pid is the player id that is passed from a link.
    $current_user is established at login.

    Basically, I just need it to look at the co_bookmarks table to determine whether or not the $current_user has bookmarked the player and display accordingly.

    Here is my basic table info if it helps:

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

    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)
    )

    Any suggestions are greatly appreciated
    Thanks!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  2. #2
    SitePoint Zealot Nate's Avatar
    Join Date
    Sep 2001
    Location
    BC, Canada
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use something like this:
    PHP Code:
    <?
    $query 
    .= "SELECT * ";

    if (
    $something == "1") {  $query . = "WHERE id = '1'"; }
    else {  
    $query .= "WHERE something_else = '1'";  }

    $query .= "FROM table";
    ?>
    Hope that helps... I don't think that you can do if's inside of queries...
    NATHAN WRIGHT
    PHP Developer, Simple Station

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hope that helps... I don't think that you can do if's inside of queries...
    Looks like you need to reread the manual.

    jdulberg,

    I think you may have the WHERE part of your query wrong, cause your implementation of the IF() function in MySQL appears normal.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Freddy...

    I had a look at the WHERE clause and changed co.cid=$current_user to co.pid=$pid and it came up with the correct results however if the player isn't bookmarked, the results no longer show.

    Am I joining the tables together in the wrong places?

    The other parts of the query works fine, its just the bookmark IF/where section that is messing up.

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

  5. #5
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FreddyDoesPHP,

    How does the IF() statement work? The explaination at mysql.com was more than a bit confusing.

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works basically the same as ternary operator in PHP. The first arg is what to test for, then the second is what to assign if its true, and the third for false. So lets build on jdulberg's example for a moment.

    Code:
    $sql = "SELECT *, IF((CURDATE() - INTERVAL 1 day) < somedatefield, 1, 0) as isNew from tablename";
    So what that would do is say if the field in this row is newer than one day assign the result field isNew a 1 or else assign it a 0.

    Not a super in depth example, but hope it helps a little.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So would this work:

    $sql = "SELECT *, IF(somedatefield BETWEEN 2001-01-01 AND 2001-12-31, somedatefield, 0) as isNew from tablename";

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In theory yes. But only if your field was a date field and not a datetime field. Also the dates would need to be in single quotes because of the dashes.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it possible to add a WHERE clause to my query if the IF statement result is true? Or would that be redundant?
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  10. #10
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very Cool

    Thanks You FreddyDoesPHP

  11. #11
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jdulberg
    Is it possible to add a WHERE clause to my query if the IF statement result is true? Or would that be redundant?
    do you mean have something different in the WHERE clause depending on a condition? no, that won't work.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  12. #12
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you mean have something different in the WHERE clause depending on a condition? no, that won't work.
    ok, just thought I might ask. I'll try anything.

    I've messed around with all kinds of WHERE combinations and I still can't get it. Either it will only display bookmarked players or none at all. I'm sure I'm missing something stupid too.

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

  13. #13
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm just trying to wrap my head around this little problem again.

    When I use the following 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.

    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;

    +-----------+-------------+----+----------+---------+---------------+
    | 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 |
    +-----------+-------------+----+----------+---------+---------------+


    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:

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

    If anyone has any suggestion 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


Bookmarks

Posting Permissions

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