当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Select the highest number in mysql

问题描述:

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,

Happy coding,

分享给朋友:
您可能感兴趣的文章:
随机阅读: