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

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: 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: 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!

Thanks in advance
Sarah

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

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

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

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

:huh: :huh:

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.


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

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.

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:
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 ,,

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!