asked on

Writing MySQL query help required

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

Beverley Portlock

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?

dovercomputers

ASKER

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.

Beverley Portlock

"....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"

Scott Madeira

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

dovercomputers

ASKER

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

dovercomputers

ASKER

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

dovercomputers

ASKER

I have worked out that

and sum(c.qty) > cpn.cc_minquantity

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

dovercomputers

ASKER

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

Beverley Portlock

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

dovercomputers

ASKER

Sorry, my mistake

XN1301 is the product code I am using

Jim

Beverley Portlock

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.

ASKER CERTIFIED SOLUTION

dovercomputers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dovercomputers

ASKER

I solved the problem myself.