Calculating Product Offer

[sam123]

Hi,

 

I am having a problem that I was hoping someone here would be able to assist me with.

 

Basically its for an online shop. Some of the products have special offers on them when the number of customer reach up to a perticaulal number.I have two tables in my database. One to hold the information about the product, and one to hold the information about user price quote table.

 

 

1.Product table(retailer table)

 

price | no.of customers | Offer1 |no.of custmers | offer2

 

$200 | 10 | $100 | 5 | $90

 

2.Quote(User table)

 

Price | High | Low

 

$200 | $400 | $80

 

 

The number of users who have registered for a product reaches the number limit set by the retailer, i.e. 10, then the new offer is $100, i need to sent out this new offer to the users.

 

I have to get the count of customers whos price range of the product is equal to the product price in the product table.Then i need to comapre this count to no.of customers and if it matches i need to set the offer.

 

Any help would be appreciated.

 

Thank you.

[Muddy_Funster]

ok, so you know what you need to do, have you had a shot at doing it? do you have some code for us to work with? Also, how are you actualy allocating users to an offer price, infact where are you storing any information about users? Lets see the actual table structure.

[sam123]

Here is my code. Please help me to solve this problem.

 

$sql = "SELECT pd_price,pd_deal_price1,pd_deal_cust_number1,pd_deal_price2, pd_deal_cust_number2, pd_deal_price3, pd_deal_cust_number3 FROM pp_product WHERE uid=$uid";
$result = mysql_query($sql, $link) or trigger_error("SQL", E_USER_ERROR);
$num=mysql_num_rows($result);
   if(mysql_num_rows($result) > 0)
  {
	  while ($list = mysql_fetch_assoc($result)) {
  $price = $list['pd_price'];
  $offer1 = $list['pd_deal_price1'];
  $cust_number1 = $list['pd_deal_cust_number1'];
  $offer2 = $list['pd_deal_price2'];
  $cust_number2 = $list['pd_deal_cust_number2'];
  $offer3 = $list['pd_deal_price3'];
  $cust_number3 = $list['pd_deal_cust_number3'];
$qry="SELECT * FROM pp_quote WHERE pp_price=$price";
$result = mysql_query($qry, $link) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
if(mysql_num_rows($result) > 0) {
while ($list = mysql_fetch_assoc($result)) {
$high = $list['pp_high'];
   $low = $list['pp_low'];
$qry= "SELECT COUNT(*) FROM pp_quote WHERE pd_price BETWEEN $low AND $high ";
$result = mysql_query($qry, $link) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);

$nrows = $r[0];

 if($nrows == $cust_number1){
 $offer=$offer1;
 } else if ($nrows == $cust_number2){
 $offer=$offer2;
 } else if ($nrows == $cust_number3){
 $offer=$offer3;
 }
 }//end while
}//end if


}//end while
}//end if

echo "Offer:$offer";

[Muddy_Funster]

Yeah...that needs a major rewrite. Lets see your full table structure in detail please.

[sam123]

Thanks for your quick response. Here is the complete table structure.Please see below.

 

 

