Sorting method with MySQL

[virruss2]

Hi,

 

I need to sort a list of webpages by the number of visitors, let's say, for today (like displaying today's most popular pages).

 

I have 2 tables in my database:

- the table containing the webpage info (like its url, title, content, unique id)

- the traffic table containing the number of daily visitors for each webpage.

 

What I need is to sort the webpages by the number of visitors. What's the best method to do that (I need an efficient one also... since the script is going to deal with a lot of webpages and I want to adapt the script for monthly visitors and all time visitors...) ? But, if anyone can give me an example of doing it with bubble sort, it's ok, even if it's not that efficient... I just need to figure out how to work with that array...

 

Can you give me a a basic sample of how the code will look like? It's not that I am lazy, but I'm not sure how to work with the array containing 2 pieces of information: webpage id and number of visitors.

 

Thank you in advance.

[Stooney]

The sorting can be done in the query itself.  Of course you will need to put in your fields/tables but that's the basic idea.

 

$result=mysql_query("SELECT * FROM tablename ORDER BY numvisit ASC");

 

 

[cooldude832]

looking to write a JOIN query since you look like u haven't done one yet I'll show you this one (is unique id the primary key (call it UrlID to make it easy to see)

Also does UrlID link the first table to the second?

 

SELECT
T1.Url as Url,
T1.UrlID as UrlID,
T1.Title as Title,
(SELECT COUNT(*) from `T2` GROUP BY UrlID) as Hits

FROM `UrlTable` as T1

LEFT JOIN `TrafficData` as T2 ON (UrlTable.UrlID = TrafficData.UrlID)

GROUP BY T1.UrlID

ORDER BY Hits

 

take it into phpmyadmin and tweak it its the basic idea though  (not checked might be a bit wrong)

 

Edit:

 

If the daily hits is an integer alternatively you could use this using the SUM concept

SELECT
T1.Url as Url,
T1.UrlID as UrlID,
T1.Title as Title,
SUM(T2.Hits) as Hits

FROM `UrlTable` as T1

LEFT JOIN `TrafficData` as T2 ON (UrlTable.UrlID = TrafficData.UrlID)

GROUP BY T1.UrlID

ORDER BY Hits

[virruss2]

I forgot to mention that the traffic should be calculated for each query... because the traffic table contains the unique visitors by day, so I can display daily stats.

 

I cannot just sort by number of visitors... I don't know how clear I am (sorry, I'm not an native English speaker...).

[cooldude832]

show us some exact table structure with some sample data and it would be helpful

[virruss2]

Ok, thank you for your fast answers!

 

"webpages" table:

 

id      |  url      |  title       |     content

----------------------------------------

1       | /page  | First page | This is the first page/'s content

2      | /page2 | 2nd page  | Some content for the second page

 

 

"traffic" table:

 

id      |     page_id     |     date     |     visitors

---------------------------------------------

1       |         1         |    1/08/08  |       20

2       |         1         |    2/08/08  |       5

3       |         2         |    1/08/08  |       9

 

 

And, if I need to sort by this week's traffic, I need to sum up all the visitors for that webpage for this week before I can sort by the number of visitors.

 

cooldude832, will your example work in this case? Thank you.

[cooldude832]

here is mine modded up to fit it
[code]
SELECT
T1.url as Url,
T1.id as UrlID,
T1.title as Title,
SUM(T2.visitors) as Hits

FROM `url` as T1

LEFT JOIN `traffic` as T2 ON (url.id = traffic.page_id)

Where traffic.date >= NOW()-(60*60*24*7)

GROUP BY T1.UrlID

 

The date part can be a mysql variable

 

try it in phpmyadmin

 

ORDER BY Hits

[/code]

[virruss2]

here is mine modded up to fit it
[code]
SELECT
T1.url as Url,
T1.id as UrlID,
T1.title as Title,
SUM(T2.visitors) as Hits

FROM `url` as T1

LEFT JOIN `traffic` as T2 ON (url.id = traffic.page_id)

Where traffic.date >= NOW()-(60*60*24*7)

GROUP BY T1.UrlID

 

The date part can be a mysql variable

 

try it in phpmyadmin

 

ORDER BY Hits

[/code]

 

I guess this should work!

 

Thank you very much! I really appreciate. And it's a lot shorter than I've thought!

[virruss2]

Maybe this will sound stupid, but, I usually use use a different sintax for my select query...

 

...like this one:

 

$select= mysql_query("SELECT *

FROM `webpages`

WHERE `status` = 'active'

ORDER BY `id` DESC

");

 

Can someone explain to me what "T1" is? Is it the name of the table ("webpages")? If "T1" and "T2" are the names of the tables, than, what's with that "traffic"? Or, maybe, you know a link where I can read about more complex queries... Sorry, I guess it's already been an hour since I'm trying to figure it out by myself... Thank you again.

 

Later edit: I think I've got it...

[Barand]

if you want to see those with no visitors

SELECT p.title, SUM(t.visitors) as visitortotal
FROM webpages p
LEFT JOIN traffic t ON p.id = t.page_id AND t.date BETWEEN CURDATE()-INTERVAL 7 DAY AND CURDATE()
GROUP BY p.title
ORDER BY visitortotal DESC

[cooldude832]

T1 and T2 are pseudo  names for the tables since I didn't know there names at the time

 

using the `Tablename` as var syntax allows this