WEB PHP/MySql - Do something if UPDATE query didnt update anything

Discussion in 'OT Technology' started by o2, Jun 1, 2008.

  1. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    11
    Location:
    Toronto
    Not sure if Im just missing the obvious here, but this is my dilemma.

    Im running an UPDATE query, similar to this:

    If domain123.com is in the table, this simply adds 1 to its hits_in counter..... simple. But if the domain is not there, I wanna run an INSERT query that would add that domain to the DB.

    Currently, Im running a SELECT query to see if the domain is there or not, and then decide if I should run UPDATE or INSERT. Im trying to get rid of the SELECT query, since 99.9% of the time, I would need to run the UPDATE query, since the domain would already be in the DB. How would I make it run the INSERT query in the remaining 0.01% of the cases when the UPDATE query doesn't update any rows.

    [​IMG]
     
  2. 96

    96 Merry Christmas :D

    Joined:
    Oct 30, 2001
    Messages:
    16,073
    Likes Received:
    0
    Location:
    San Diego
    I think mysql_affected_rows() will help you :)

    http://us.php.net/manual/en/function.mysql-affected-rows.php

    Code:
    mysql_query("UPDATE table SET hits_in=hits_in+1 WHERE url = 'domain123.com' 			 		");
    if (mysql_affected_rows()==0) {
      mysql_query("INSERT INTO table (blah,blah) VALUES(blah,domain123.com)");
    }
    
     
  3. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    11
    Location:
    Toronto
    Thank you kind sir!

    I didnt realize this function existed. I tried mysql_num_rows but that didnt work out.
     
  4. 96

    96 Merry Christmas :D

    Joined:
    Oct 30, 2001
    Messages:
    16,073
    Likes Received:
    0
    Location:
    San Diego
    Yeah, that'll just return you the number of rows in the query
     
  5. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    11
    Location:
    Toronto
    I've also stumbled upon this: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

    Mysql solution should be more speedy than php, I think.

    I've also been experimenting with the ON DUPLICATE KEY, and Im trying to run a query that updates 2 tables on failure of the INSERT query, along the lines of this:

    It works fine if I dont also query the users table, and just do websites, but in this case, I gotta query both.
     

Share This Page