SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    who's the biggest pimp?

    hi i want to do a top player table for my game, you play as a pimp, and get hoes..

    but the hoes you get are stored in a different database, one row per hoe..

    how can i select all the hoes, and with the value `owner` find out who are the top 5 pimps and how much hoes they have..?

    if more info is needed please say so..

    thanks alot!
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to do a SQL "join". What DB are you using? MySQL?

  3. #3
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes mysql..
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  4. #4
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool - have a look at the samples in the documentation here:
    http://dev.mysql.com/doc/refman/4.1/en/join.html

    You should be able to create a SELECT easily enough to do what you want. Let us know if you're struggling and post your SQL string so we can see what you've tried.

    Cheers,

    G

  5. #5
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i dont think this is what i need here...

    lets say this is the `hoes` table:

    id - name - quality - cash - owner - picture
    1 - Jessica - 5 - 300 - 1 - 1.jpg
    2 - Emily - 1 - 300 - 1 - 2.jpg
    3 - Faith - 3 - 300 - 1 - 3.jpg
    4 - Amber - 2 - 300 - 2 - 4.jpg
    5 - Elena - 4 - 300 - 2 - 5.jpg

    so the result should show that the pimp with id 1 has 3 hoes, and id 2 has 2 hoes... this should be limited for the top 5 pimps, and then select from table `pimps` the pimp with the id (owner) and show this:

    King-Pimp has 17 hoes
    sonic has 13 hoes
    pimpmasta2006 has 10 hoes
    deadboi77 has 7 hoes
    small pimp has 5 hoes
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  6. #6
    SitePoint Wizard Pedro Monteiro's Avatar
    Join Date
    Sep 2002
    Location
    Lisbon
    Posts
    1,393
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For Christ Sake

  7. #7
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehehe what?
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  8. #8
    SitePoint Enthusiast Knito's Avatar
    Join Date
    Feb 2006
    Location
    44 miles nnw from Heidelberg
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Here is a query

    Code:
     
    SELECT COUNT(owner) AS counthoes, pimps.name
    FROM hoes LEFT JOIN
    pimps ON hoes.owner = pimps.id
    GROUP BY hoes.owner, pimps.name

  9. #9
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how would that help me? *confused*
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  10. #10
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry - I see what you mean. You do still need a JOIN though - it would look something like this:

    Code:
    SELECT     COUNT(hoes.id) AS HoeCount, pimps.pimp_name
    FROM         hoes INNER JOIN
                          pimps ON pimps.id = hoes.owner
    GROUP BY pimps.pimp_name
    ORDER BY HoeCount DESC
    LIMIT 5

  11. #11
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both solutions would work - I'll go through mine so you can see how it helps ...

    Code:
    SELECT     COUNT(hoes.id) AS HoeCount, pimps.pimp_name
    This line tells MySQL I want to select my "hoes" as a number rather than individual names and I want to get that number using the alias "HoeCount" - and I also want to get the pimp's name from the pimps table.

    Code:
    FROM         hoes INNER JOIN
    ... so, select that count data from the "hoes" table and JOIN it with ...

    Code:
                          pimps ON pimps.id = hoes.owner
    ... data from the "pimps" table - now here, ON is like WHERE ... so, where the pimp id is the hoe owner id ...

    Code:
    GROUP BY pimps.pimp_name
    ... this line is important - you GROUP that data by pimp name, because otherwise your count will just return the total number of records in the "hoes" table, which is not what you want. What GROUP BY does is get MySQL to group those hoe counts by pimp name, which is what you DO want ...

    Code:
    ORDER BY HoeCount DESC
    ... is obvious, because you want the "pimps" in order of the number of hoes they own ...

    Code:
    LIMIT 5
    ... and again, obvious - limit to 5 results. Now you can cycle through the results from this recordset, spitting out pimps.pimp_name and HoeCount to the screen.

    Clearer?

  12. #12
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok cool thats working one thing i forgot, is that i want to link their username with their profile, and i need their `id` from pimps or `owner` from hoes.. ill try to do that... and if you can show how thats even better, never did this things before..

    btw... THANKS ALOT!!
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  13. #13
    SitePoint Member auveeb's Avatar
    Join Date
    Oct 2005
    Location
    NY, USA
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha, I couldn't help but laugh at your original post. 'One row per hoe' hah

  14. #14
    Crazy Webmaster Cr0CX's Avatar
    Join Date
    Dec 2003
    Location
    WWW
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehehe... btw never mind i finished this... thanks everbody..!
    Run a site? over 1,000 uniques a day? PM me for link exchange.

  15. #15
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice one. No problem. It would be cool if you posted your finished SQL so others can see what you ended up with in the future ... ?

  16. #16
    SitePoint Enthusiast Knito's Avatar
    Join Date
    Feb 2006
    Location
    44 miles nnw from Heidelberg
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb You said you needed the id

    Here is a query with the id field:

    Code:
     
    SELECT COUNT(hoes.owner) AS counthoes, pimps.name, pimps.id
    FROM hoes LEFT JOIN pimps ON hoes.owner = pimps.id
    GROUP BY hoes.owner, pimps.name, pimps.id
    ORDER BY COUNT(hoes.owner) DESC
    LIMIT 5
    And it would be a *very good* idea to have an index on every field in the group-by clause and the join clause: owner, name and id.
    In pimps and hoes.

    And now I goes.


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
  •