[SOLVED] MYSQL - Order By (Count of Values from Other Table)

[NeoMarine]

Hello ,

 

Got myself in a bit of a pickle here!  ??? ??? ???

 

What I would like to do is, determine the total views for each product of mine and Sort the Products by the Total Views.

 

The views are recorded in a separate table, where as the Products are listed in another. Normally, I would be able to find the total amount of views for the product by just counting (count(*)) the number of Views where the Product ID matches that in the Products table... however, since these values are not stored in the same table I cant seem to do an Order By for the values...

 

What I need is something like this (that would actually work):

 

SELECT * FROM tbl_products ORDER BY tbl_views.count(where tbl_views.ProductID=tbl_products.ID)

 

--- Essentially I have a log of all the views which records the product ID and the date it was added + IP address...

--- I also have the Products, which needs to be sorted by the total amount of views for that product WITHIN mysql...

 

I realise it can be done easily through PHP, however the system I have so far is build in such a way that it is sorted through mysql, and changing it now would be a hastle...

 

The only other alternative I can think of is, (if nobody can work this one out :'() would be to have a Views field in the Products table, and then I iterate through all the Views logs and get the total views and then store it in that product table for each product... this of course would be extremely messy and sluggish (at least with many records).

 

~ I appreciate your help in this, rather confusing matter. Thank you!

 

[mezise]

Hi,

 

however, since these values are not stored in the same table I cant seem to do an Order By for the values...

Yes, you can. You just need to:

SELECT COUNT(*) AS alias_count
...
GROUP BY tbl_products.ID
ORDER BY alias_count

 

Michal

[NeoMarine]

Thank you!

 

Solved!! Here is what I am using now (well, something similar anyway):

 

SELECT tbl_products.ID, (SELECT Count(tbl_productviewslog.productID) FROM tbl_productviewslog WHERE tbl_productviewslog.productID=tbl_products.ID) AS alias_count FROM tbl_products, tbl_productviewslog GROUP BY tbl_products.ID ORDER BY alias_count DESC

 

What needed to be done was combine your Grouping and a selection with it's own parameters. As you know I am not very knowledgeable at mysql code in general.

 

Anyway, thank you Michal - I hope this code will help other viewers with similar confusions.