MySQL Question

[Anon]

I have another MySQL question, but this one is a little different then the other one I had asked. Here's my situation:

I have a database of files and each file has a "column" for the number of downloads which increases by 1 everytime someone downloads it.

I was wondering how I could go through and print the top 5 most downloaded files (or top 10, top 25, top 50), something along those lines.

Thanks very much in advance.

[kylector]

Here is how I did it a couple years ago when I was still a newbie coder. Now I'd probably make another function called renumber that could shorten it by a ton. Or there could just be a much better way to do it, but this is the code I had handy 🙂. BTW, this is in MivaScript:

<MvASSIGN NAME="totalcount" VALUE=d.totrec>
<MvASSIGN NAME="mycounter" VALUE="1">
<MvASSIGN NAME="found" VALUE="0">

<MvASSIGN NAME="count1" VALUE=0>
<MvASSIGN NAME="count2" VALUE=0>
<MvASSIGN NAME="count3" VALUE=0>
<MvASSIGN NAME="count4" VALUE=0>
<MvASSIGN NAME="count5" VALUE=0>
<MvASSIGN NAME="count6" VALUE=0>
<MvASSIGN NAME="count7" VALUE=0>
<MvASSIGN NAME="count8" VALUE=0>
<MvASSIGN NAME="count9" VALUE=0>
<MvASSIGN NAME="count10" VALUE=0>

<MvASSIGN NAME="place1" VALUE=0>
<MvASSIGN NAME="place2" VALUE=0>
<MvASSIGN NAME="place3" VALUE=0>
<MvASSIGN NAME="place4" VALUE=0>
<MvASSIGN NAME="place5" VALUE=0>
<MvASSIGN NAME="place6" VALUE=0>
<MvASSIGN NAME="place7" VALUE=0>
<MvASSIGN NAME="place8" VALUE=0>
<MvASSIGN NAME="place9" VALUE=0>
<MvASSIGN NAME="place10" VALUE=0>

<MvWHILE EXPR="{mycounter LT (totalcount + 1)}">
	<MvGO NAME=scmalias ROW=mycounter>

	<MvIF EXPR="{dls GT count1 OR dls EQ count1}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=place6>
		<MvASSIGN NAME="place6" VALUE=place5>
		<MvASSIGN NAME="place5" VALUE=place4>
		<MvASSIGN NAME="place4" VALUE=place3>
		<MvASSIGN NAME="place3" VALUE=place2>
		<MvASSIGN NAME="place2" VALUE=place1>
		<MvASSIGN NAME="place1" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=count6>
		<MvASSIGN NAME="count6" VALUE=count5>
		<MvASSIGN NAME="count5" VALUE=count4>
		<MvASSIGN NAME="count4" VALUE=count3>
		<MvASSIGN NAME="count3" VALUE=count2>
		<MvASSIGN NAME="count2" VALUE=count1>
		<MvASSIGN NAME="count1" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count2 AND dls LT count1}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=place6>
		<MvASSIGN NAME="place6" VALUE=place5>
		<MvASSIGN NAME="place5" VALUE=place4>
		<MvASSIGN NAME="place4" VALUE=place3>
		<MvASSIGN NAME="place3" VALUE=place2>
		<MvASSIGN NAME="place2" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=count6>
		<MvASSIGN NAME="count6" VALUE=count5>
		<MvASSIGN NAME="count5" VALUE=count4>
		<MvASSIGN NAME="count4" VALUE=count3>
		<MvASSIGN NAME="count3" VALUE=count2>
		<MvASSIGN NAME="count2" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count3 AND dls LT count2}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=place6>
		<MvASSIGN NAME="place6" VALUE=place5>
		<MvASSIGN NAME="place5" VALUE=place4>
		<MvASSIGN NAME="place4" VALUE=place3>
		<MvASSIGN NAME="place3" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=count6>
		<MvASSIGN NAME="count6" VALUE=count5>
		<MvASSIGN NAME="count5" VALUE=count4>
		<MvASSIGN NAME="count4" VALUE=count3>
		<MvASSIGN NAME="count3" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count4 AND dls LT count3}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=place6>
		<MvASSIGN NAME="place6" VALUE=place5>
		<MvASSIGN NAME="place5" VALUE=place4>
		<MvASSIGN NAME="place4" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=count6>
		<MvASSIGN NAME="count6" VALUE=count5>
		<MvASSIGN NAME="count5" VALUE=count4>
		<MvASSIGN NAME="count4" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count5 AND dls LT count4}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=place6>
		<MvASSIGN NAME="place6" VALUE=place5>
		<MvASSIGN NAME="place5" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=count6>
		<MvASSIGN NAME="count6" VALUE=count5>
		<MvASSIGN NAME="count5" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count6 AND dls LT count5}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=place6>
		<MvASSIGN NAME="place6" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=count6>
		<MvASSIGN NAME="count6" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count7 AND dls LT count6}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=place7>
		<MvASSIGN NAME="place7" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=count7>
		<MvASSIGN NAME="count7" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count8 AND dls LT count7}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=place8>
		<MvASSIGN NAME="place8" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=count8>
		<MvASSIGN NAME="count8" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count9 AND dls LT count8}">
		<MvASSIGN NAME="place10" VALUE=place9>
		<MvASSIGN NAME="place9" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=count9>
		<MvASSIGN NAME="count9" VALUE=dls>
	</MvIF>

	<MvIF EXPR="{dls GT count10 AND dls LT count9}">
		<MvASSIGN NAME="place10" VALUE=mycounter>

		<MvASSIGN NAME="count10" VALUE=dls>
	</MvIF>

	<MvASSIGN NAME="mycounter" VALUE="{mycounter + 1}">
</MvWHILE>

<MIVA STANDARDOUTPUTLEVEL="html,text">
<P>
<MvGO NAME=scmalias ROW=place1>
1. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place2>
2. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place3>
3. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place4>
4. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place5>
5. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place6>
6. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place7>
7. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place8>
8. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place9>
9. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>
<MvGO NAME=scmalias ROW=place10>
10. <MvEVAL EXPR="{title}"> with <MvEVAL EXPR="{dls}"> downloads<BR>

[daath]

SELECT TOP x * FROM files ORDER BY downloadtimes DESC

or

SELECT * FROM files ORDER BY downloadtimes DESC LIMIT 0,x

=)

(no reality in my email)

[Anon]

$query = "SELECT thingy FROM table ORDER BY id DESC LIMIT 10";

there u go

[Anon]

Oh ya! Why didn't I think of that? I'm not exactly a newbie PHP'er but I'm not that familiar with MySQL....thanks for your help!

[kylector]

Or you can just do that. I forget that you can use SQL queries.....the power of SQL!! :-)