order by number of rows in anther table?

[Jas]

So, I have two tables. The first is a list of websites, like so:



Table1:
id | url
1 | www.site1.com
2 | www.site2.com
3 | www.site3.com



Table2:
id | site | ip | date
1 | 1 |127.0.0.1|2008-08-11
2 | 2 |127.0.0.1|2008-08-11
3 | 1 |127.0.0.1|2008-08-11
4 | 2 |127.0.0.1|2008-08-11
5 | 1 |127.0.0.1|2008-08-11
6 | 3 |127.0.0.1|2008-08-11


What I want to do it pull up the websites from table1 and order them based on the number of rows in table2 where `table1`.id = `table2`.site

Any ideas?

[Nile]

I don't know what your trying to make, but I think your trying to make something like, vote for a site.
Would this work:


for($co=0;$co<=strlen($row);$co++){
for($oc=0;$oc<=strlen($row2);$co+){
if(array_exists($array[$row[$co]])){
$array[$row[$co].rand(0,9)] = $oc;
}
else {
$array[$row[$co]] = $oc;
}
}
}

I have go no clue if that will work, even if its right. You'll have to oil it a lot Jas, but with that. The table arrays are kinda combined. Once you oil it up, if you dare to, lol. I'd run an array_dump() on it so you can see what variables you got, and don't got.

[boogyman]

What I want to do it pull up the websites from table1 and order them based on the number of rows in table2 where `table1`.id = `table2`.site
you cannot order something by the number of rows... what you might be trying to do is having all of the similar sites together? and then list order then according to site id? or something like that? if that is correct, you can use this query


SELECT t1.id, t1.url, t2.id AS something_id, t2.site, t2.ip, t2.date FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t2.site=t1.id GROUP BY t2.site ORDER BY t1.id ASC

Note that I changed the t2.id to something_id because you already have a field recording the id from Table1


GROUP BY t2.site
The above statement will put all of the same site ids together so you would end up with something that could resemble


Table2:
id | site | ip | date
1 | 1 |127.0.0.1|2008-08-11
3 | 1 |127.0.0.1|2008-08-11
5 | 1 |127.0.0.1|2008-08-11
6 | 3 |127.0.0.1|2008-08-11
2 | 2 |127.0.0.1|2008-08-11
4 | 2 |127.0.0.1|2008-08-11


then to sort them a certain way we use the following statement

ORDER BY t1.id ASC

which will sort the results by the site id that you have specified in the first table, and I am sorting them in ascending (ASC) order, but you can also sort in descending (DESC) order which would look like

ORDER BY t1.id DESC

so our final results would look something like


Table2:
id|url |something_id | site | ip | date
1 |www.site1.com|1 | 1 |127.0.0.1|2008-08-11
1 |www.site1.com|3 | 1 |127.0.0.1|2008-08-11
1 |www.site1.com|5 | 1 |127.0.0.1|2008-08-11
2 |www.site2.com|4 | 2 |127.0.0.1|2008-08-11
2 |www.site2.com|2 | 2 |127.0.0.1|2008-08-11
3 |www.site3.com|6 | 3 |127.0.0.1|2008-08-11


if tat is not what you want, then can you possibly try to explain again what you are looking for

[Jas]

Sorry, guys, but neither of those is even close to what I need. But I refuse to believe that this is impossible with MySQL-- surely the query language has something in it that can help. What does the COUNT() function do? I was looking into that, or maybe the row_count() function with a sub query? I don't know much about either function, and I've never had a MySQL problem so complex.

This script is part of the link exchange program for my personal website. The objective is to see what sites are generating the most traffic into my site, and order them from most traffic recieved to least. (Table1 is the list of sites, table2 is the traffic record for the sites.)

If I can't do this with MySQL alone, how can I do it with PHP? I wouldn't want to have to have a really long loop to calculate the top sites, and for some reason (probably frustration) I can't come up with any ideas. :(

And, thanks for the replies so far :) I was afraid that a problem like this wouldn't get much help.'

EDIT: I just realized I never said what table two was for in the original post. Sorry guys.

[Jas]

Stand by, I think I may have done it with GROUP BY and ORDER BY COUNT(). I need to test it a little more, but I'll post the solution if it works :)

[Jas]

Blast! Another problem, and I forgot the syntax to fix it.

Suppose these are the tables:


table1
id|something
1 | la la la
2 | la la la
3 | la la la

table2
number|Name
1 |something
1 |something else
2 | more stuff


How can I get it to show table1's 3rd row with this:



SELECT table1.id, table2.name FROM table1, table2 WHERE table1.id = table2.number


?

I thought I just had to add an "|| table1.id = NULL" to the end, but that doesn't work. I've done this before, but I don't remember how anymore.