A php function to insert or update mysql table on true/false

Question

Im trying to write a function to check whether a user exists in a table, if so, update the row, if not, insert it.

Here is my function:

     function UserExists($screenname){
      $mysql = mysql_fetch_array ( mysql_query("SELECT * FROM `users` WHERE `screenname` = '$screenname' "));
       if($mysql){
        return TRUE;
       }else{
        return FALSE;
       }
}

And im executing this function using the following:

if(UserExists($user)){
mysql_query("UPDATE `users` SET  `token` =  '$token' ,  `secret` =  '$secret'  WHERE `screenname` = '$user' ");
}else{
mysql_query("INSERT INTO `users` (`screenname`, `token`, `secret`) VALUES ('$user', '$token', '$secret')");
}

Its not doing anything, not throwing any errors, but also not updating the table.

Answer 1

mysql_query('..query..') or die(mysql_error());

Answer 2

Call mysql_error after the calls to see what error you are getting. Also another good debugging technique is to execute it on the server using a mysql client such as mysqlfront or myphpadmin

Answer 3

You'd want to take a look at the INSERT ... ON DUPLICATE KEY UPDATE ... MySQL query syntax to make this work with just one query.

INSERT INTO users (screenname, token, secret) VALUES ('screenname', 'token', 'secret')
ON DUPLICATE KEY UPDATE token = 'token', secret = 'secret'

screenname should be unique (or a primary key) which you probably don't have at the moment. I suggest to add an ID column in your database table and use that to refer to database rows.

Answer 4

Your best solution is ON DUPLICATE KEY as JoostK stated

Answer 5

I'd simply suggest to use REPLACE INTO SET col = val in this case, all you need to do is define a unique index on the table.

The Replace Command will have a look at the unique indexes, if a row already exists it will update the existing, if not it will insert a new one.

Bobby