?
Solved

Writing MySQL query help required

Posted on 2011-02-24
13
Medium Priority
?
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

762 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