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
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
bump…
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
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'>\
<input type='hidden' name='member_type' value='$member_type'/>\
<input type='submit' value='View Them'/>
</form>View them";
}
?>
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?
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.
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.
@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?
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.
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.
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.
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’
That’s getting funny
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.