Mysql Query Question

[YoBro]

Hi,

I have a database that stores in a field the number of views for a product.

I want to write a query that gets the two highest views, so I can then
output the result.

I have this so far, but not sure how to adapt it to get the two highest
views.

mysql_query("SE LECT max(views) FROM stock WHERE status='enabled ' AND photo1
!= ''");

I then plan to loop through the results and echo the photo value for each
result.

I am sure this is easy, but just can't figure out the correct syntax.

Thanks,

YoBro

[Geoff Berrow]

Re: Mysql Query Question

I noticed that Message-ID: <AO5hc.224$_s.2 0808@news.xtra. co.nz> from
YoBro contained the following:
[color=blue]
>I have this so far, but not sure how to adapt it to get the two highest
>views.
>
>mysql_query("S ELECT max(views) FROM stock WHERE status='enabled ' AND photo1
>!= ''");[/color]

order it by views and simply print the top two records
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Michael S. Clark]

Re: Mysql Query Question

YoBro wrote:[color=blue]
> Hi,
>
> I have a database that stores in a field the number of views for a product.
>
> I want to write a query that gets the two highest views, so I can then
> output the result.
>
> I have this so far, but not sure how to adapt it to get the two highest
> views.
>
> mysql_query("SE LECT max(views) FROM stock WHERE status='enabled ' AND photo1
> != ''");
>
> I then plan to loop through the results and echo the photo value for each
> result.
>
> I am sure this is easy, but just can't figure out the correct syntax.
>
> Thanks,
>
> YoBro
>
>[/color]
mysql_query("
SELECT MAX(views) AS view_count
FROM stock
WHERE status = 'enabled'
AND photo1 IS NOT NULL
ORDER BY view_count DESC
LIMIT 0,2
");

I think this is correct but I haven't tested it.

Michael.

[Virgil Green]

Re: Mysql Query Question

"YoBro" <yobro@wazzup.c o.nz> wrote in message
news:AO5hc.224$ _s.20808@news.x tra.co.nz...[color=blue]
> Hi,
>
> I have a database that stores in a field the number of views for a[/color]
product.[color=blue]
>
> I want to write a query that gets the two highest views, so I can then
> output the result.
>
> I have this so far, but not sure how to adapt it to get the two highest
> views.
>
> mysql_query("SE LECT max(views) FROM stock WHERE status='enabled ' AND[/color]
photo1[color=blue]
> != ''");[/color]

Micheal was very close:
mysql_query("
SELECT *
FROM stock
WHERE status = 'enabled'
AND photo1 IS NOT NULL
ORDER BY views DESC
LIMIT 2
");

You don't want to use the max function because that would cause you to
receive only one row... containing just the highest view count and no other
data. The querey above will return the entire row for each of the two
records having the two highest values in views. There is no guarantee what
will happen if more than one two rows match the highest value or if two or
more rows match the second highest value. You might want to have additional
fields specified in the Order By clause to control what happens then.
Perhaps a last viewed date so that the most recently viewed of the most
viewed bubbles to the top.

- Virgil

[YoBro]

Re: Mysql Query Question

Hi,

Thanks for great replies.
And it was so simple, I should have thought have that.

Virgil, thanks for the advice, I will have to add a date last viewed field,
because initially all the stock in the database will be 0. So many are the
same.

Cheers,

YoBro



"Virgil Green" <vjg@obsydian.c om> wrote in message
news:q5ehc.1943 5$Ti1.11267@new ssvr22.news.pro digy.com...[color=blue]
> "YoBro" <yobro@wazzup.c o.nz> wrote in message
> news:AO5hc.224$ _s.20808@news.x tra.co.nz...[color=green]
> > Hi,
> >
> > I have a database that stores in a field the number of views for a[/color]
> product.[color=green]
> >
> > I want to write a query that gets the two highest views, so I can then
> > output the result.
> >
> > I have this so far, but not sure how to adapt it to get the two highest
> > views.
> >
> > mysql_query("SE LECT max(views) FROM stock WHERE status='enabled ' AND[/color]
> photo1[color=green]
> > != ''");[/color]
>
> Micheal was very close:
> mysql_query("
> SELECT *
> FROM stock
> WHERE status = 'enabled'
> AND photo1 IS NOT NULL
> ORDER BY views DESC
> LIMIT 2
> ");
>
> You don't want to use the max function because that would cause you to
> receive only one row... containing just the highest view count and no[/color]
other[color=blue]
> data. The querey above will return the entire row for each of the two
> records having the two highest values in views. There is no guarantee what
> will happen if more than one two rows match the highest value or if two or
> more rows match the second highest value. You might want to have[/color]
additional[color=blue]
> fields specified in the Order By clause to control what happens then.
> Perhaps a last viewed date so that the most recently viewed of the most
> viewed bubbles to the top.
>
> - Virgil
>
>
>[/color]