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

dovercomputersAsked:
Who is Participating?
 
dovercomputersConnect With a Mentor Author Commented:
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
 
Beverley PortlockCommented:
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
 
dovercomputersAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Beverley PortlockCommented:
"....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
 
Scott MadeiraCommented:
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
 
dovercomputersAuthor Commented:
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
 
dovercomputersAuthor Commented:
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
 
dovercomputersAuthor Commented:
I have worked out that

and sum(c.qty) > cpn.cc_minquantity

is causing the error. But I have no idea why.
0
 
dovercomputersAuthor Commented:
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
 
Beverley PortlockCommented:
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
 
dovercomputersAuthor Commented:
Sorry, my mistake

XN1301 is the product code I am using

Jim
0
 
Beverley PortlockCommented:
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
 
dovercomputersAuthor Commented:
I solved the problem myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.