Order MYSQL query based on another MYSQL querys results

Question

I have 2 sql queries, the first selects products from the database based on the AREA and CATEGORY

Its a furniture store, and this query is for Dining Tables.

SELECT p.id,
   p.product_name,
   p.range_name,
   p.pic_url,
   p.hits,
   p.manufacturer,
   p.standard_price,
   p.sale_price,
   p.offer_price,
   p.discount,
   p.display_manufacturer,
   p.display_price,
   p.visible,
   p.display_model_results,
   p.model,
   p.subcat,
   p.disable_options,
   p.request_price,
   p.corner_flag,
   cf.flag_name,
   cf.flag_url,
   cf.flag_description,
   p.display_model,
   p.offer_status,
   p.disable_options_results,
   p.added_timestamp,
   p.about,
   p.keywords
FROM   products p,
   corner_flags cf
WHERE  ( p.area = 'Dining Room Furniture'
      OR p.additionarea = 'Dining Room Furniture' )
   AND ( p.subcat = 'Dining Tables'
          OR p.additionsubcat = 'Dining Tables' )
   AND p.sale_price = ''
   AND p.visible = '1'
   AND p.corner_flag = cf.flag_id
   AND ( p.sale_price = ''
          OR p.offer_price = '0' )
   AND p.visible = '1'
 ORDER  BY ( p.hits ) DESC  

Every time a product is viewed the field 'hits' in the product table it increased by 1. This was ok at first but now new products are always at the bottom of the list if the customer decides to order the results by popularity.

We have another table which tracks every click made on our site so ive wrote a query to count how many times a particular product was view with the last 2 week for example.

SELECT Count(*) AS total,
   vtp.product_id
FROM   visitor_tracking_pages vtp,
   products p
WHERE  vtp.product_id = p.id
   AND p.subcat = 'Dining Tables'
   AND p.visible = '1'
   AND vtp.last_click >= '1380153600'
GROUP  BY vtp.product_id
ORDER  BY total DESC 

This query gives the product id and the total hits received in the last 2 weeks.

I just need a way to combine these two queries into one. Obviously not every product will have been viewed within the last 2 week period so id need a way of returning 0 if that makes any difference?

Ive looked at the various JOINS but im not familiar with how to write them.

Any help for you greatly appreciated!

Answer 1

is there any way i can further filter the hit count? ... Basically some visitors flick back and forth between products multiple times which show up as the product receiving more views that it really has.

a query in the visitor_tracking_pages table for a product shows its been viewed a total of 7 times, but its actually only been view by 5 unique visitors.

SELECT ip_address, product_id
FROM  `visitor_tracking_pages` 
WHERE  `product_id` =  '1562805'
AND  `last_click` >=  '1380153600'

| ip_address   | product_id |
-----------------------------
| 192.168.0.1  | 1562805    |
| 192.168.0.1  | 1562805    |
| 192.168.0.2  | 1562805    |
| 192.168.0.3  | 1562805    |
| 192.168.0.4  | 1562805    |
| 192.168.0.1  | 1562805    |
| 192.168.0.5  | 1562805    |
-----------------------------

Answer 2

Try this

SELECT p.id,
   p.product_name,
   p.range_name,
   p.pic_url,
   p.hits,
   p.manufacturer,
   p.standard_price,
   p.sale_price,
   p.offer_price,
   p.discount,
   p.display_manufacturer,
   p.display_price,
   p.visible,
   p.display_model_results,
   p.model,
   p.subcat,
   p.disable_options,
   p.request_price,
   p.corner_flag,
   cf.flag_name,
   cf.flag_url,
   cf.flag_description,
   p.display_model,
   p.offer_status,
   p.disable_options_results,
   p.added_timestamp,
   p.about,
   p.keywords,
   IFNULL(vtp.last_two_week_hits, 0) as last_two_week_total_hits
FROM corner_flags cf INNER JOIN  products p
     ON cf.flag_id = p.corner_flag
    LEFT JOIN
       (SELECT v.product_id as product_id,Count(*) as last_two_week_hits
            FROM   visitor_tracking_pages v
           WHERE v.last_click >= '1380153600' group by v.product_id 
        ) as vtp ON vtp.product_id = p.id
WHERE  p.visible = '1' AND ( p.area = 'Dining Room Furniture'
      OR p.additionarea = 'Dining Room Furniture' )
   AND ( p.subcat = 'Dining Tables'
          OR p.additionsubcat = 'Dining Tables' )
   AND ( p.sale_price = ''
          OR p.offer_price = '0' )
 ORDER BY last_two_week_total_hits DESC  

Answer 3

Thanks for all the help!

Ravi - Your optimization worked but for some reason it stopped returning the products that hadnt been view in the last 14 days.

In the end i finally figured a working solution.

If you see any further improvements though, let me know :)

I created a new table called popular_products and wrote a script to populate this table on a daily basis (via cron job) with the new hit count from querying the visitor_tracking_pages table.

TRUNCATE TABLE to remove the previous days results before adding the new ones.

