simple sorting problem?

claire

This really should be easy, but I don't seem to get the hang of it.

I have a MySQL db-table as follows:

apartment name

1 smith
1 smith
2 doe
3 larsen
3 brown

I need to create a list that looks like this

doe
brown, larsen
smith

In other words, the list should be ordered by name but names in the same apt should be kept together. Also, if both names in one apt is the same, only one name should be printed out.

I tried ("SELECT * FROM tbl WHERE Apt>=24 AND Apt <=48 GROUP BY Apt ORDER BY Name")

But the GROUP BY isn't correct since it gets rid of one of the names...

Anon

I can't imagine how you could do that in SQL. Would be pretty easy if you just read the rows one at a time and build the list(s) in php.

shottie

try this.. a simple if-else in a while loop:

$dbq = mysql_query(\"SELECT * FROM tbl ORDER BY Apt\") or die(mysql_error());

while ($currentrow = mysql_fetch_array($dbq)) {
	$name = $currentrow[name];
	$apartment = $currentrow[Apt];

	if ($apartment == $oldApartment) {
		echo \", \" . $name;
	} else {
		echo $apartment . \" \" . $name;
	}
	$oldApartment = $apartment;
}

claire

Hi,
a bit on its way!

I now get the names belonging to the same apt kept together. However, the list is still sorted by apt number instead of being sorted in an alphabetical order (regardless of the apt no. In fact I'm not even echoing out the apt no)

(Sorry, i missed out on the aphabetical order in my first posting)

shottie

hmm.. not quite sure your situation. but if you have have a set number of apartment numbers or if they are sequential, this should work:

$startNumber = 1; // whatever you want
$totalApts = 30; // whatever you want

for ($number = $startNumber; $number <= $totalApts; $number++) {

$dbq = mysql_query("SELECT * FROM tbl WHERE Apt = '$number' ORDER BY Apt ASC")

while blah blah .. { // loops through name in each apartment
}

}

claire

Tried it, but I still get the same output as earlier...names listed in order of apt (with names kept together per apt) but not a list in alphabetical order.

Do you think creating a tmp table would do the trick? First "storing" names together according to their apt number, then performing a second query sorting in alphabetical order?

Anon

This might work...

$result = mysql_query("SELECT * FROM tbl ORDER BY Name");

$result = mysql_fetch_object(...);

other nifty stuff

while (list($key, $item) = each($result)) {
$apt[$item->apt] .= "$item->name ";
}

while (list($key, $item) = each($apt)) {
print "Apt: $key, $item";
}

Good luck

claire

I don't get it to work.What goes inside the parentethis in the $result line ?

$result = mysql_fetch_object(...);

claire

I have a MySQL db-table as follows:

apartment no | name
-------------|--------
1 | Smith
1 | Smith
2 | Doe
3 | Brown
3 | Larsen

I need to create an alphabetical list that looks like this

Brown, Larsen
Doe
Smith, Smith

In other words, the list should be ordered by name, but names in the same apt should be kept together.

Yesterday I got some help keeping names in the same apartement together(see below: simple sorting problem?) However, I still haven't managed to produce the list in alphabetical order! Any ideas???

shottie

whoops..i got your variables mixed up. sort the db query by name (or whatever you alphabetical variable is). so if it\'s \'Name\' this should work:

$startNumber = 1; // whatever you want
$totalApts = 30; // whatever you want

for ($number = $startNumber; $number <= $totalApts; $number++) {

$dbq = mysql_query(\"SELECT * FROM tbl WHERE Apt = \'$number\' ORDER BY Name ASC\")

while blah blah .. { // loops through name in each apartment
}

}