SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 39

Thread: Group by php

  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group by php

    Hi guys,

    I need to group search results. while I can use "Group By (column_name)", my problem is when a search result is returned how to see what rows are within a group so i can do smomthing like:

    we found 5 matches in group A, click here to see all

    how you have you done this? can you pelase give me some ideas?

    thanks

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select count(column_name) as found ...

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    select count(column_name) as found ...
    thanks, but i know this. My problem is when say I get 5 rows within a group, how do I then do something like this?

    x matches found click here to seee all

    x is the number of rows found

    hope i make myself clear

  4. #4
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump...

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    perhaps post the query/code, not entirely sure what you want.

  6. #6
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hash View Post
    perhaps post the query/code, not entirely sure what you want.
    ok, i'll explain again:

    lets say i have a table with these values:

    id | price
    1 | 20
    2 | 20
    3 | 20
    4 | 30

    so I have a sql statement count(price) and group by price, this now returns
    count | price
    3 | 20
    1 | 30

    my question is what is the best way to show the individual rows within each group like in this format:

    Found 3 rows with price 20 click here to see all rows (if clicked will show all 3 rows that have price 20)

    Found 1 row with price 30 click here to see all rows

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Slightly different example but I believe it still achives what you want (just change the table and field names to suit - you would need to add any required WHERE clause).

    The table and sample data:

    Code SQL:
    CREATE TABLE IF NOT EXISTS `members` (
      `id` INT(11) NOT NULL,
      `member_type` VARCHAR(20) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
    INSERT INTO `members` (`id`, `member_type`) VALUES
    (1, 'New Member'),
    (2, 'New Member'),
    (3, 'New Member'),
    (4, 'Junior Member'),
    (5, 'Junior Member'),
    (6, 'Junior Member'),
    (7, 'Junior Member'),
    (8, 'Senior Member'),
    (9, 'Senior Member'),
    (10, 'Senior Member'),
    (11, 'Senior Member'),
    (12, 'Senior Member');

    The script (quickly put together and not tested at all):
    PHP Code:
    <?php


    // database connection stuff here

    $sql="
        SELECT
              member_type
            , COUNT(*) as number_of_members
        FROM
            members
        GROUP BY
            member_type
    "
    ;

    $escaped_sql=mysql_real_escape_string($sql);

    $result mysql_query($escaped_sql);

    while (
    $row mysql_fetch_assoc($result)) {
        
    $member_count=$row['number_of_members'];
        
    $member_type=$row['member_type'];
        
        echo 
    "There were $member_count member(s) found for membership type '$member_type'. 
         <form method='get' action='script_that_displays_the_members'>\n
                          <input type='hidden' name='member_type' value='
    $member_type'/>\n
                          <input type='submit' value='View Them'/>
                          </form>View them"
    ;
    }
    ?>
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ma201dq View Post
    ok, i'll explain again:

    lets say i have a table with these values:

    id | price
    1 | 20
    2 | 20
    3 | 20
    4 | 30

    so I have a sql statement count(price) and group by price, this now returns
    count | price
    3 | 20
    1 | 30

    my question is what is the best way to show the individual rows within each group like in this format:

    Found 3 rows with price 20 click here to see all rows (if clicked will show all 3 rows that have price 20)

    Found 1 row with price 30 click here to see all rows
    Use the count query to get table two above, use php to create links for each row, pass the price in the link, use a where query to get all rows for that price. Is that what you mean?

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    SELECT
         p.price
         ,p.prices
         ,t.id
      FROM
         (SELECT
               t.price
               ,COUNT(*) prices
            FROM
               my_table t
           GROUP
              BY
               t.price) p
     INNER
      JOIN
         my_table t
        ON
         p.price = t.price

    The other solution is to eliminate the subquery and count the number of number of rows per price in PHP. In either case your going to need to format the result set on the application side. So I would run a simple select for everything then handle the aggregate calculations on the application side while parsing the result set into a price => rows pair. You could also do as hash said and run the count query then a query for each individual row that pertains to the given price.

  10. #10
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SpacePhoenix, the less code you write, the less errors you have.
    Try to think of mysql_real_escape_string use in your code. And usefulness of other code too. I bet OP has his table with data already.

    oddz, you don't need such a query. There can be huge amount of data. And we do not know, what type we well need. So, if you want scalable application, it must be done with different queries.

    ma201dq, make a hyperlink.

  11. #11
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    @Shrapnel_N5

    It was an example for the OP on how to do it, instead of mysql_real_escape_string on a live site they would use prepared statements. How do you suggest the OP does it?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  12. #12
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no use for mysql_real_escape_string, nor prepared statements in this code.
    Why not to learn PHP a bit?

  13. #13
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ma201dq View Post
    Hi guys,

    I need to group search results. while I can use "Group By (column_name)", my problem is when a search result is returned how to see what rows are within a group so i can do smomthing like:

    we found 5 matches in group A, click here to see all

    how you have you done this? can you pelase give me some ideas?

    thanks
    Quote Originally Posted by Shrapnel_N5 View Post
    There is no use for mysql_real_escape_string, nor prepared statements in this code.
    Why not to learn PHP a bit?
    Shrapnel_N5, I suggest that you get some better glasses. If you had read ma201dq's original post (quoted above) you would have seen the it is to do with search results which might well mean the use of a form where the user selects something to search for that means user input and user input should never be trusted, hence the use of mysql_real_escape_string or preferably prepared statements.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  14. #14
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Too much words.
    Try to run your code once at least.

  15. #15
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    Too much words.
    Try to run your code once at least.
    Fewer words: Security, never trust user submitted data including search parameters. It's not rocket science.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  16. #16
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You've a lot of proper words, but not a bit of understanding what does it mean
    There is no connection between your words and your code.
    Yes, there may be use of escaping. And even with no user input at all. But, of course, not in the way you wrote it.
    I wonder why don't you want to read mysql_real_escape_string page in documentation or at least run your code (with printing out mysql_error() added to it) to see actual result of it.

  17. #17
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    @Sharpnel_N5 What is SQL injection?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  18. #18
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Space, you don't escape the query, only the variables, try your code to see results.
    Perhaps more obvious with this
    SELECT 1 WHERE name = 'hash'

  19. #19
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's getting funny

  20. #20
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hash View Post
    Space, you don't escape the query, only the variables, try your code to see results.
    Perhaps more obvious with this
    Ok, I made a mistake. For a live site I would use prepared statements anyway as mysql_real_escape_string does not always make a query safe.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  21. #21
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh ok complete sql fail :P but was just trying to make the escaping point

  22. #22
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wonder, if it possible to make him understand at all.

    SpacePhoenix, please, do me a favor?
    Rewrite your code using, oh my - ok, prepared statements. And try to run it.

  23. #23
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,526
    Mentioned
    83 Post(s)
    Tagged
    3 Thread(s)
    Potential issues are:

    • "View them" is repeated in plain text after the button
    • The end of one result runs in to the start of another result


    Here's one way to tidy things up

    Code php:
    echo <<< EOT
    <form method="get" action="script_that_displays_the_members">
        <p>There were $member_count member(s) found for membership type $member_type.</p>
        <p><input type="hidden" name="member_type" value="$member_type" />
           <input type="submit" value="View Them" />
        </p>
    </form>
     
    EOT;
    And here's another way

    Code php:
    echo '<form method="get" action="script_that_displays_the_members">'
            . '<p>There were ' . $member_count . ' member(s) found for membership type "' . $member_type . '".</p>'
            . '<p><input type="hidden" name="member_type" value="' . $member_type . '" />'
                . '<input type="submit" value="View Them" /></p>'
        . '</form>';
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  24. #24
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off topic I guess, but isn't method three easier to read?


  25. #25
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,526
    Mentioned
    83 Post(s)
    Tagged
    3 Thread(s)
    The difficulty with method three is that is it directly output to the page. While the example here uses echo, in real life we are more likely to return the html code so that it can be output at a later date when the time is right.

    There are certain uses for the third method, but only when you're not planning to store the html code for later use.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript


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
  •