While Loop question...

[elmas156]

Hey everyone, I have a database list of staff members that work at my office.  I want to list each staff member with a while loop.  In the database there is a field named "admin" where the value is either "y" or "n."  I want to list administration first, then everyone else alphabetically by their last name.  I wrote the code below that should work (haven't tested it yet) but I'm wondering if there is an easier, more efficient way to accomplish this. Thanks for any input that you may provide.

 

<?php

$result1 = mysql_query("SELECT `prefix`,`lname`,`pos` FROM staff WHERE staffid > '0' AND admin == 'y' ORDER BY `lname` DESC");

$result2 = mysql_query("SELECT `prefix`,`lname`,`pos` FROM staff WHERE staffid > '0' AND admin == 'n' ORDER BY `lname` DESC");

while ($row1 = mysql_fetch_row($result1)) {
  				
			$prefix = $row1[0];
			$lname = $row1[1];
			$pos = $row1[2];

			$fullname1 = "$prefix $lname ($pos)";

                echo "$fullname<br>";

}


while ($row2 = mysql_fetch_row($result2)) {
  				
			$prefix = $row2[0];
			$lname = $row2[1];
			$pos = $row2[2];

			$fullname2 = "$prefix $lname ($pos)";

                echo "$fullname2<br>";

}

?>

[shlumph]

You can have more then one order specification, the first one takes the most priority:

"SELECT `prefix`,`lname`,`pos` FROM staff WHERE staffid > '0' ORDER BY admin DESC, `lname` DESC"

 

So, now you'll just need one query and one while loop.

[rwwd]

In your sql syntax ( AND admin == 'y' & AND admin == 'n') php an mysql work differently, so your example will error.  What you need is this:-

 

AND admin = 'y' 

 

and of course

 

AND admin = 'n'

 

Php 'is equal too' is represented with ==

mysql 'is equal too' is with =

 

Other than that, seems fine, I think ;p

 

Rw

[elmas156]

Thanks rwwd, I would have realized that eventually ;-). 

 

Shlumph, wouldn't this just list all the non admin people first, because the value of admin for them would be "n", then list all the admin people at the bottom without alphabetizing?  I don't know, I'm going to play with it and see what I can do... Thanks.

[shlumph]

Shlumph, wouldn't this just list all the non admin people first, because the value of admin for them would be "n", then list all the admin people at the bottom without alphabetizing?  I don't know, I'm going to play with it and see what I can do... Thanks.

 

It will list all the admins first, because 'y' comes after 'n', and it's ordered by DESC.

 

It will order all the admins first, and order them by their last name. It would then order all the other people next, ordering them by their last name. Which is what you want, right? This would be a huge performance boost, and would make your code less redundant.

 

I'd probably want to have their lname ordered by ASC, though.

[elmas156]

Awesome!  Got it... it works great!  Thanks Shlumph!