Solved

Writing MySQL query help required

Posted on 2011-02-24
13
327 Views
Last Modified: 2012-05-11
Hello experts,
I have been staring at the screen ages and getting nowhere on this. I have a shopping cart written in php and a list of special offers that I want to validate the cart against when the user arrives at the show cart page. If they qualify for a special offer this will then be displayed.

Cart table:
cartId,cookieId,itemId,qty
itemId = product code
qty = quanity in cart

Coupon table:
cc_id,cc_code,cc_productcodes,cc_datestart,cc_dateend,cc_minspend,cc_minquantity,typeofpromo,offervalue,cc_description,cc_active

cc_productcodes can contain up to 100 product codes each seperated with a space.
cc_minspend = minimum spend required to activate the offer
cc_minquantity = minimum quantity to activate offer
typeofpromo  = 1 for min qty, 2 for min spend, 3 for min spend & min qty
offervalue = what you get free
cc_active = 1for active, 0 for inactive

I am not sure how to tackle this. And quidance, examples, in fact anything to help would be much appreciated.

Many thanks
Jim

0
Comment
Question by:dovercomputers
  • 8
  • 4
13 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 34969027
You need a cross reference table to link your cart table to your cookie table. The cross reference is simply a table with two fields, the product code and the coupon code

CREATE TABLE xRef (
     xrProductCode ..... whatever data type you use
     xrCouponCode ..... whatever data type you use
     KEY( xrProductCode )
     KEY (xrCouponCode )
)

then for each product that has a discount coupon just write a record to this table, so if product AAA had a coupon 111 and product BBB had coupons 22A and 22B then you would have

AAA 111
BBB 22A
BBB 22B

Then to check if a product has a coupon

SELECT xrCouponCode FROM xref WHERE xrProductId = 'AAA'

similarly to find all products valid for coupon 22A

SELECT xrProductId FROM xref WHERE xrCouponCode = '22A'



Is that any nearer what you are trying to do?
0
 

Author Comment

by:dovercomputers
ID: 34969093
bportlock: Thanks for the reply,

I sort of have this already in the coupon table, the only difference being that all the product codes for each coupon are storedin a single field, space seperated. Or am I barking up the wrong tree.

What I was thinking was something along the lines of
do { // LOOP THROUGH COUPONS

// SELECT ALL ITEMS FROM CART THAT HAVE SAME PRODUCT CODE
$row = mysql_query("select * from cart inner join products_schema on cart.itemId = products_schema.ProductCode where cart.cookieId = '" . GetCartId() . "' AND itemId LIKE '%".$row_currentCoupons['cc_productcodes']."%'");

} while ($row_currentCoupons = mysql_fetch_assoc($currentCoupons)); // END COUPON LOOP

Open in new window


I have needed to join the  table products.schema as this holds the product prices for each client, and then count the quantities or values for each hit and to see if the activate the coupon.

0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 34969148
"....the only difference being that all the product codes for each coupon are storedin a single field, space seperated. Or am I barking up the wrong tree."

I'm afraid you are barking up the wrong tree. This is a well known problem and the best solution to it is as I outlined above. Storing repeated data (product codes) is a well known path to problems which is why in database design rule number one is "no repeated data". I kid not not - it really is rule number one  http://en.wikipedia.org/wiki/First_normal_form  "First normal form......it is free of repeating groups"

0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 34969159
Storing data in a field and separating it by spaces isn't the best way to do it.  You would have to parse that field every time to see which product codes exist and if any apply.  If you use the cross ref table as suggested you can calculate each product, value and quantity and then query to see if there is a coupon code that applies to it.  That can all be easily done in SQL by joining the tables.  

Below is a start for the SQL to compare for the spending case.  I didn't check it for proper syntax (commas, field names, etc.) so you may have to do a little messing around with it.

Hope it helps...

select 
	c.cartid,
	c.cookieid,
	c.itemid,
	sum(c.qty) as 'TotalQty',
	sum(c.qty) * ps.cost as 'TotalCost
	
from 
	cart c
	inner join products_schema ps on c.itemId = ps.ProductCode 
	inner join xref x on c.itemid = x.cartid
	inner join coupon cpn on x.cc_id = cpn.cc_id
