Solved

Writing MySQL query help required

Posted on 2011-02-24
13
334 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Node.js 11 80
can i read my emails on lamp ftp 4 70
embadded search engine in website 4 46
PHP substring 3 51
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

734 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