SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    select and update -- turn into one query

    Hi. Could someone tell me how to merge these two queries into one?
    It's in PHP format to give you an idea what I'm doing.

    PHP Code:
               $query "
                   SELECT id FROM `trackReferrers` WHERE `cookie_id`='
    $cookieID
                   ORDER BY `timestamp` DESC LIMIT 1"
    ;
       
               
    $result mysql_query($query$this->_dbConn);
       
               
    $row mysql_fetch_assoc($result);
       
               
    $query2 "
                   UPDATE `trackReferrers` SET `bool_converted`=1 WHERE `id`='
    $row[id]'"
    --ed

  2. #2
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    Edmonton, the upper part of AB.
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why are you bothering with selecting 'id' anyways? Couldn't you just update the table by matching 'cookie_id' instead of using 'id' as an intermediary step?

    Kinda like:
    PHP Code:
     $query "UPDATE `trackReferrers` SET `bool_converted`=1 WHERE `cookie_id`='$cookieID'"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    Edmonton, the upper part of AB.
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh wait..
    I'm assuming that 'cookie_id' is unique. If not, then yours is a very good question.
    You can use LIMIT on an update to only update one row, but to be honest, I've never understood how it chooses which row to update. I would just be lazy and leave it as two queries. Who knows, you might need to do something else with that particular row later, so you might just select additional columns along with 'id' in the first query and dump them into an array so you don't have to access the DB later on.
    But other than that, sorry, I have no idea.

  4. #4
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I want to update only the most recent record(by `timestamp`). Since `id` is unique and `cookie_id` isn't, I get the unique id of the record that I want by using a select statement with that WHERE and LIMIT clause.

    I think I could do something like this:

    UPDATE `trackReferrers` SET `bool_converted`=1 WHERE `cookie_id`='$cookieID' [AND `timestamp` field is greatest]

    The stuff in the brackets is just English, because I'm not sure what the sql would be.
    I'm thinking I could use the MAX() function to narrow it down?

    --ed


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
  •