Prevent duplication of records in database

[Gen.Asura]

Hi i'm practising in creating an android application where a user can store records into an external database (my local server in myphpadmin). I can store records into the the database just fine but I wish to prevent the same information being entered twice. I wish for my system to check against the database if the record does exist, if it does an error should be pointed in the existence of the data.

Doing the error handling is no problem but the problem comes in when i use the PHP codes to check the server (im not very good with php) I have two php codes where it checks the records on the database and I was wondering what can I do in order for it to check data duplication. My friend helped me with the php files before he went on for vacation and I'm at a dead end at the moment for php.

The first php file is to add the record into the database.

<?php

$response = array();


if (isset($_POST['name']) && isset($_POST['datetime']) && isset($_POST['log'])) {

    $name = $_POST['name'];
    $datetime = $_POST['datetime'];
    $log = $_POST['log'];

    require_once __DIR__ . '/db_connect.php';

    $db = new DB_Connect();

    $result = mysql_query("INSERT INTO bookingdb(name, datetime, log) VALUES('$name', '$datetime', '$log')");

    if ($result) {
        $response["success"] = 1;
        $response["message"] = "Booking created.";
        echo json_encode($response);
    } else {
        $response["success"] = 0;
        $response["message"] = "Error.";
        echo json_encode($response);
    }
} else {
    $response["success"] = 0;
    $response["message"] = "Information is missing";
    echo json_encode($response);
}
?>

The second php file is to modify the records in the database.

<?php

$response = array();


if (isset($_POST['bid']) && isset($_POST['name']) && isset($_POST['datetime']) && isset($_POST['log'])) {

    $bid = $_POST['bid'];
    $name = $_POST['name'];
    $datetime = $_POST['datetime'];
    $log = $_POST['log'];

    require_once __DIR__ . '/db_connect.php';
    $db = new DB_Connect();

    $result = mysql_query("UPDATE bookingdb SET name = '$name', datetime = '$datetime', log = '$log' WHERE bid = $bid");

    if ($result) {
        $response["success"] = 1;
        $response["message"] = "Booking created.";
        echo json_encode($response);
    } else {

    }
} else {
    $response["success"] = 0;
    $response["message"] = "Information is missing";
    echo json_encode($response);
}
?>

[Gen.Asura]

I was thinking of including a code something like.

public function alreadyExists($timedate) {
        $result = mysql_query("SELECT timedate from bookingdb WHERE timedate = '$timedate'");
        $no_of_rows = mysql_num_rows($result);
        if ($no_of_rows > 0) {
            // booking existed 
            return true;
        } else {
            // bookings does not existed
            return false;
        }
    }

Something similar to check whether the booking already exists, the variable to check whether it exist or not is the "timedate" variable. I've included the code in the above php files but It prevented me from entering the data in the database and in my android GUI no error dialog was shown.

[rubberman]

Create the mysql table with a unique primary key. Duplicates will be automatically rejected with a 1062 SQL error code. I use that technique to handle duplicate rejects when storing performance log records in our data centers. It is more efficient than searching for a duplicate, and avoids the overhead of handling the return data, even if you use a key to find it.

[Gen.Asura]

Thanks for your post and i've put some thoughts into what you
ve said, I've created a table with mySQL looking like this

CREATE TABLE bookingdb(
bid int(11) primary key auto_increment,
name varchar(100) not null,
datetime varchar(100) not null,
log text,
created_at timestamp default now(),
updated_at timestamp
);

[Gen.Asura]

To create a unique key my table would look something like this

CREATE TABLE bookingdb(
bid int(11) primary key auto_increment,
unique_id varchar(23) not null unique,
name varchar(100) not null,
datetime varchar(100) not null,
log text,
created_at timestamp default now(),
updated_at timestamp
);

But how would I use the unique key in the PHP script?

[Gen.Asura]

bump

[rubberman]

You are using what is called a surrogate key. An auto-increment column is going to be unique, period. You need a primary key that is part of the data in the row. It can be one or more columns, but DO NOT use an auto-increment column for that! So, define an index on the auto-increment (unique_id) column for fast lookups. An example of this would be for things like an invoice number. However, that is not adequate for your purposes.