Order by ASC within a loop containing another category

[SF23103]

I have a database that contains First Name, Last Name, ID number, Title, Assignment, etc.

 

I am trying to echo the data in a loop that prints everyone on Division_1 and then within title, and again within ID number.  For instance:

 

Title_1 John Doe

Title_2 Jim Smith

Title_2 George Johnson

Title_2 Bob James

Title_3 William Smith

Title_3 Billy Bob

Title_4 Joh Smitherson

 

Some titles have more people than others.  For each title, I want to sort them by ID number (ASCending).  Right now with the following code, it will sort them by ID number with no regard to Title.  Ideas?!  :-)

 

 

<?php
$query="SELECT * FROM form_1 WHERE col_5='Division_1' ORDER by col_3";
$result=mysql_query($query);
$num=mysql_numrows($result);
?>

<table cellpadding="7px" width="735px" border="0">

<?php
$i=0;
while ($i < $num) {

$result1=mysql_result($result,$i,"col_1"); //First Name
$result2=mysql_result($result,$i,"col_2"); //Last Name
$result3=mysql_result($result,$i,"col_3"); //ID Number
$result4=mysql_result($result,$i,"col_4"); //Title
$result5=mysql_result($result,$i,"col_5"); //Assignment
$result6=mysql_result($result,$i,"col_6"); //Hours

if ($result4 == "Title_1")
echo ("<tr><td height=\"30px\"> $result4 </td><td>$result1 $result2</td><td><a href=\"...\">Send Message</a></td></tr>");

if ($result4 == "Title_2")
echo ("<tr><td height=\"30px\"> $result4 </td><td>$result1 $result2</td><td><a href=\"...\">Send Message</a></td></tr>");            

if ($result4 == "Title_3")
echo ("<tr><td height=\"30px\"> $result4 </td><td>$result1 $result2</td><td><a href=\"...\">Send Message</a></td></tr>");

if ($result4 == "Title_4")
echo ("<tr><td height=\"30px\"> $result4 </td><td>$result1 $result2</td><td><a href=\"...\">Send Message</a></td></tr>");

if ($result4 == "Title_5")
echo ("<tr><td height=\"30px\"> $result4 </td><td>$result1 $result2</td><td><a href=\"...\">Send Message</a></td></tr>");

if ($result4 == "Title_6")
echo ("<tr><td height=\"30px\"> $result4 </td><td>$result1 $result2</td><td><a href=\"...\">Send Message</a></td></tr>");


$i++;
}

mysql_close();

?>
</table>

 

[haku]

You can have multiple ORDER BY statements in a query. For example:

 

SELECT name, age FROM users ORDER BY age, name

 

This will first order users by their age, then alphabetically by their name. So it would look something like this:

 

22 Dave

22 Joe

23 Alan

23 Bob

 

As you can see, the order is by age, then within age, it's by name.

[Jessica]

Add the title column to the order clause...

[SF23103]

The title colum isn't alphabetical though.  How do I tell it what order I want?

 

Managers:

 

John (22)

Dave (23)

Bob (34)

 

 

Supervisors:

 

Alan (21)

Smith (24)

[Jessica]

What order do you want?

[haku]

ORDER BY CASE WHEN title="manager" THEN 1 WHEN title="supervisor" THEN 2 END ASC

 

The above applies a number to the given cases, and orders them by that number. So with the above, manager is equal to 1, and supervisor is equal to 2, and since it's ordered ascendingly, managers will come before supervisors.

[SF23103]

i.e.

 

Managers

 

Supervisors

Representitives

 

Cashiers

[haku]

We cross-posted. See my response above your post.

[SF23103]

Thanks Haku.  Working on it now.  I'm getting an error right now, looking at figuring it out.

 

$query="SELECT * FROM form_21 WHERE col_5='Division_1' ORDER BY CASE WHEN col_4='Manager' THEN 1 WHEN col_4='Supervisor' THEN 2, col_3";
$result=mysql_query($query);
$num=mysql_numrows($result);

 

Getting Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource

Edited March 21, 2013 by SF23103

[haku]

You missed END.

[SF23103]

$query="SELECT * FROM form_21 WHERE col_5='Division_1' ORDER BY CASE WHEN col_4='Manager' THEN 1 WHEN col_4='Supervisor' THEN 2 END, col_3";

$result=mysql_query($query);
$num=mysql_numrows($result);

Still getting an error.. never used it before, tried putting END without the col_3 and still getting the error.  I'm playing around with it, maybe I missed something simple.

[SF23103]

$query="SELECT * 
FROM form_21 
WHERE col_5='Division_1'
ORDER by (CASE 
WHEN col_4='Supervisor' THEN 1
WHEN col_4='Manager' THEN 2
WHEN col_4='Assistant Manager' THEN 3
WHEN col_4='Team 1 Leader' THEN 4
WHEN col_4='Team 2 Leader' THEN 5
WHEN col_4='Team 3 Leader' THEN 6
WHEN col_4='Team 4 Leader' THEN 7
WHEN col_4='Team 5 Leader' THEN 8
WHEN col_4='Team 6 Leader' THEN 9
WHEN col_4='Team 7 Leader' THEN 10
WHEN col_4='Team 8 Leader' THEN 11
WHEN col_4='Team 9 Leader' THEN 12 ASC END)";
$result=mysql_query($query);
$num=mysql_numrows($result);

outputs Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in...

[haku]

END ASC

 

...as can be seen in my original example.

[SF23103]

Same error :-(

[haku]

$result=mysql_query($query) OR die(mysql_error());

Need to figure out what your problem is. But I do see you've added some parenthesis in your query, that were not part of the example I gave. Could be that.

[SF23103]

That did it.. it was the parenthesis.  Thats what I get for trying someone elses suggestion first and not yours :-)

 

Thanks for your help.