CODEWITHSUNDEEP
phpmysql
FrostyGamer150
FrostyGamer150

Reputation: 3

Check if MySQL Row exists

How can I check if the table row exists and if it doesn't make a row? So far I have this:

$id = $user->getID();
$username = $user->username;
$check = "SELECT id FROM users WHERE nickname = '".$username."'";
$result = mysql_query($check);

if(mysql_num_rows($result)){
$user->parent->mysql->query("INSERT INTO $ngtable (`id`, `namecolor`, `glowcolor`, `bubblecolor`, `bubbletext`, `bubbleglow`, `ringcolor`, `snowglow`) VALUES ('".$id."','0x','0x','0x','0x','0x','0x','0x')");
}else{
$user->parent->mysql->query("UPDATE $ngTable SET namecolor = '0x" . $arg . "' WHERE id = '" . $user->getID() . "'");
$user->sendPacket("%xt%sm%-1%0%$user->username, your name color is now: $arg%");
}

Upvotes: 0

Views: 184

Answers (2)

Fluffeh
Fluffeh

Reputation: 33502

Although you would need to set up a table constraint on the nickname to be unique, the best way might be to use the MySQL replace syntax which does what you want in one fell swoop.

replace INTO 
    $ngtable 
        (`id`, `namecolor`, `glowcolor`, `bubblecolor`, `bubbletext`, `bubbleglow`, `ringcolor`, `snowglow`) 
    VALUES 
        ('".$id."','0x','0x','0x','0x','0x','0x','0x')

From the docs:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE): Try to insert the new row into the table While the insertion fails because a duplicate-key error occurs for a primary key or unique index: Delete from the table the conflicting row that has the duplicate key value Try again to insert the new row into the table

Upvotes: 0

phil-lavin
phil-lavin

Reputation: 1217

MySQL INSERT supports ON DUPLICATE KEY UPDATE - that would be the most efficient way.

For example...

INSERT INTO table_name (id, foo, bar) VALUES (7, 'baz', 'bat') ON DUPLICATE KEY UPDATE foo='baz', bar='bat'

Naturally this relies on your table having a unique index (a primary key will do) which your insert will cause a conflict with and thus trigger the update.

Upvotes: 2

Related Questions