[SOLVED] Help: Sort by access level

[clown[NOR]]

I'm working on a members page, but I'm completely stuck, cant figure this out.. The code I've got now is

 

<?php

function getStaff()
{

	c2db();

	$query = "SELECT * FROM `staff` ORDER BY `alevel` DESC";
	$result = mysql_query($query);
	if (!$result) { die(mysql_error()); }



}

?>

 

What I wanna do is to categorise by access name (Admin, Modeartor, etc) but I just cant figure out how to make it work...

 

This is how I want it to be shown on the page

Admin

Displayname | Country

 

Moderator

Displayname | Country

 

DJ's

Displayname | Country

 

and I also want to be able to add new access names, so I cant use if ($accessname == "Admin")...

 

Thanks In Advance

- Clown

[clown[NOR]]

Ok.. I'm getting closer now... but there's one issue... when it prints out the info it comes out like this:

Admin

Clown || Norway

PoV || Canada

 

Admin

Clown || Norway

PoV || Canada

 

Moderator

Mordi || Norway

 

Why is admin coming twice? Here's the code I'm using:

<?php

function getStaff()
{

	c2db();

	$query = "SELECT * FROM `staff` ORDER BY `alevel` DESC";
	$result = mysql_query($query);
	if (!$result) { die(mysql_error()); }

	while ($alevel = mysql_fetch_assoc($result)) {

		$q="SELECT * FROM `staff` WHERE `aname` = '".$alevel['aname']."' ORDER BY `dname` ASC";
		$r=mysql_query($q);
		if (!$r) { die(mysql_error()); }
		echo '<strong>'.$alevel['aname'].'</strong><br />';
		while ($dbField = mysql_fetch_assoc($r)) {

			echo $dbField['dname'].' || '.$dbField['country'].'<br />';

		}
		echo "<br>";
	}

}

?>

[trq]

Running multiple queries to get infomation is very inificient. try using a better query. eg;

 

SELECT * FROM staff GROUP BY alevel SORT BY aname;

[clown[NOR]]

ok..thanks...

[clown[NOR]]

$query = "SELECT * FROM `staff` GROUP BY `alevel` SORT BY `aname`";

 

returns

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SORT BY `aname`' at line 1

[trq]

Sorry,

 

$query = "SELECT * FROM staff GROUP BY alevel ORDER BY aname";

[clown[NOR]]

Ok. The error message is gone now, but now it only prints out one from each access level...

 

like this:

Clown || Norway

Mordi || Norway

 

<?php

function getStaff()
{

	c2db();

	$query = "SELECT * FROM staff GROUP BY alevel ORDER BY aname";
	$result = mysql_query($query);
	if (!$result) { die(mysql_error()); }

	while ($dbField = mysql_fetch_assoc($result)) {

		echo $dbField['dname'].' || '.$dbField['country'].'<br />';
	}

}

?>

[MadTechie]

what a about this

<?php
function getStaff()
{

	c2db();

	$query = "SELECT * FROM `staff` ORDER BY `alevel` DESC";
	$result = mysql_query($query);
	if (!$result) { die(mysql_error()); }

	while ($dbField = mysql_fetch_assoc($result)) {
if($dbField['alevel'] != $lastalevel)
{
$lastalevel = $dbField['alevel'];
echo "<B>$lastalevel</B><br>";
}
		echo $dbField['dname'].' || '.$dbField['country'].'<br />';
	}

}
?>

[clown[NOR]]

thanks for the help guys, I've solved it now

 

<?php
function getStaff()
{
	c2db();
	$query = "SELECT * FROM staff ORDER BY alevel DESC";
	$result = mysql_query($query);
	if (!$result) { die(mysql_error()); }
	$aname = "Admin";
	echo "<strong>".$aname."</strong><br/>";
	while ($dbField = mysql_fetch_assoc($result)) {
		$tmpAname = $dbField['aname'];
		if ($tmpAname != $aname) {
			echo "<br /><strong>".$dbField['aname']."</strong><br/>";
			$aname = $dbField['aname'];
		}
		echo $dbField['dname'].' || '.$dbField['country'].' || '.$dbField['aname'].'<br />';
	}
}
?>