Check if MySQL Row exists

Question

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%");
}

I believe you want to flip your if/else blocks, since when if(mysql_num_rows($result)) == true, the id exists in users, so you want to do the update, else id doesn't exist in user so you need to do an insert? — Sean
– Sean, Commented Oct 15, 2013 at 22:15
If the ID is not in the table I want MySQL to insert it, if it is in the table it should update instead — FrostyGamer150
– FrostyGamer150, Commented Oct 15, 2013 at 22:18

phil-lavin · Accepted Answer · 2013-10-15 22:14:44Z

2

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.

answered Oct 15, 2013 at 22:14

phil-lavin

1,2278 silver badges19 bronze badges

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

4 Comments

FrostyGamer150 Over a year ago

I do not understand this

Fluffeh Over a year ago

@phil-lavin The code I posted will insert the row, or delete and insert a new one if one was there already. (See the comments below my answer for more detail)

user1864610 Over a year ago

REPLACE INTO will delete an existing row. The OP is updating only one column, so a REPLCE will lose all the data on other columns.

phil-lavin Over a year ago

Fair point - deleted my earlier comment. Sounds like the INSERT with ON DUPLICATE KEY UPDATE is what's required here.

Fluffeh · Accepted Answer · 2013-10-15 22:15:44Z

0

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

answered Oct 15, 2013 at 22:15

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

3 Comments

user1864610 Over a year ago

REPLACE will not update an existing row - it will delete it. Any existing data in the row will be lost.

Fluffeh Over a year ago

@MikeW Yeah, though it does look from the code in the question like that won't be an issue has he does seem to have all the data for the row there?

user1864610 Over a year ago

For an INSERT, yes. For an update only one column is changed. Your suggestion will lose any data in other columns.

Collectives™ on Stack Overflow

Check if MySQL Row exists

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related