SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just a quickie...

    I'd like to count how many times certain text occurs in one of the tables.

    basically, I have a list of people and I want to show how many times database entries each one of them has.

    So here's the part where I list the people:

    PHP Code:
    $limit_str "LIMIT "$cur_page 10 .",10";
    $result mysql_query("SELECT * FROM agents $limit_str",$link);
    while (
    $a_row =mysql_fetch_array ($result))
    {
    print 
    "agent: $a_row[id]";

    (I'm limiting display to 10 people per page)

    So where it says "agent:... I'd like to show how many pieces of data are associated with that agent.

    Like - agent: 1 total listings: 10

    Each listing in "homes" has a field "agent" that is related to an "agents" table.

    Does that make sense at all? Everything that I've tried just gives the total number of rows in the db.

    Any suggestions are greatly appreciated

    Thanks!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  2. #2
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am not quite sure on your table structure, but here is a stab:

    PHP Code:
    $aid $a_row["id"];
    $result mysql_query("SELECT COUNT(*) FROM homes WHERE agent=$aid"); 
    Then the number in the result is the number of rows in homes which have that particular agent id.

    You'd have a column in 'homes' called 'agent' or similar with the agent's id. It should be indexed, too.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff

  3. #3
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just plugged in what you suggested but I had to change it a bit to fit the table structure. Problem is now is that $aid displays the same number as $a_row[id]

    I'm assuming I should be putting your code inside of the while loop correct?

    PHP Code:
    $result mysql_query("SELECT * FROM agents $limit_str",$link);

    while (
    $a_row =mysql_fetch_array ($result))
    {
    $aid $a_row[id];
    $tmp mysql_query("SELECT COUNT(*) FROM homes WHERE owner=$aid");

    print 
    "Agent: $a_row[id] Listings: $aid ";

    Here's a snipped of my table dump structure:

    CREATE TABLE agents (
    id int(11) NOT NULL auto_increment,
    PRIMARY KEY (id)
    );

    CREATE TABLE homes (
    id int(11) NOT NULL auto_increment,
    owner int(11) NOT NULL,
    PRIMARY KEY (id)
    );

    - owner in table "homes" relates to "id" in table "agents"
    - "id" in table homes is just a counter

    I'm not sure if all the stuff that I included above helps so if you need anything else, please ask.

    Your help is greatly appreciated!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You want to use a left outer join (because I am assuming that an agent may have NONE OR MANY homes) and you will want your result set to include any agents who have no listings (as in, for example, agent 2 : total listings 0).

    SELECT agents.id, COUNT(homes.id)
    FROM agents LEFT JOIN homes ON agents.id = owner
    GROUP BY agents.id
    ORDER BY agents.id
    LIMIT 0, 10

    Otherwise, if you don't want your result set to include agents who have no listings then a normal inner join would suffice

    SELECT agents.id, COUNT(homes.id)
    FROM agents, homes
    WHERE agents.id = owner
    GROUP BY agents.id
    ORDER BY agents.id
    LIMIT 0, 10

  5. #5
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm thinking your first example is the right way to go. However, since I'm pretty new to php/mysql, your code goes right over my head. I don't understand the logic.

    Would I stick your code in the while loop that writes the agent number?

    What do I use to actually print out the result of the query?

    So I make it:
    $result = mysql_query("SELECT * FROM agents $limit_str",$link);

    while ($a_row =mysql_fetch_array ($result))
    {
    $tmp = mysql_query("... your code...");

    ...

    Agent number: $a_row[id] Total Listings ??

    Sorry for my lack of knowledge on this one. Its the only way to learn I guess.

    Thanks again for your help!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is how I would code it. Note that this time, I have created aliaises (... AS aid) in the sql query to make it easier to extract the data from an associative array later.
    PHP Code:
    $sql "SELECT agents.id AS aid, COUNT(homes.id) As listings 
          FROM agents LEFT JOIN homes ON agents.id = owner 
          GROUP BY agents.id 
          ORDER BY agents.id
          LIMIT*"
    .*$cur_page***10*",10";

    $result mysql_query($sql$link);
    while(
    $row mysql_fetch_array($result)) {
       echo 
    'Agent number: ' $row["aid"] . " Total Listings: ' . $row["listings"];

    Here is some test data I used to test the query I posted yesterday
    (using the schema you posted).
    Code:
    INSERT INTO agents(id) 
    VALUES (1), (2), (3), (4), (5);
    
    INSERT INTO homes (id, owner)
    VALUES (1, 1),
           (2, 2),
           (3, 2),
           (4, 3),
           (5, 3),
           (6, 3);
    I don't have my development server running right this minute, but the expected results of the sql query (the left outer join one I have used in the code above) would be:
    Code:
    aid   listings
    ---------
    1      1
    2      2
    3      3
    4      0
    5      0

  7. #7
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just plugged in the code that you gave and it sort of works - I think the problem is 'cuz I stuck your code within the other while loop for the other sql query. So the numbers are getting messed up. Do I even need the first loop anymore? Can I just add the query's from the other loop into yours?

    So what I have now prints all the print "text.."; stuff out the number of agents * the number of listings.

    For example...

    agent # 2 name: hello
    listings: 1
    agent #3 name: hello
    listings:1
    agent #4 name: hello
    listings: 0
    agent #2 name: agent2
    listings: 1

    So the agents get messed up from the loop and every agent gets printed multiple times.

    Here's what I have:

    PHP Code:
    $limit_str "LIMIT "$cur_page 10 .",10";
    $result mysql_query("SELECT * FROM agents $limit_str",$link);
    while (
    $a_row =mysql_fetch_array ($result))
    {
                    
    $sql "SELECT  agents.id AS aid, COUNT(homes.id) As listings 
          FROM agents LEFT JOIN homes ON agents.id = owner 
          GROUP BY agents.id 
          ORDER BY agents.id
          LIMIT "
    $cur_page 10 ",10";

    $resultq mysql_query($sql$link);
    while(
    $row mysql_fetch_array($resultq)) {

    echo (
    'Agent number: ' $row["aid"] . ' Total Listings: ' $row["listings"]);
    }
    }

    thanks for your time!
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, no, no! The for loop is unecesary.

    Let me explain my code.

    first, I create a string $sql which holds the required sql query including your desire to limit the result set to ten records per page.

    Next, I execute that query using mysql_query();

    Third, I iterate through the result set and print out each row (agent/listings)

    That's it! I am only going by the exact schema (table definitions) you have given. How can I go by anything else? Now in your last post you have thrown a "name" into the mix. OK, let me assume that "name" is a field in your agents table. I can only assume because only you know Then I would use this code:
    PHP Code:
    $sql*=*"SELECT*agents.id*AS*aid,*name, COUNT(homes.id)*As*listings*
    ******FROM*agents*LEFT*JOIN*homes*ON*agents.id*=*owner*
    ******GROUP*BY*agents.id*
    ******ORDER*BY*agents.id
    ******LIMIT*"
    .*$cur_page***10*.*",10";
    $result*=*mysql_query($sql,*$link);
    while(
    $row*=*mysql_fetch_array($result))*{
    ***echo*'Agent*number:*'*.*$row["aid"]*.*
                    
    ' Name: ' $row["name"] . 
                   
    '*Total*Listings:*'*.*$row["listing"];

    All the magic is in the sql statement I have crafted. I know it seams mysterious to you, but as I indicated in my previous post, your schema, plus my test data and sql produces the result set I posted. SQL is real fun and increadably powerful. And MySQL doesn't even come close to implimenting the full power of ANSI SQL.

  9. #9
    SitePoint Addict
    Join Date
    Sep 2000
    Location
    Ontario, Canada
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK... it sunk in now. And it works great! I understand how your code works now. Thank you for your time an patience!!! I realize it must be annoying to try to get beginners to understand how things work.

    Thanks again.
    Jason Dulberg
    Extreme MTB
    http://extreme.nas.net

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's my pleasure. I have found these forums to be friendly, helpfull and educative, but rarely annoying We are all experts at some things and complete novices at other things. I'm happy if I helped.

  11. #11
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Blah - vB playing up.
    Last edited by freakysid; Apr 17, 2001 at 17:01.


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
  •