INSERT only once into MySQL or update if exists - Facebook App

Question

I've been trying to make a facebook game for some weeks now and I am stuck.. it probably will be a easy question to answer but it is my first time working with mysql and I didn't find something to help me in the internet.

What I want to make is a flash game where people upload their personal data like email, name, surname in the background after confirmation has been granted and been given a score where after achieving higher score the field updates if the existing value is lower.

Right about now I've managed to create this code

$dbhost     = 'bla bla';
$dbuser     = 'bla bla';
$dbpass     = 'bla bla';
$data       = 'bla bla';



$db = mysql_connect($dbhost, $dbuser, $dbpass);

if (mysql_errno() > 0) {
if (mysql_errno() == 1203) {
    die("DB error");
} else {
    die("DB error");
}
}

if ($_REQUEST['action'] == "add") {
mysql_select_db($data, $db);
$insert = "INSERT INTO FB (UID, first_name, last_name, email, link) 
VALUES (

    '".$_REQUEST['uid']."', 
    '".$_REQUEST['first_name']."', 
    '".$_REQUEST['last_name']."', 
    '".$_REQUEST['email']."', 
    '".$_REQUEST['link']."'

    )";

$res = mysql_query($insert, $db) or die ("Save Error");
mysql_close($db);
echo "Added to DB!";
}

It works just fine but it has a problem.. if i submit my field again another duplicated row appears in the table with the same information.

I want the user to submit only once.. therefor having a unique row and update only his score if he manages to get a higher score.

Any help would be appreciated :)

**Your code is vulnerable to SQL injection attack.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/q/332365/623041). — eggyal, Dec 19 '12 at 15:05
As stated in [the introduction](http://www.php.net/manual/en/intro.mysql.php) to the PHP manual chapter on the `mysql_*` functions: *This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the [mysqli](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. See also the [MySQL API Overview](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API.* — eggyal, Dec 19 '12 at 15:07

score 2 · Answer 1 · answered Dec 19 '12 at 15:03

2

Create an appropriate UNIQUE key and then use INSERT ... ON DUPLICATE KEY UPDATE:

ALTER TABLE FB ADD UNIQUE KEY (UID);

INSERT INTO FB
  (UID, first_name, last_name, email, link) 
VALUES
  (123, 'foo', 'bar', 'foo@bar.com', '')
ON DUPLICATE KEY UPDATE
  first_name = VALUES(first_name),
  last_name  = VALUES(last_name),
  email      = VALUES(email)
  link       = VALUE(link)
;

answered Dec 19 '12 at 15:03

eggyal

122,705
18
212
237

I tried it this way: ` $insert = "ALTER TABLE FB ADD UNIQUE KEY (UID); INSERT INTO FB_ca (UID, first_name, last_name, email, link) VALUES ( '".$_REQUEST['uid']."', '".$_REQUEST['first_name']."', '".$_REQUEST['last_name']."', '".$_REQUEST['email']."', '".$_REQUEST['link']."' ) ON DUPLICATE KEY UPDATE first_name = VALUES(first_name), last_name = VALUES(last_name), email = VALUES(email) link = VALUES(link) ";` But it returns a Save Error.. have i done something wrong? thanks for your help man :) – Anonymous Dec 19 '12 at 15:49
for some reason this ALTER TABLE FB ADD UNIQUE KEY (UID); returns the error – Anonymous Dec 20 '12 at 12:02
@EneaDhiamandi: The `ALTER TABLE` command only needs to be run once, e.g. in phpMyAdmin. – eggyal Dec 20 '12 at 12:08

Howitzer · Answer 2 · 2012-12-19T15:24:31.187

The original poster mentioned something about scoring, but it's not present as a column in the table... This should check if the new score is less than the current score, if it is then the new score is assigned to the field in the update, otherwise the current score is used.

$insert = "INSERT INTO FB (UID, first_name, last_name, email, link, score) 
VALUES (
    '".$_REQUEST['uid']."', 
    '".$_REQUEST['first_name']."', 
    '".$_REQUEST['last_name']."', 
    '".$_REQUEST['email']."', 
    '".$_REQUEST['link']."', 
    '".$_REQUEST['score']."'
    )
ON DUPLICATE KEY UPDATE
score = IF(score < VALUES(score), VALUES(score), score)
";

Or just `score = GREATEST(score, VALUES(score))` – eggyal Dec 19 '12 at 15:28 — eggyal, Dec 19 '12 at 15:28

INSERT only once into MySQL or update if exists - Facebook App

2 Answers2