Sql auction script [closed]

-1

I need to make an application for a used things auction company, so I need to create an interface where n clients can bid a minimum and a maximum bid for a X product 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, whoever gives the lowest value, but not his minimum (I do not understand why, but it will be so rs). For example, if the minimum value of a C client is $ 50.00 and the maximum value is $ 300.00 but in the round of calculation 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 their minimum. I hope I have been able to explain it to you. I want to know if you can do this with just a mysql query or with php together. And if so, how? Can anyone give me a light? (There is also the possibility of a tie)

The main query table would have:

  • ID (pk)
  • PRODUCT_ID (fk)
  • CUSTOMER_ID (fk)
  • MIN_PRICE (float)
  • MAX_PRICE (float)
  • WINNER (true or false)
asked by anonymous 07.02.2017 / 00:53

2 answers

1

By what I understand is equal to those tv shows, the lowest single value wins? From this were manages to do with simple sql! Analyzing the values for which there are duplicate values, eliminating them and then analyzing the remaining values to get the minimum value.

Ex to identify duplicate values:

  

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

This code will return the duplicate values, so just do a routine to delete them or change a hidden value in the table as "limit values."

Subsequently a new SELECT returning only the lowest value above $ 50, so you have your winner.

I believe this is the way your customer wants, at least ofi as soon as I understand it.

    
07.02.2017 / 03:48
1

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

You'll have to run a series of rules and filters to get the guy right. Do not cling to having a single query, this will more trouble you than help.

I recommend that, at the end of the auction, I 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'll list here a list of activities that you should perform in your list, it will help you to see that a query is not what you are looking for:

  • Exclude from the list all bids where the maximum bid is less than the Reserve Price - if any;
  • Exclude from the list all bids where the maximum bid is less than the minimum bid of another buyer;
  • Exclude from the list all bids where the minimum bid is greater than the maximum bid of another buyer;
  • Select the buyer of the FIRST bid that has the highest bid - it is important to be the first bidder, if more than one buyer has the same bid, but the one who wins is the bidder;
  • Select the second highest bid and add the Increment value ;
  • Define that the buyer of item 4 will pay the value of item 5;
  • Increment value : Value that should 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.

        
    07.02.2017 / 09:35