Results 1 to 3 of 3
  1. #1

    Query result -> array for looping

    I have inherited an application that tracks certain server and account information. The application appears to have been written hastily for the needs of the original owner, and I want to extend it, but my php/mySQL skills are - to be very generous about it - quite rusty.

    There are two tables for my question: the first table has the servername, and other information associated with that server (these fields are unimportant for the particular operation I want). The second table also has a servername field, with other information associated with an account (again, these fields are unimportant).

    What I want to do: I want to read in the server names from the first table, then loop through the server names and get a count from the second table of how many records there are for that server. For instance, if there are three records for accounts on serverone, and two records for accounts on servertwo, I'd like something like this:

    Serverone total: 3
    Servertwo total: 2
    Total all servers: 5

    My problem: a for loop requires that the values be in an array, and this has me stumped for reasons other than a lack of caffeine or junk food. I just can't fgure out how to write the query so that it gives me the data I'm after.

    Opinions needed: is a for loop (for each servername) the best way to go? If so, how would I write this query? If this is not the best or most efficient way, what would be better?

    Assumptions: there are several hundred servers and tens of thousands of accounts in the database, and the database will continue to grow rapidly. PHP 4.x and mySQL client 4.1.x are in use.


  2. #2
    Join Date
    Nov 2001
    Assuming there isn't a ID column serving as a relation between table1 and table2:
    SELECT  t2.server_name, count(t2.server_name) 
        FROM table_one t1, table_two t2 
        WHERE t2.server_name = t1.server_name 
        GROUP BY t2.server_name;
    If you want your results ordered by the number of occurrences add to the end of the query string above:
        ORDER BY count DESC;
    You'll get:

    servernameA 12
    servernameB 3
    servernameC 15

    etc., which you can then deal with fairly easily I hope.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  3. #3
    Thanks, I'll give that a go.

Posting Permissions

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