Sql auction script

Question

I need to make an application for an auction company of used things, then I will need to create an interface where n customers can give a minimum bid and a maximum bid for a product X and the system returns the winner, but making a rule for the customer to pay as much as possible. The business rule is a little different, because, in this project, wins whoever gives the least value, but, not its minimum (I do not understand why, but will be so rs). For example, if the minimum value of a customer C for R$50,00 and the maximum is R$ 300,00 but in the calculus round he can win with R$120,00 as the cheapest price, he would pay R$120,00 and not the R$50,00 which is to say the least. I hope I’ve been able to explain it to you. I was wondering if you could do this with just a mysql query or php together. And if so, how can I do it? Can anyone give me a light? (There is also the possibility of a draw)

---

The main query table would have:

• ID (pk)
• PRODUCT_ID (fk)
• CUSTOMER_ID (fk)
• MIN_PRICE (float)
• MAX_PRICE (float)
• WINNER (true or false)

Answer 1

from what I understand is the same as those TV shows, of the lowest single value won? This was can do with simple sql! Analyzing the values whose values are duplicated, eliminating them and then analyzing the remaining values to reach the minimum value.

Ex to identify duplicate values:

SELECT DCB, Count() FROM table name GROUP BY DCB HAVING Count() > 1

with this code will return duplicated values, this way just do a routine to delete them or change a value hidden in the table as "unlimited value_values".

Later of a new SELECT returning only the lower value above R$50,00, and so will have its winner.

I believe this is the way your client wishes, at least as soon as I understood.

Answer 2

In a single query you do not solve this. Working with auction bids are more complex than they appear to be - I work in a auction house. ;)

You’ll have to run a series of rules and filters to get to the right guy. Don’t get too attached to having a single query, it will mess you up more than it will help.

I recommend that, at the end of the auction, you make a simple query, returning a list - never a table - with all bids, and from there you start the calculations to find the winner.

I will list here a list of activities that you should run in your list, will help to see that a query is not what you are looking for:

1. Delete from the list all bids where the maximum bid is lower than the Reserve Price - if any;
2. Delete from the list all bids where the maximum bid is less than the minimum bid of another buyer;
3. Delete from the list all bids where the minimum bid is higher than the maximum bid of another buyer;
4. Select the buyer of the FIRST bid that has the highest bid - it is important to be the first if more than one buyer has the same highest bid, but the winner is the first bid;
5. Select the SECOND highest bid and add the Increment Value;
6. Set that the buyer of item 4 will pay the value of item 5;

Increment Value: Value that must be added to the value of the next bid. For example: Between R$0-R$100 the increment will be R$20. Between R$100-R$500 will be R$100. Between R$500-R$1000 will be R$250. And so on.