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.

Joost · Accepted Answer · 2009-10-30 12:51:35Z

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.

answered Oct 30, 2009 at 12:51

Joost

10.4k4 gold badges59 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben · Accepted Answer · 2009-10-30 12:54:36Z

0

Your best solution is ON DUPLICATE KEY as JoostK stated

answered Oct 30, 2009 at 12:54

Ben

62.7k117 gold badges330 silver badges505 bronze badges

Comments

Francisco · Accepted Answer · 2017-08-04 19:27:39Z

0

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 phpMyAdmin.

edited Aug 4, 2017 at 19:27

Francisco

11.6k6 gold badges37 silver badges47 bronze badges

answered Oct 30, 2009 at 12:45

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

Comments

Francisco · Accepted Answer · 2017-08-04 19:28:00Z

0

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.

edited Aug 4, 2017 at 19:28

Francisco

11.6k6 gold badges37 silver badges47 bronze badges

answered Oct 30, 2009 at 13:03

Bobby

11.6k5 gold badges48 silver badges70 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related