CODEWITHSUNDEEP
phpmysqlduplicates
user4507211
user4507211

Reputation:

prevent duplicate records in mysql table

Im creating a website for booking activities. I have 3 centres. The customer is cant book the same activity twice neither in a different centre. Im using a table in mysql which i store the infos provided by the costumers. Is there any way to filter or to check in my php code if a customer has already booked the same activity more than one time and echo an error msg? my table(and the info im asking) contains these columns: ID(Primary) FirstName LastName Email ContactNumber ClassName Week Intensity CentreName

$values = $_POST;
foreach ($values as &$value) {
$value = mysql_real_escape_string($value);
}

$sql1="INSERT INTO loan (loan_id)
VALUES ('$values[loan_id]')";

$result = mysql_query($sql1);
if (!$result) {
die('Invalid query: ' . mysql_error());
}

Upvotes: 0

Views: 2908

Answers (3)

BK435
BK435

Reputation: 3176

You need to create a unique (composite) index on the column(s) that you wish to be unique. You can disregard your PK when making your unique index. In your case your sql would look something like: 

Alter table yourtablename 
add unique index idx_unq(`LastName`, `FirstName`, `Email`, `ContactNumber` `ClassName`, `Week`, `Intensity`, `CentreName`);

Then do an INSERT IGNORE INTO instead of an INSERT INTO.

This post may also help you.

"INSERT INTO .. ON DUPLICATE KEY UPDATE" Only inserts new entries rather than replace?

In order to see if record already exist in table you must first "test" to see if that exact record exist in your table. This is to be done before the 'Insert IGNORE Into' in your logic. Using the variables your code would look something like this: 

$testcount = "Select count(`LastName`, `FirstName`, `Email`, `ContactNumber` `ClassName`, `Week`, `Intensity`, `CentreName`) 
      from yourtablename 
          where 
(LastName = '$LastName' AND FirstName= '$FirstName' AND Email= '$EMAIL' AND ContactNumber= '$ContactNumber' AND ClassName= '$ClassName' AND Week= '$Week' Intensity = '$Intensity' AND CentreName = '$CentreName' )";

This query will give you back (assuming there are no duplicates already in the table) a 0 or a 1 and store it in your $testcount variable. This can then be used to either determine based on the value to insert the record into the table or print a message to end user informing them that it already exist.

I am not sure how you want to structure the php code but the psuedocode would look something like:

If $testcount = 1 then do your insert.

else if $testcount = 0 then echo your message.

Upvotes: 0

Emc_tgn15
Emc_tgn15

Reputation: 151

Hope this helps you; If you do not have a unique id i believe this will suit you best on what you need; Note that this is not the full code; You need to add some to other information to fit in your question; 

// Checks if the value already exist on the database
$query = SELECT EXISTS(SELECT column_name FROM table_name WHERE 
condition   LIMIT 1)

// If condition is not met it will proceed with save
if (mysql_num_rows(!$query) > 0) {
    echo "Activity Booked";
} else { // If condition is met it will echo an error message
    echo "Unable to booked activity"; }

Upvotes: 0

Dimitri Acosta
Dimitri Acosta

Reputation: 1816

When you create the table add the unique attribute to the fields you want to prevent, something like this

CREATE TABLE Persons
(
P_Id INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255),
UNIQUE (P_Id)
)

If you already have created the table just edit it like this

ALTER TABLE Persons
ADD UNIQUE (P_Id)

Upvotes: 2

Related Questions