Group by php

[ma201dq]

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

[Shrapnel_N5]

select count(column_name) as found ...

[ma201dq]

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

[ma201dq]

bump...

[hash]

perhaps post the query/code, not entirely sure what you want.

[ma201dq]

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

[SpacePhoenix]

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:

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


// 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";
}
?>

[hash]

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?

[oddz]

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.

[Shrapnel_N5]

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.

[SpacePhoenix]

@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?

[Shrapnel_N5]

There is no use for mysql_real_escape_string, nor prepared statements in this code.
Why not to learn PHP a bit?

[SpacePhoenix]

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

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.

[Shrapnel_N5]

Too much words.
Try to run your code once at least.

[SpacePhoenix]

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.

[Shrapnel_N5]

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.

[SpacePhoenix]

@Sharpnel_N5 What is SQL injection?

[hash]

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'

[Shrapnel_N5]

That's getting funny

[SpacePhoenix]

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.

[hash]

heh ok complete sql fail :P but was just trying to make the escaping point

[Shrapnel_N5]

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.

[paul_wilkins]

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

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

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>';

[hash]

Off topic I guess, but isn't method three easier to read?

[paul_wilkins]

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.