I am developing a trading site. Users will post some price, $100, $120 for certain products. And I wanted to display highest price for that product, which means current highest price will replace the previous highest price. Tables of mysql is:
user_email //The user who gives price for the product
product//The product name
price//The prices that users posted
chosen//it will be 1 if it is highest otherwise 0
What I wanted to do is that if a user posted $100 for, for example, a book and it is currently highest price (chosen is one for this user) and site displays it. Another user posts $120 for that book and it is now highest price (chosen is 1) and for the previous user's chosen is 0. I could not figure out how to compare prices for certain products and change all of the lower prices chosen 0 for that product. How can i do this? Sorry i am new:(
You can use the MAX function from MySQL to get the highest number of a colomn. Example :
SELECT MAX(field) FROM `table_name`
You just need to add WHERE and it will confine the search fields.
After, you update everything else with something like this
UPDATE `table_name` SET `field` = 0 WHERE `product` = "X" AND `id` != Y
X will be the product ID and Y will be the ID from the first query.
Something like this will do you,
$q = $dbc -> prepare("SELECT price FROM table_name ORDER BY price DESC LIMIT 1");
Using the order by clause will get you the highest price available, now store it in a variable like so,
$q -> execute(); $highest = $q -> fetch(PDO::FETCH_ASSOC);
Now change all the other users' price to zero,
$q = $dbc -> prepare("UPDATE table_name SET price = 0 WHERE price != ?"); $q -> execute(array($highest));
Now this will eliminate the need for the chosen column in your database, as you only need the highest price for a bid, which can easily be found using the opposite of the above query with another parameter for the BID ID.
This example is using PDO which I highly suggest if you are making a trading site, I hope this was what you wanted to achieve,