Results 1 to 6 of 6

Thread: order by number of rows in anther table?

  1. #1
    Join Date
    Jan 2007
    Posts
    629
    Thanks
    10
    Thanked 28 Times in 28 Posts

    Question order by number of rows in anther table?

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

    Code:
    Table1:
    id | url
    1  | www.site1.com
    2  | www.site2.com
    3  | www.site3.com
    Code:
    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?
    --Jas
    function GreatMinds(){ return "Think Like Jas"; }
    I'm gone for a while, but in the meantime: Try using my FTP script | Fight Bot Form Submissions

  2. #2
    Join Date
    Jan 2008
    Posts
    4,168
    Thanks
    28
    Thanked 628 Times in 624 Posts
    Blog Entries
    1

    Default

    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:
    PHP Code:
    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.
    Jeremy | jfein.net

  3. #3
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    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

    Code:
    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

    Code:
    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
    Code:
    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

  4. #4
    Join Date
    Jan 2007
    Posts
    629
    Thanks
    10
    Thanked 28 Times in 28 Posts

    Default

    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.
    Last edited by Jas; 08-12-2008 at 05:18 PM.
    --Jas
    function GreatMinds(){ return "Think Like Jas"; }
    I'm gone for a while, but in the meantime: Try using my FTP script | Fight Bot Form Submissions

  5. #5
    Join Date
    Jan 2007
    Posts
    629
    Thanks
    10
    Thanked 28 Times in 28 Posts

    Default

    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
    function GreatMinds(){ return "Think Like Jas"; }
    I'm gone for a while, but in the meantime: Try using my FTP script | Fight Bot Form Submissions

  6. #6
    Join Date
    Jan 2007
    Posts
    629
    Thanks
    10
    Thanked 28 Times in 28 Posts

    Default

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

    Suppose these are the tables:
    Code:
    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:

    Code:
    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.
    --Jas
    function GreatMinds(){ return "Think Like Jas"; }
    I'm gone for a while, but in the meantime: Try using my FTP script | Fight Bot Form Submissions

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •