[SOLVED] A scenario similar to "Purchasing discount coupons" tutorial

sarah963
OFFLINE
Senior Member
Posts: 44
Thanks: 10
Karma: 0

8 years 2 months ago - 8 years 2 months ago #278488 by sarah963

[SOLVED] A scenario similar to "Purchasing discount coupons" tutorial was created by sarah963

Hi everybody

I have been working on a scenario (i.e. user case) somewhat similar to the "Purchasing discount coupons" tutorial presented here: www.joomlapolis.com/support/tutorials/107-use-cases/18407-purchasing-discount-coupons

Unfortunately, my SQL code is not working..

I generated random discount codes in MySQL and saved them in my database.
Now, when a user register for the first time in my website, he/she is assigned only one of those codes randomly. Then this code should show in their profile under the discount tab and never assigned to anybody else.

The random-discount-codes table 'c_discounts_test' is saved in my database and has the following columns:
id, hash, assigned, user_id, discount_code.

The 'assigned' column is a Boolean field in which it's default value is set to 0 but when a user is registered and assigned a random discount code from the table 'c_discounts_test' the SQL code below will update it's value to 1 so that the discount code is used only once.

Now, the SQL code I used under the 'SQL actions' in my CB Subs plan is:

UPDATE c_discounts_test AS t1
        INNER JOIN 
        (
            SELECT  *
            FROM    c_discounts_test
            WHERE assigned = 0
			ORDER BY RAND() LIMIT 1
        ) AS t2 
            ON t1.id = t2.id
SET t1.assigned = 1, t1.discount_code = t2.hash, user_id = [user_id];

notice I did not use the ` character. maybe that's a problem.. i don't know!

Now the code I used in the CB Field query is:

select discount_code from c_discounts_test WHERE user_id = [user_id];

Again I did not use the ` character.. and I am sure this code has other problems but I can not identify them,,

Please help me! :unsure:

Thanks in advance
Sarah

Last edit: 8 years 2 months ago by krileon.

Please Log in to join the conversation.

sarah963
OFFLINE
Senior Member
Posts: 44
Thanks: 10
Karma: 0

8 years 2 months ago - 8 years 2 months ago #278489 by sarah963

Replied by sarah963 on topic A scenario similar to "Purchasing discount coupons" tutorial

Well, Offcourse, I realized that I forgot to add the prefix to user_id in the first code..

sorry guys the code works fine now!
The correct SQL code is:

UPDATE c_discounts_test AS t1
        INNER JOIN 
        (
            SELECT  *
            FROM    c_discounts_test
            WHERE assigned = 0
			ORDER BY RAND() LIMIT 1
        ) AS t2 
            ON t1.id = t2.id
SET t1.assigned = 1, t1.discount_code = t2.hash, t1.user_id = [user_id];

Anyway, my question now is (since the code works) does these two codes pose a potential problem, vulnerability ,, or whatever??

I read that using the rand() in the 'order by' clause could slow the database tremendously!!
Is there a better way to do it??

or should I just ago ahead leave it as is?!!

Thanks alot
Sarah

Last edit: 8 years 2 months ago by sarah963.

Please Log in to join the conversation.

sarah963
OFFLINE
Senior Member
Posts: 44
Thanks: 10
Karma: 0

8 years 2 months ago #278490 by sarah963

Replied by sarah963 on topic A scenario similar to "Purchasing discount coupons" tutorial

I also have a feeling that calling one of my columns 'hash' is not a good idea!!

:huh:

Please Log in to join the conversation.

krileon
Team Member
ONLINE
Posts: 68555
Thanks: 9098
Karma: 1434

8 years 2 months ago - 8 years 2 months ago #278525 by krileon

Replied by krileon on topic A scenario similar to "Purchasing discount coupons" tutorial

So you're assigning the user to the first unassigned random coupon code available? What happens if there's multiple coupon code rows per user or is that a scenario that isn't accounted for? I would do something like the following.

Table Structure:
id (int) (auto-increment)
coupon (varchar 255)
user_id (int) (null)

The above basically means user_id is optional. You'd create your coupon rows without a user id assigned at all. This essentially handles your assigned column and who it's assigned to as a single column.

Next you need to assign the next available coupon to the user during registration which the below should do. I wouldn't bother randomly selecting one, but just grabbing the next available as you're correct that rand() is slow although in this scenario it shouldn't particularly matter.

UPDATE `#__discounts_test` SET `user_id` = '[user_id]' WHERE `user_id` IS NULL LIMIT 1

Next we need to grab what code is assigned to the user using a CB Query Field. This is simply done as follows.

SELECT `coupon` FROM `#__discounts_test` WHERE `user_id` = '[user_id]' LIMIT 1

I also have a feeling that calling one of my columns 'hash' is not a good idea!!

It's a keyword so I wouldn't use it. Typically you want to avoid using keywords. Reserved keys would flat out error. The below is a list of reserved keys and keywords.

dev.mysql.com/doc/refman/5.6/en/keywords.html

If you want to handle multiple coupon codes that's also doable as the query field would just need to be adjusted to output multiple rows. With this usage though you have to manually create your coupons while the tutorial is actually generating coupons in CBSubs Promotions directly.

Note the limit in the update query is subjective to your database version, but with todays hosting it should work fine. Test in phpmyadmin to be sure though.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Last edit: 8 years 2 months ago by krileon.

The following user(s) said Thank You: sarah963

Please Log in to join the conversation.

sarah963
OFFLINE
Senior Member
Posts: 44
Thanks: 10
Karma: 0

8 years 2 months ago - 8 years 2 months ago #278570 by sarah963

Replied by sarah963 on topic A scenario similar to "Purchasing discount coupons" tutorial

Thanks Kyle

user_id column in my table is empty (that is all null values) initially.

Your first code did not work. I think it needs inner join.

I fixed it to look like this:

UPDATE c_discounts_test AS t1
        INNER JOIN 
        (
            SELECT  *
            FROM    c_discounts_test
            WHERE user_id IS NULL LIMIT 1
        ) AS t2 
            ON t1.id = t2.id
SET t1.user_id = [user_id];

Now this code does not pick a random row, it just go through the table from the top and search for a null user_id ,,

That probably will avoid the rand() problem ,,,,
I changed the name of column called "hash" to something else..

Oh I forgot to mention that the reason behind generating discount_codes in advance is because when people register in my website and get the discount_codes, they will use it in another website developed by other people. So, I needed to give those other developers a table with previously generated discount codes in order to make sure that users are members in my website so they can give them discounts in their own website..

Thanks alot for your help Kyle ,,

Last edit: 8 years 2 months ago by sarah963.

Please Log in to join the conversation.

krileon
Team Member
ONLINE
Posts: 68555
Thanks: 9098
Karma: 1434

8 years 2 months ago #278596 by krileon

Replied by krileon on topic A scenario similar to "Purchasing discount coupons" tutorial

Shouldn't need an inner join. LIMIT works on update queries since MYSQL 5.5. It will not work if there are joins in it though. At any rate the join usage is the alternative and if it's working then that's also fine.

Oh I forgot to mention that the reason behind generating discount_codes in advance is because when people register in my website and get the discount_codes, they will use it in another website developed by other people. So, I needed to give those other developers a table with previously generated discount codes in order to make sure that users are members in my website so they can give them discounts in their own website..

Ah, ok. Neat usecase!

Please Log in to join the conversation.

Moderators: beat, nant, krileon

Time to create page: 0.263 seconds

[SOLVED] A scenario similar to "Purchasing discount coupons" tutorial

Search

Login / Logout

Menu

User Menu