a very simple query without group_concat ?

ishadami

Hi all,
i have to write a query that work as a group_concat, this is because i have no chance to use a new version.
so here is my table and data

table => users

id, name, graduate, university

1, John, 2004, Cambridge
2, Ally, 2001, Harvard
3, Tom, 2004, Cambridge
4, Jane, 2001, Cambridge

i want to get this data in this way >>

John & Tom, 2004, Cambridge
Ally, 2001, Harvard
Jane, 2001, Cambridge

is it possible without group_concat ?
thanks a lot ..

bretticus

are you sure...I mean

SELECT GROUP_CONCAT( `name` SEPARATOR ' & '), graduate, `university` FROM `students` GROUP BY `graduate`,`university`

...works slick. 😃

Yeah, you could do this programatically...

Make sure you do a

ORDER BY `graduate`,`university`

so that the recordset returns alike records together. For example. if you were printing individual rows. use if statement(s) to determine whether to just print an extra name or print the name, the rest of the columns and new row. I suppose (leaving the logic out but to give you the basic idea) your code might look similar

<?php
$last_graduate = 0;
$last_university = '';
while ($row = mysql_fetch_array($result)) {
	if ( $row['graduate'] == $last_graduate && $row['university'] == $last_university )
	/* ...do stuff... */
	$last_graduate = $row['graduate'];
	$last_university = $row['university'];
}
?>

good luck.

ishadami

well, i think i couldnt tell my problem correctly
here is my working query with group_concat

<?php
$sql = "SELECT a.graduate, a.university, group_concat(name) as people From `users` AS a GROUP BY `graduate`, `university`";
?>

however normal query with "ORDER BY graduate,university"
doesnt get all records but only one record, where their graduate and university values are same..

bretticus

ishadami wrote:
Hi all,
i have to write a query that work as a group_concat, this is because i have no chance to use a new version.
...
is it possible without group_concat ?
thanks a lot ..

That made me think that you had an older version of MySQL and could not use GROUP_CONCAT(). The very first query I wrote was your example working with GROUP_CONCAT(). Everything after that was a suggestion of how you might do this without GROUP_CONCAT().