where 
	c.cookieId = 'yourcartID'  
	and cpn.productcodes = c.itemid
	and sum(c.qty) * ps.cost > cpn.cc_minspend

Open in new window

0
 

Author Comment

by:dovercomputers
ID: 34969202
Ok, thank you. I will redesign the DB and get to work on trying to implement the above ideas/code.

I'll be back in a while :)

Jim
0
 

Author Comment

by:dovercomputers
ID: 34969424
OK, I have created a new table 'xref' and added a couple of rows.

xrefID       couponcode   productcode
2               wine/w               EN1301
3               wine/w               EN1302

I have changed the field name in the query to match the tables. As 99% of the coupons are qty based I am trying to work on that first. So this is now my query...

$query_currentCoupons = "select 
	c.cartId,
	c.cookieId,
	c.itemId,
	sum(c.qty) as 'TotalQty'
from 
	cart c
	inner join products_schema ps on c.itemId = ps.ProductCode 
	inner join xref x on c.itemId = x.productcode
	inner join coupon_codes cpn on x.couponcode = cpn.cc_code
where
	c.cookieId = 'GetCartId()'  
	and cpn.cc_productcodes = c.itemId
	and sum(c.qty) > cpn.cc_minquantity";

Open in new window


If I run the on its own I get the following error:
Invalid use of group function

Also, do I run this query within a loop of the coupons codes?

Jim
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dovercomputers
ID: 34969645
I have worked out that

and sum(c.qty) > cpn.cc_minquantity

is causing the error. But I have no idea why.
0
 

Author Comment

by:dovercomputers
ID: 34969891
Having had a read on various sites, having a sum in a where clause is a no no. So I have changed it to

select 
	c.cartId,
	c.cookieId,
	c.itemId,
	sum(c.qty) as 'TotalQty',
	cpn.cc_minquantity
from 
	cart c
	inner join products_schema ps on c.itemId = ps.ProductCode 
	inner join xref x on c.itemId = x.productcode
	inner join coupon_codes cpn on x.couponcode = cpn.cc_code
where
	c.cookieId = '".GetCartId()."'  
	and cpn.cc_productcodes = c.itemId
	having TotalQty > cpn.cc_minquantity

Open in new window


which removes the error but gives me 0 rows. I know there is consistant data in the tables, I have double checked these.

c.itemId has XN1301 in one of the rows c.qty has a value of 10
cpn.cc_productcodes has XN1301 in one of the rows with cpn.cc_minquantity as 3
ps.ProductCode has XN1301 in one of the rows
xref.productcode has a row with XN1301
and xref.couponcode has a matching code in coupon_codes.cc_code

Any ideas please?

Jim
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 34970722
In the examples above you use EN1301 and XN1301. Is this correct?

OK, I have created a new table 'xref' and added a couple of rows.

xrefID       couponcode   productcode
2               wine/w               EN1301
3               wine/w               EN1302


.....

c.itemId has XN1301 in one of the rows c.qty has a value of 10
cpn.cc_productcodes has XN1301 in one of the rows with cpn.cc_minquantity as 3
ps.ProductCode has XN1301 in one of the rows
xref.productcode has a row with XN1301
and xref.couponcode has a matching code in coupon_codes.cc_code


0
 

Author Comment

by:dovercomputers
ID: 34970804
Sorry, my mistake

XN1301 is the product code I am using

Jim
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 34978254
The inner joins will effectively produce a selection which is then further reduced in size by the WHERE and HAVING clauses so the place to start is to eliminate the WHERE and HAVING clauses one bit at a time until data appears. When that happens you can look at the sub-clause you just removed and then at the data and it will be fairly obvious what happened.

I would remove the HAVING clause and try it and if it produces zero records then remove and cpn.cc_productcodes = c.itemId and then the where c.cookieId = '".GetCartId()."' . If it still produces zero records then there is something wrong with the JOIN criteria.

Try that and report the results back here.
0
 

Accepted Solution

by:
dovercomputers earned 0 total points
ID: 35121367
I tried everything and spent days on this and couldn't make it work. It appears what I was trying to do wasn't technically possible with a single query. So what I did was to run two queries in a loop to get the desired results. Not perfect, not pretty, but it works and is quick.

Thanks for your help.
0
 

Author Closing Comment

by:dovercomputers
ID: 35163426
I solved the problem myself.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now