CODEWITHSUNDEEP
phpmysql
absentx
absentx

Reputation: 1417

How do I write this mysql join query and then order as I need?

I have two tables "widgets" and "clicks", both tables share an ID column.

I need to write a query that joins the widgets and clicks tables by id, and then I need the data returned in an order by clicks, so the widget with the most clicks returned first, and then down the line.

I have at least tried putting the initial query together but I still need help:

       $query = "SELECT widgets.id AS w_id, 
       clicks.id AS c_id, 
       widgets.manf_id AS w_manf,
       widgets.name AS w_name, 
       widgets.widgetlink AS w_link, 
       widgets.type_id AS w_type_id, 
       widgets.image_url AS w_url, 
       clicks.clicks AS c_clicks 
       FROM widgets, clicks WHERE widgets.id = clicks.id ";

Other important info:

Some widgets may not have any clicks, so I need to gather all the info from both tables, then order by amount of clicks for each id.

Looking back, adding a clicks column to the widgets table may have been easier but I think there are other advantages to separation down the road.

Upvotes: 1

Views: 76

Answers (3)

Martyn
Martyn

Reputation: 384

$query = "
SELECT w.id AS w_id, 
   c.id AS c_id, 
   w.manf_id AS w_manf,
   w.name AS w_name, 
   w.widgetlink AS w_link, 
   w.type_id AS w_type_id, 
   w.image_url AS w_url, 
   c.clicks AS c_clicks 
FROM widgets w, click c
WHERE w.id = c.id 
ORDER BY c.clicks DESC";

As simple as that. I'm not sure why you are renaming the fields, but it seems a little bit unnecessary.

In most cases in MySQL you just need to specify what you want from the query. While MySQL will choose the optimal way to execute it and get the results for you.

EDIT: It is always good to limit your results by adding the following line (think of a reasonable amount):

LIMIT 10

Upvotes: 1

Niche
Niche

Reputation: 967

What you want to do may be achieved by using Joins. You can SELECT the columns you want from both tables and join them by widget_id so that all widgets are shown, independently of the amount of clicks:

$query = "SELECT w.id AS w_id, 
                 c.id AS c_id, 
                 w.manf_id AS w_manf,
                 w.name AS w_name, 
                 w.widgetlink AS w_link, 
                 w.type_id AS w_type_id, 
                 w.image_url AS w_url, 
                 c.clicks AS c_clicks 
          FROM widgets w
          LEFT JOIN clicks c ON w.id = c.id 
          ORDER BY c.clicks DESC";

This, of course, assuming that the clicks.clicks columns stores the amount of clicks in a widget.

Extra

You could also consider storing every individual click, adding a datetime attribute and a primary key using a unique identifier for every click (or a compound key from the datetime, ip address and id attributes) so you can track individually every hit on every widget. Then you could select the amount of clicks for every widget using a COUNT() in the SELECT string. Depends on what you're trying to do with your database.

Upvotes: 2

Þaw
Þaw

Reputation: 2057

can you post your tables and indicate the fields you want to display? You could do something like this. 

SELECT fields a, fields b
FROM widgets a JOIN clicks b
ON a.id = b.id
ORDER BY clicks

Upvotes: 0

Related Questions