CREATE TABLE IF NOT EXISTS `pp_product` (

`pd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(11) NOT NULL,

`pd_rname` varchar(200) NOT NULL,

`pd_product` varchar(200) NOT NULL,

`pd_name` varchar(100) NOT NULL DEFAULT '',

`pd_description` text NOT NULL,

`pd_type` varchar(200) DEFAULT NULL,

`pd_size` varchar(200) DEFAULT NULL,

`pd_defenition` varchar(200) NOT NULL,

`pd_price` decimal(7,2) NOT NULL DEFAULT '0.00',

`pd_deal_price1` decimal(7,2) DEFAULT NULL,

`pd_deal_price2` decimal(7,2) DEFAULT NULL,

`pd_deal_price3` decimal(7,2) DEFAULT NULL,

`pd_deal_cust_number1` int(11) DEFAULT NULL,

`pd_deal_cust_number2` int(11) DEFAULT NULL,

`pd_deal_cust_number3` int(11) DEFAULT NULL,

`pd_qty` smallint(5) unsigned NOT NULL DEFAULT '0',

`pd_image` varchar(200) DEFAULT NULL,

`pd_thumbnail` varchar(200) DEFAULT NULL,

`pd_retailer_logo` varchar(200) DEFAULT NULL,

`pd_technical_specs` text,

`pd_product_specs` text,

`pd_warranty` text,

`pd_pickup` text,

`pd_special_details` text,

`pd_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`pd_last_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`pd_id`),

KEY `pd_name` (`pd_name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

 

CREATE TABLE IF NOT EXISTS `pp_quote` (

`pp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(11) NOT NULL,

`pp_product` varchar(200) NOT NULL,

`pp_name` varchar(100) NOT NULL DEFAULT '',

`pp_type` varchar(200) DEFAULT NULL,

`pp_size` varchar(200) DEFAULT NULL,

`pp_defenition` varchar(200) NOT NULL,

`pp_price` decimal(7,2) NOT NULL DEFAULT '0.00',

`pp_svdate` int(11) DEFAULT NULL,

`pp_range` varchar(200) DEFAULT NULL,

`pp_ramount` decimal(7,2) DEFAULT NULL,

`pp_high` decimal(7,2) NOT NULL,

`pp_low` decimal(7,2) NOT NULL,

`pp_status` varchar(60) NOT NULL,

`pp_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`pp_edate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`pp_last_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`pp_id`),

KEY `pp_name` (`pp_name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;

[Muddy_Funster]

ok, I'll see what I can come up with, as others will too I'm sure. Just so I know how best to focus on this, how open are you to changing these tables?

[sam123]

If it is really required to change the table structure, i can do it. But not sure it will affect the other site functionality because of the structure change. But anyway i am open for anything because i need to solve the problem. Thanks for your help.

[sam123]

What will be the easiest solution for this problem, restructuring the table and edit the code or just change code..?

[Muddy_Funster]

Easiest is rarely best, and not a route I usualy look to take. Restructuring the tables into a normalised structure would be the best solution, rewriting the code to work with what's there is probably the quickest. Unfortunately I'm being kept busy with work of my own at the moment so can't get a chance to look at either option at the moment. As it stands I'm not even sure on what some of the table columns are being used to store.

[sam123]

In the product table retailer is setting the offer price and no.of customers.

 

`pd_deal_price1` decimal(7,2) DEFAULT NULL,

`pd_deal_price2` decimal(7,2) DEFAULT NULL,

`pd_deal_price3` decimal(7,2) DEFAULT NULL,

`pd_deal_cust_number1` int(11) DEFAULT NULL,

`pd_deal_cust_number2` int(11) DEFAULT NULL,

`pd_deal_cust_number3` int(11) DEFAULT NULL,

 

 

If an user quote a product price it saves in the quote table. He can set the price range also say high 200 and low 90. So if total no.of customers whoes price range is between the product price matches the product price in the product table , then i need to send an email saying that this product is on sale. I am not saving the offer value anywhere now. I think i need to create one more colum in product table to add this data.

 

Please advice. Last two days i wasted for solving this problem. So any help will be greatly appreciated.

[Muddy_Funster]

I need you to take some time and work up a full description of what's going on here. Customers/Users don't quote prices to retailers/suppliers, it's the other way around. Users may say, I'm looking for thisItemHere and am willing to pay between lowValue and highValue for it, but that's about as far as it would go.

 

Please, a verbose description of the task you are looking to perform will save everyone time in the long term. If you can make sure to cover the following as well...

How is it all linked together?

How do users associate to products?

How do products associate to retailers/suppliers?

Is the number of customers accross each offer price bracket cumulative?

How are you differentiating between the groups of people that are at each end of the offer scale,

ie - offerPrice1 = 100, offerPrice2 = 80, offerPrice3 = 75 and users have their "quotes" at low = 60 high = 200. How are you selecting which price to send these users in the mail?

[sam123]

Sorry if i make you confused. I will explain you the project which i am currently working on .

 

In my shopping website, users and retailers can create their accounts. Retailers can add their product in any category and specify its price. He can also set offers like $90 if 10 users require the same product and if 5 customers choose the same product then the price will be $95..

 

At the same time users can set the price for a product in a perticaular category or he can specify its price range.He is open to get offer for this product.

 

There is no relationship between these two tables. Product details will be stored in the product table and user price detials will be stored in the quote table.

 

If this category matches with retailers category and if the product is same then i have to send him (user) a mail about the product.

 

Then i have to check the total no.of customers who's price range is between the product price specified by the retailer. If this matches with the no.of customers specified by retailer for this product , then i need to send an email saying that this product is on sale. i.e if 10 customers set the same product price or price range is between the product price in the product table , the i have to set the price of the product to $90 and i need to send email to those 10 users that this product is on sale and its price is $90 now. If 5 customers set the same product price or price range is between the product price in the product table , the the offer price will be $95. I have to send this offer to those 5 customers. Please let me know if you need any more clarification.

 

Thanks for your time.

[Muddy_Funster]

Right, so basicly the user picks products and states prices that they are willing to pay for these products in a list. The retailer lists products with a standard sell price and, optionaly, bulk discount cutoff limits. If there are enough users to reach the bulk dicount cutuff for a product you want to email the users that have listed that product with the sale price that the bulk discount will offer. But you only want to count the users that have listed a price range that matches the relevant bulk discount prices.

 

Is that the basis behind the idea?

 

What happens if 20 people register valid price ranges for a bulk discount that requires 20 people to take efect, but only 18 people follow through with a purchase?

How are you facilitating people who may want to by multiple items? ie. if the bulk discount takes effect at 5 orders can a single user order 5 items and recieve the discount all on their own?

 

Sorry if it seems I'm being obtuse, but I need to get my head around the outcome before I can work out the road to get there.

[sam123]

I am trying to do the first part. Now i am just sending the offer email for those customers and its their wish to buy the product. The offer will be expiring after few days and it will be describing in the email. I need to check the no of customers who are quoting the product and it is not related with their product purchase.

[Muddy_Funster]

OK, I don't have any data to test this, but the only way I can think of just now would be to do this :

$sql = <<<SQL
SELECT uid, pd_name, pd_deal_price1, pd_image
FROM pp_product
INNER JOIN (
 SELECT pp_product, count(*) as user_count
 FROM pp_quote
 GROUP BY pp_product
) as pp_count_users
ON (pp_count_users.pp_product = pp_products.pd_product)
WHERE user_count >= pd_deal_cust_number1
SQL;

this single query should return the results you are after for the deal price 1, you will eaither need to expand on it or run another 2 queries for the other 2 price cutoffs.

 

Like I said, I don't have the data to test it, to it's probably not exactly right, the where may need to actualy go inside the join subquery. Give it a shot and lt us know how it goes.

[sam123]

Sorry i am confused here. So in that sql how do we compare the product with its price range specified by the user?

 

In the quote table i am saving the product name,category,price, high and low(this is the price range specified by the user i.e he is open to accept the offer with in this price range).

 

What i am trying to do is first i will get the product information form the product table. i.e, the product, its price, the offer details and no. of customers.

 

I want to check in quote table any product price or pricerange is matching with the product price specified in the product table.

Then i need to count the no of customers of this price or with in the price point matches with price specified in the product table.

 

After that i need to compare the no.of customers specified in the product table and count of the customers (above condition) from quote table and if it matches i need to set the product on sale and need to send the email.

 

Please explain. Thanks for your time and help.

[Muddy_Funster]

That one doesn't compare the price, only the number of people "following" the product, I wasn't sure what part of what I asked you it was that you were trying to achieve at this point in time. Just expand on the query to add something like this, and it should do the price range check as well :

$sql = <<<SQL
SELECT uid, pd_name, pd_deal_price1, pd_image
FROM pp_product
INNER JOIN (
 SELECT pp_product, count(*) as user_count
 FROM pp_quote 
  LEFT JOIN pp_product 
  ON (pp_product.pd_product = pp_quote.pp_product)
 WHERE (pp_product.pd_deal_price1 BETWEEN pp_quote.pp_low AND pp_quote.pp_high)
 GROUP BY pp_product
) as pp_count_users
ON (pp_count_users.pp_product = pp_products.pd_product)
WHERE user_count >= pd_deal_cust_number1
SQL;

It's one of the harder things to get your head around with SQL, although things seem as though they need to be split over what apears to be clearly defines stages, with SQL the stages are all incorporated into the one overall process. This query (assuming it works - wich is a big ask the way my brain is just now) now runs a subquery (the second select) that counts the amount of users watchiing each product as long as the products deal_price1 is within the range the user entered. The main query then links to the results of this subquery and lists the uid, pd_name, pd_deal_price_1 and pd_image for each successfully matched product as long as the number of users that the subquery matched is higher or equial to the number entered in the pd_deal_cust_number1 field.

[Barand]

PMFJI,

 

Is this scenario a possibility?

 

Product price 100.00

 

Num Custs | 10 | 20 | 30

Price | 95 | 90 | 85

 

There are 10 customers willing to pay 100.00.

Because there are 10 the price drops to 95.00

Now the price is only 95.00 there are another 10 willing to pay 95.00

Now there are 20 customers so the price falls to 90.00

This new lower price brings in another 10 customers so the price drops to 85.00.

This bottom price brings in further customers.

 

Scenario 2

 

A customer has a low-high quote for a product of 100-125

The product is priced at 90

Is customer excluded because price is not in their range?

Edited November 20, 2012 by Barand

[sam123]

In the site i am listing the exact price of the product.The price of the product is not changing. If 10 customers are willing to quote this product on this same price or with in the price range i will give offer $90 only to those 10 customers.But in the site the price will be same $100..I will consider only the price range of the product. If the customers set their price range $80-$90 ,they will be excluded from the offer, since the price of the product will be same i.e $100. I hope you are clear by now. Thanks .

[sam123]

Thanks Muddy_Funster. But the code is not working. I tried all the way i can but lost somewhere..

 

Let me explain this in a simple way what i am trying to acheive here.

 

From the product table i will get Price, pname, pd_deal_price_1, pd_deal_cust_number1, pd_deal_price_2, pd_deal_cust_number2, pd_deal_price_3, pd_deal_cust_number3.

 

eg. $100,sony,$80,10,$95,5,$90,3

 

Then in the quote table i listed pname,price,high and low.

eg: sony,$95,$120,$80

 

What i am trying to do here is that i will check pname and price or price range of the product from quote table is matching with pname and price from the product table.If it matches then i will count the number of users which quote the same price or with in the price range.

 

Then i will check this count will match to the pd_deal_cust_number1, then i will set the first offer, if it matches the pd_deal_cust_number2 then i will set the second offer like that.

 

Sorry if i made you confused. Please help me.

[Muddy_Funster]

If you give me the errors/problems with the code I might be able to help. But without having a sample dataset to work with I can't pull that kind of query out of thin air. Others can, and Barand is one of those, but I need something tangible to work with.

[Barand]

 

Scenario 2

 

A customer has a low-high quote for a product of 100-125

The product is priced at 90

Is customer excluded because price is not in their range?

 

@sam123,

 

What about the second case that I mentioned?

[sam123]

Hi Barand,

 

You are right,customer will be excluded because price is not in their range.

 

Hi Muddy_Funster,

 

I tried the code which you give me ,its not giving any error. But it is not working as expected.

[sam123]

When i try the below sql i am getting the wrong answer.

It shows samsung | 16.But the actual count is 6.

 

SELECT pp_name, count( * ) AS user_count

FROM pp_quote

LEFT JOIN pp_product ON ( pp_product.pd_name = pp_quote.pp_name )

WHERE (

pp_product.pd_price

BETWEEN pp_quote.pp_low

AND pp_quote.pp_high

)

LIMIT 0 , 30

[Barand]

This is new one for me - a business model designed to minimise sales.

 

Muddy has hinted several times that one of the problems he faces is a lack of test data. Why don't you provide some, along with predicted results?

Edited November 21, 2012 by Barand