$timestamp = strtotime("-2 week");

mysql_query("TRUNCATE TABLE  `popular_products`");

$result = mysql_query("SELECT COUNT(*) AS total, product_id 
FROM visitor_tracking_pages vtp 
WHERE  vtp.product_id != '' AND vtp.last_click >= '$timestamp' 
GROUP BY product_id");

while($row = mysql_fetch_array($result)){

mysql_query("INSERT INTO popular_products (product_id, total_views) 
VALUES ('$row[1]','$row[0]')");

}

This then allowed me to add it in the results without having a run a query on-the-fly.

The products that haven't been viewed in the last 2 weeks return as NULL which is fine.

SELECT p.id, 
   p.product_name, 
   p.range_name, 
   p.pic_url, 
   p.hits, 
   p.manufacturer, 
   p.standard_price, 
   p.sale_price, 
   p.offer_price, 
   p.discount, 
   p.display_manufacturer, 
   p.display_price, 
   p.visible, 
   p.display_model_results, 
   p.model, 
   p.subcat, 
   p.disable_options, 
   p.request_price, 
   p.corner_flag, 
   cf.flag_name, 
   cf.flag_url, 
   cf.flag_description, 
   p.display_model, 
   p.offer_status, 
   p.disable_options_results, 
   p.added_timestamp, 
   p.about, 
   p.keywords, 
   (SELECT pp.total_views 
       FROM popular_products pp 
       WHERE pp.product_id = p.id 
   ) AS total_views 
FROM products p, corner_flags cf 
WHERE p.visible='1' 
   AND (p.AREA='Dining Room Furniture' OR p.AdditionAREA='Dining Room Furniture') 
   AND (p.SUBCAT='Dining Tables' OR p.AdditionSUBCAT='Dining Tables') 
   AND p.corner_flag = cf.flag_id 
   AND p.sale_price= '' 
ORDER BY ABS(total_views) DESC

Answer 4

I think this solves your problem, the RIGHT JOIN:

SELECT COUNT(*) AS total, p.id as product_id
FROM visitor_tracking_pages vtp RIGHT JOIN products p ON p.id=vtp.product_id
WHERE p.SUBCAT = 'Dining Tables' AND p.visible = '1' AND (vtp.last_click >= '1380153600' or vtp.last_click IS NULL)
GROUP BY p.id
ORDER BY total DESC

The RIGHT (or LEFT) JOIN performs the standard JOIN, like in the query you've written, and then adds all rows in the right (or left) table that where not matched by the join.

To combine the two queries you posted, you can do this query

SELECT p.id, p.product_name, p.range_name, p.pic_url, p.hits, p.manufacturer, p.standard_price, p.sale_price, p.offer_price, p.discount, p.display_manufacturer, p.display_price, p.visible, p.display_model_results, p.model, p.SUBCAT, p.disable_options, p.request_price, p.corner_flag, cf.flag_name, cf.flag_url, cf.flag_description, p.display_model, p.offer_status, p.disable_options_results, p.added_timestamp, p.about, p.keywords
FROM products p, corner_flags cf LEFT JOIN visitor_tracking_pages vtp ON vtp.product_id=p.id
WHERE (p.AREA='Dining Room Furniture' OR p.AdditionAREA='Dining Room Furniture') AND (p.SUBCAT='Dining Tables' OR p.AdditionSUBCAT='Dining Tables') AND p.sale_price='' AND p.visible='1' AND p.corner_flag = cf.flag_id AND (p.sale_price= '' OR p.offer_price = '0') AND p.visible='1'
AND (vtp.last_click >= '1380153600' or vtp.last_click IS NULL)
GROUP BY p.id
ORDER BY COUNT(*), p.hits DESC

I know it is not the perfect way because canonically the only fields allowed in the field list are that ones mentioned in the GROUP BY clause, but it works and you can use COUNT(*) in the ORDER BY clause.

Another way to do what you need is to create a view with the first query I wrote, for example *view_order* and then use it as a number to avoid every kind of problem using GROUP BY, with this query:

SELECT p.id, p.product_name, p.range_name, p.pic_url, p.hits, p.manufacturer, p.standard_price, p.sale_price, p.offer_price, p.discount, p.display_manufacturer, p.display_price, p.visible, p.display_model_results, p.model, p.SUBCAT, p.disable_options, p.request_price, p.corner_flag, cf.flag_name, cf.flag_url, cf.flag_description, p.display_model, p.offer_status, p.disable_options_results, p.added_timestamp, p.about, p.keywords
FROM products p, corner_flags cf JOIN view_order vtp ON vtp.product_id=p.id
WHERE (p.AREA='Dining Room Furniture' OR p.AdditionAREA='Dining Room Furniture') AND (p.SUBCAT='Dining Tables' OR p.AdditionSUBCAT='Dining Tables') AND p.sale_price='' AND p.visible='1' AND p.corner_flag = cf.flag_id AND (p.sale_price= '' OR p.offer_price = '0') AND p.visible='1'
ORDER BY vtp.totale, p.hits DESC