I want to implement a new system for referal/promotions. my new system where when a user register a specific source is assig to him. A source can be anything from referal (clients domain) and a promotion code (eg: 50% off). This source also have a date (y-m-d). Each source id is unique and multiple users can have the same source id
For example:
source id : 1 = www.domain1.com / 2011-11-20 / (empty referal code)
source id : 2 = www.domain1.com / 2011-11-20 / referalcode1
source id : 3 = www.domain2.com / 2011-11-20 / referalcode1
source id : 4 = www.domain2.com / 2011-11-20 / referalcode2
The referal code can be mixed from client1 to client 2
How can i make sure when someone register (its free registration and we have over 1000 per hours now) we dont have duplicate records and risk the mysql to generate errors?
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
You use the "DISTINCT" keyword to only copy over distinct organizations. The above output shows that only 1287 records are inserted into the "organizations" table. On the other hand, if you use "INSERT INTO SELECT", without the "DISTINCT" keyword, duplicate records would be copied over as well.
To get unique or distinct values of a column in MySQL Table, use the following SQL Query. SELECT DISTINCT(column_name) FROM your_table_name; You can select distinct values for one or more columns. The column names has to be separated with comma.
You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to stop duplicate records. Let us take an example – The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.
From mysql page:
The main idea is to insert records using the
ignore
statement. If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. Then retrieve the last ID usingmysql_insert_id
If the record does not exists, it will insert it and the last ID will be returned.
Now, if there's not record returned, you can do your select. This means a record already exists, so simply use it.
Example:
// make sure $id is safe (preventing sql injections)
$sql = "INSERT IGNORE INTO yourtable SET `field1` = 'value1' ... ";
mysql_query($sql);
if(mysql_affected_rows() == 1) { // no record found and then the inserts worked
$id = mysql_insert_id(); // id from the primary key
// add to cache
}
else {
// you can also cache them when they were inserted (faster to run than a select statement)
$id = retreiveFromCache($field1, $field2 /* etc...*/);
if(!$id) { // no record found in cache
// now the select can be done using the fields received
// make sure your use the right index otherwise it can be slow
$sql = "SELECT id FROM promotions_referrals WHERE `field1` = 'value1' ... "; //
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$id = $row['id'];
// add to cache
}
}
At this point you will have $id
assigned and you are sure there's no duplicate/mysql errors.
Note: if a domain belongs to one client, use the client id instead of using the domain. This way you will use INT as index which is faster than VARCHAR. Same idea for referral code.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With