jiser Posted June 29, 2007 Share Posted June 29, 2007 Hi, I got this problem. I got two tables forming one array of information (products and pricing). They are linked to each other using the field productid. The output of this array should however be sorted. This should be sorted by the amount of times the value of productid can be found in order_details in the column productid. However, only the entries in order_details not older than 90 days should be taking into the sorting. The date can be found at the orders table. The relation between the orders table and order_details table is orderid. Does anyone know how this query will look like? Best regards Michael Link to comment https://forums.phpfreaks.com/topic/57783-formulating-a-quite-complicated-sql-query/ Share on other sites More sharing options...
bubblegum.anarchy Posted June 29, 2007 Share Posted June 29, 2007 Define a column based on the outlined conditions and sort accordingly. The column definition could include an IF ( order_details.date > CURRENT_DATE - INTERVAL 90 DAY, count(productid), 0 ) so that the value is 0 if the date is older than 90 days. Link to comment https://forums.phpfreaks.com/topic/57783-formulating-a-quite-complicated-sql-query/#findComment-286246 Share on other sites More sharing options...
jiser Posted June 29, 2007 Author Share Posted June 29, 2007 I forgot to mention: the output should be limited with the value of the current categoryid ($cat = "4654"). This value is to be looked up in the table product_categories. The table product_categories is linked up with the products table using the productid. Pretty complicated right? ??? Link to comment https://forums.phpfreaks.com/topic/57783-formulating-a-quite-complicated-sql-query/#findComment-286247 Share on other sites More sharing options...
jiser Posted June 29, 2007 Author Share Posted June 29, 2007 Hi, Unfortunately the products which can't be found in order_details should also be put in this array. Only at the bottom of it... It's just a query to put the products, which are most sold in the last 90 days, at the top of the array. Many thanks Michael Link to comment https://forums.phpfreaks.com/topic/57783-formulating-a-quite-complicated-sql-query/#findComment-286251 Share on other sites More sharing options...
fenway Posted July 1, 2007 Share Posted July 1, 2007 How does ba's solution not help? Link to comment https://forums.phpfreaks.com/topic/57783-formulating-a-quite-complicated-sql-query/#findComment-287037 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.