0
I have the following problem: It is necessary to assign a coupon number to a certain person after a purchase and it has the following conditions: it must have 6 digits, the first digit being the series of the draw and the other 5 are the number itself. These numbers cannot be assigned in sequence (000000, 000001, 000002, ...). What I got was to select to check if the number already exists in my table as the example of select below:
SELECT CONCAT(1, LPAD(FLOOR(RAND() * 100000), 5, 0)) as num FROM oc_order WHERE NOT EXISTS (SELECT 1 FROM (SELECT number_coupon, id_campaign FROM oc_campaign_coupon) as cc WHERE cc.number_coupon = 'num' AND cc.id_campaign = '10') LIMIT 1
And I insert that value that was found with the example below:
INSERT INTO oc_campaign_coupon SET id_customer = '1', id_campaign = '10', id_order = '60', number_coupon = (SELECT CONCAT(1, LPAD(FLOOR(RAND() * 100000), 5, 0)) as num FROM oc_order WHERE NOT EXISTS (SELECT 1 FROM (SELECT number_coupon, id_campaign FROM oc_campaign_coupon) as cc WHERE cc.number_coupon = 'num' AND cc.id_campaign = '10') LIMIT 1), created_at = NOW();
The problem occurs that after 1000 more or less it starts to collide in the insertion, even making this select. Have another option to do this is to have a table with the possible numbers already entered and assign the campaigns and make a rand() to pick up some table row, but each campaign has at least 100,000 numbers and there can be multiple campaigns happening at the same time. Below my code I do the insert and creates the collisions:
$select = "SELECT CONCAT(" . $this->db->escape($serie) . ", LPAD(FLOOR(RAND() * 100000), 5, 0)) as num FROM " . DB_PREFIX . "order WHERE NOT EXISTS (SELECT 1 FROM (SELECT number_coupon, id_campaign FROM " . DB_PREFIX . "campaign_coupon) as cc WHERE cc.number_coupon = 'num' AND cc.id_campaign = '" . $this->db->escape($campaign['id']) . "') LIMIT 1";
$sql = "INSERT INTO " . DB_PREFIX . "campaign_coupon SET id_customer = '" . $this->db->escape($sellercoupon['customer_id']) . "', id_campaign = '" . $this->db->escape($campaign['id']) . "', id_order = '" . $this->db->escape($sellercoupon['order_id']) . "', number_coupon = (" . $select . "), created_at = NOW();";
$this->db->query($sql);
Would someone have a tip on how to do it another way or my case query is something wrong.
Note: I already changed NOT EXISTS for NOT IN and the same problem occurs.
I think you’ll need to lock the table if you want to ensure that you checked the one and inserted it before another process does so
– Ricardo Pontual
would it be necessary to add the LOOK TABLES before each Switch? Ex. LOCK TABLES oc_campaign_coupon READ; SELECT * FROM oc_campaign_coupon?
– Andre Lacomski
I made this LOCK TABLES for my query and also generates duplicate values: LOCK TABLES oc_order READ, oc_campaign_coupon as cc READ, oc_campaign_coupon as occ READ, oc_campaign_coupon WRITE; INSERT INTO oc_campaign_coupon SET id_customer = '1', id_campaign = '10', id_order = '60', 
number_coupon = (SELECT CONCAT(1, LPAD(FLOOR(RAND() * 100000), 5, 0)) as num 
FROM oc_order WHERE 'num' NOT IN 
(SELECT number_coupon FROM (SELECT number_coupon, id_campaign FROM oc_campaign_coupon as occ) as cc WHERE cc.id_campaign = '10') LIMIT 1), created_at = NOW(); UNLOCK TABLES;
– Andre Lacomski
look, it’s not enough to lock and generate a random and check if it exists, you need so for example in a Function, to check if it exists inside a loop, if it exists generates another and so on... it will slow down as the table fills, but it is the way. if the performance gets bad over time, you can think of a more intelligent algorithm, but you need to generate the value and check, if it already exists, generate again
– Ricardo Pontual
I would make a table of type id, sequence , the sequence would be from 00001 to 99999 already shuffled without repetition , the application , would take the smallest id would use the sequence and delete the id would be quick.
– Motta
I thought I’d do it this way, but there could be multiple raffle campaigns rolling around, so there would have to be several tables with that sequence.
– Andre Lacomski
a table (idcampanha,id,seq) but the solution of generating and testing the tb collision works , use Oracle and where I work we have a solution like this.I would ask for the key to be higher or alphanumeric less chance of colliding.
– Motta
I’ve switched to generate and test for collision
– Andre Lacomski