PHP / MYSQL - 在插入新记录之前检查它是否有重复记录

Question

Suppose I have a table called "device" as below:

device_id(field)
123asf15fas
456g4fd45ww
7861fassd45

I would like to use the code below to insert new record:

...
$q = "INSERT INTO $database.$table `device_id` VALUES $device_id";
$result = mysql_query($q);
...

I don't want to insert a record that is already exist in the DB table, so how can I check whether it have duplicated record before inserting new record?

Should I revise the MYSQL statement or PHP code?

Thanks

UPDATE

<?php
    // YOUR MYSQL DATABASE CONNECTION
    $hostname = 'localhost';
    $username = 'root';
    $password = '';

    $database = 'device';
    $table = 'device_id';
    $db_link = mysql_connect($hostname, $username, $password);
    mysql_select_db( $database ) or die('ConnectToMySQL: Could not select database: ' . $database );
    //$result = ini_set ( 'mysql.connect_timeout' , '60' );

    $device_id = $_GET["device_id"];
    $q = "REPLACE INTO $database.$table (`device_id`) VALUES ($device_id)";
    $result = mysql_query($q);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }
?>

Answer 1

Since I understood well your question you have two ways to go, it depends how you would like to do the task.

First way -> A simple query can returns a boolean result in the device_id (Exists or not) from your database table. If yes then do not INSERT or REPLACE (if you wish).

Second Way -> You can edit the structure of your table and certify that the field device_id is a UNIQUE field.

[EDITED]

Explaining the First Way

Query your table as follow:

SELECT * FROM `your_table` WHERE `device_id`='123asf15fas'

then if you got results, then you have already that data stored in your table, then the results is 1 otherwise it is 0

In raw php it looks like:

$result = mysql_query("SELECT * FROM `your_table` WHERE `device_id`='123asf15fas'");
if (!$result)
{
   // your code INSERT
   $result = mysql_query("INSERT INTO $database.$table `device_id` VALUES $device_id");
}

Explaining the Second Way

If your table is not yet populated you can create an index for your table, for example go to your SQL command line or DBMS and do the follow command to your table:

ALTER TABLE `your_table` ADD UNIQUE (`device_id`)

Warning: If it is already populated and there are some equal data on that field, then the index will not be created.

With the index, when someone try to insert the same ID, will get with an error message, something like this:

#1062 - Duplicate entry '1' for key 'PRIMARY'