How select 3 criteria

[wudz]

Hi,
I wish to select three auctions that hold the highest number of bids (num_bids) and if any hold the same number pick the highest viewed (viewed), so I end up with the three hottest auctions.The above variables are held in the same table below.

Now I already have a loop query running to show the last three uploaded auctions in the login area ....query below. I would like just to duplicate the query with the added changes into a new page, as the rest of the script does time, started, area, ending, price, picture..etc which I would still require.

I am stuck on how to extract the auctions by checking which three have the highest bids..etc



$limit = 20;
if(!$offset) $offset = 0;
$result = mysql_query ( "SELECT * FROM AUC_qwr WHERE closed='0' AND suspended = '0' AND local_area= '$local_area' ORDER BY ends ASC LIMIT $offset,$limit" );
if ($result)
{
$tplv = "";
$bgColor = "#EBEBEB";
while ($row=mysql_fetch_array($result))

all help appreciated.

John

 

[Olavxxx]

If you want to get the three highest bids:

SELECT <id>, <auction price>, <etc.> FROM <table> WHERE `closed`  = '0' AND `suspended` = '0' AND `local_area` = '$local_area' SORT BY <auction price field> DESC LIMIT 0,3

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

Oh, sorry.. Highest bids, I thought about highest price!
I guess you could also use MAX() function in mysql?

*too tired for programming*

ORDER BY field1 A, field2 ASC LIMIT 0,3;

I dont know how many fields you can order by, but:
In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[wudz]

Thanks DaButcher for your speedy response,

If in your example I changed auction_price to num_bid (as it is the largest amount of bids I require). if say there was 5 auctions with 10 bids I would like to select 3 of the 10 with the highest amount of viewed which would correspond more accuratly with the 3 hottest, other than going down the road of how fast the bids were place over the item auction time...

Cheers
john

 

[Olavxxx]

If you only want the hottest items, it's just a simple query.
Select the relevant fields, where fields = your rules.

Then you ORDER BY fields, DESC and limit from row 0 to number of rows you want.

EG:

SELECT * FROM table WHERE closed='0' AND .. = '..' AND .. = '..' ORDER BY num_bid DESC 0,3

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[wudz]

looks like we cross posted...looks like you answered my second question...will give it a whirl tomorrow...just turned midnight and my fangs are showing..hi

Cheers again

John

 

[wudz]

Cheers Olav,

Sorry I am a noob to PHP, Flash graphics are my forta, now pushed 60 finding it extremly hard for things to sink in....but still trying.

many thanks

john

 

[Olavxxx]

Hi again,
I looked up and you can do a order by on three fields (or "as many as you want".).

Also, I dont know if you are aware that you dont have to select fields to order by them, if you dont want to show them to your users.

In this case you might want to, however.

Eg. If you can avoid select *, I would.
Today I have been really slow myself and have been reading and posting confused

My dog is sick (stomach), so I've gotten way more exercise than it-people should get

Also, as most it-people, less sleep then they say is lethal.
(ok, not that little, but still..)

Good night and good luck!

ps. as far as queries go,

http://www.mysql.com

is also quite good.

http://www.php.net

is a great resource..

but often, you have to know a, to find b on those places.
However, google might some times be the middle-ground, as far as you know what you want to accomplish.

Good night from Norway.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster