CODEWITHSUNDEEP
phpmysql
Daniel
Daniel

Reputation: 55

INSERT ... ON DUPLICATE UPDATE

what I am trying todo is make a highscores page and each time it updates I want it to go into the same entry, so if Username is a duplicate I want it to update with the current information and then the information posted, after looking at some stuff on the internet I tried the code below and that gives me the error Column count doesn't match value count at row 1. Can anyone please tell me what I am doing wrong as I am very new to PHP + MySql.

mysql_query("INSERT INTO $table(Username, Time, Videos, Credits) VALUES ('$user', '$time', '$videos', '$credits',
    'ON DUPLICATE KEY UPDATE', Time='Time+$time', Videos='Videos+$videos', Credits='Credits+$credits')", $conn);

Upvotes: 1

Views: 2815

Answers (2)

Johan
Johan

Reputation: 76567

This code is a SQL-injection nightmare.

Make sure to check dynamic table names against a white-list like so:

$user = mysql_real_escape_string($_GET['user']);
$time = mysql_real_escape_string($_GET['time']);
$videos = mysql_real_escape_string($_GET['videos']);
$credits = mysql_real_escape_string($_GET['credits']);

$allowed_tables = array('table1', 'table2');  
$table = $_POST['table'];
$query = "";  //do nothing

if (in_array($table, $allowed_tables)) {   //<<-- check against whitelist.
  $query = "
    INSERT INTO
        $table(Username, Time, Videos, Credits)
    VALUES
        ('$user', '$time', '$videos', '$credits')
    ON DUPLICATE KEY UPDATE
        Time=Time+'$time',
        Videos=Videos+'$videos',
        Credits=Credits+'$credits'
    "
}    
mysql_query($query, $con);

Upvotes: 1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

The syntax is incorrect, you are passing the 'on duplicate update' and it's rules as values to the insert. The correct query would be

mysql_query("
    INSERT INTO
        $table(Username, Time, Videos, Credits)
    VALUES
        ('$user', '$time', '$videos', '$credits')
    ON DUPLICATE KEY UPDATE
        Time=Time+'$time',
        Videos=Videos+'$videos',
        Credits=Credits+'$credits'
    ",
    $conn
);

Upvotes: 6

Related Questions