can I sort records in a table when the sort requested is not alphabetical?

[JTapp]

It's quite possible that this is impossible.. but here goes...

 

I have some personnel data that is being reported in a table and in that table I have a field that holds a person's "title". 

Let's say the returned "title" results are "President", "Vice President", "Assistant To The President" and "Assistant to the Vice President".  There are not more than four of them.  Does anybody have any idea if I could sort the returned results based on the above heirarchy - which is not alphabetical?

 

Here is my code:

 

echo "<center>\n";

echo "<H2>Roster</H2>\n";

echo "<table border='1'>

  <tr>

<th>Title</th>

<th>First Name</th>

<th>Last Name</th>

 

</tr>";

 

if (mysql_num_rows($query)) {

    while ($row = mysql_fetch_array($query)) {

$variable1=$row["title"];

$variable2=$row["firstname"];

$variable3=$row["lastname"];

       

//table layout

       

print("<tr>");

echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";

echo "<td class=\"td_id\">$variable1</td>\n";

echo "<td class=\"td_id\">$variable2</td>\n";

echo "<td class=\"td_id\">$variable3</td>\n";

print("</tr>");

 

Thanks for looking at it!

[unsider]

Couldn't that potientally done in CSS?

 

Maybe I'm misunderstanding your question, but I am not qualified enough to help you, but if you're doing some research look from something with CSS.

[PFMaBiSmAd]

Using a mysql CASE statement to assign a value for the different titles, then doing an ORDER BY that value will allow an arbitrary sort order. See this link for an example - http://www.shawnolson.net/a/722/mysql-arbitrary-ordering.html

 

 

[JTapp]

I spent the last hour researching the suggestions you guys have made, but I'm not any closer to finding a solution.

Can you offer more details?  I'm really quite the beginner at this...

[DarkerAngel]

Try this, it's kind of redundant but should work in your situation:

But this will only output for rows those 4 fields:

<?php
if (mysql_num_rows($query)) {
   ranksort("President");
   ranksort("Vice President");
   ranksort("Assistant To The President");
   ranksort("Assistant to the Vice President");
}
function ranksort($level) {
       while ($row = mysql_fetch_array($query)) {
          if($row['title'] == $level) {
              $variable1=$row["title"];
              $variable2=$row["firstname"];
              $variable3=$row["lastname"];
       
              //table layout
       
              print("<tr>");
              echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";
              echo "<td class=\"td_id\">$variable1</td>\n";
              echo "<td class=\"td_id\">$variable2</td>\n";
              echo "<td class=\"td_id\">$variable3</td>\n";
              print("</tr>");
         }
       }
       return true;
}?>

[JTapp]

DarkerAngel -

I tried to plug it into my existing code but I got an error message.. here is the entire code with the query.. maybe it makes a difference to your suggestion?

 

<?php

//query details table begins

$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeMailingCity, tblLodges.strLodgeMailingPostCode, tblLodges.strLodgeCounty, tblOfficers.lngLodgeID, tblOfficers.Title, tblOfficers.strFirstName, tblOfficers.strLastName, tblOfficers.BusinessPhone, tblOfficers.PersEmail FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID WHERE tblLodges.intLodgeNumber=$id LIMIT 0, 50")or die(mysql_error());

 

echo "<center>\n";

echo "<H2>Roster</H2>\n";

echo "<table border='1'>

  <tr>

<th>Office Number</th>

<th>Title</th>

<th>First</th>

<th>Last</th>

<th>Email</th>

<th>Phone</th>

 

</tr>";

 

if (mysql_num_rows($query)) {

    while ($row = mysql_fetch_array($query)) {

        $variable1=$row["intLodgeNumber"];

        $variable2=$row["Title"];

        $variable3=$row["strFirstName"];

        $variable4=$row["strLastName"];

        $variable5=$row["PersEmail"];

        $variable6=$row["BusinessPhone"];

       

        //table layout for results

       

        print("<tr>");

        echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";

        echo "<td class=\"td_id\">$variable1</td>\n";

        echo "<td class=\"td_id\">$variable2</td>\n";

        echo "<td class=\"td_id\">$variable3</td>\n";

        echo "<td class=\"td_id\">$variable4</td>\n";

        echo "<td class=\"td_id\">$variable5</td>\n";

        echo "<td class=\"td_id\">$variable6</td>\n";

        print("</tr>");

    }

}

?>

 

[DarkerAngel]

I know what I did... i'll try to fix it for you... I didn't send the $query to the function thus it wasn't set, my mistake.

[DarkerAngel]

See if this works any better (sorry about above)

 

<?php
//query details table begins
$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeMailingCity, tblLodges.strLodgeMailingPostCode, tblLodges.strLodgeCounty, tblOfficers.lngLodgeID, tblOfficers.Title, tblOfficers.strFirstName, tblOfficers.strLastName, tblOfficers.BusinessPhone, tblOfficers.PersEmail FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID WHERE tblLodges.intLodgeNumber=$id LIMIT 0, 50")or die(mysql_error());

echo "<center>\n";
echo "<H2>Roster</H2>\n";
echo "<table border='1'>
   <tr>
<th>Office Number</th>
<th>Title</th>
<th>First</th>
<th>Last</th>
<th>Email</th>
<th>Phone</th>

</tr>";

if (mysql_num_rows($query)) {
   ranksort("President", $query);
   ranksort("Vice President", $query);
   ranksort("Assistant To The President", $query);
   ranksort("Assistant to the Vice President", $query);
}
function ranksort($level, $query) {
    while ($row = mysql_fetch_array($query)) {
        $variable1=$row["intLodgeNumber"];
        $variable2=$row["Title"];
        $variable3=$row["strFirstName"];
        $variable4=$row["strLastName"];
        $variable5=$row["PersEmail"];
        $variable6=$row["BusinessPhone"];
       
        //table layout for results
        if($variable2 == $level) {
     		print("<tr>");
     		echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";
       		echo "<td class=\"td_id\">$variable1</td>\n";
        	echo "<td class=\"td_id\">$variable2</td>\n";
        	echo "<td class=\"td_id\">$variable3</td>\n";
        	echo "<td class=\"td_id\">$variable4</td>\n";
        	echo "<td class=\"td_id\">$variable5</td>\n";
        	echo "<td class=\"td_id\">$variable6</td>\n";
        	print("</tr>");
	}
    }	
}
?>

EDITED CODE

[JTapp]

Well, its kind of working...

I'm getting "President" in the top row.

But that's all I'm getting.. the rest of the staff isn't showing up.. but I know they are there...

[DarkerAngel]

Well, its kind of working...

I'm getting "President" in the top row.

But that's all I'm getting.. the rest of the staff isn't showing up.. but I know they are there...

Humm... I was afraid that was how it was going to end up working...

[JTapp]

I'll keep playing with what you have given me so far.. who knows.. maybe it will click...

[JTapp]

I found this link.. it seems close to what you were trying to do.. but it makes me REALLY have to concentrate and re-read it several times.

Can you look at it and tell me if you think it might be a solution?

 

http://www.wellho.net/resources/ex.php4?item=h999/exc.php

[JTapp]

OK - scratch that last post.

I now have a column called "titleID"

For every "president" there is a value of 1

For every "VP" there is a value of 2... etc.

 

Now I have to try to figure out how to make the 'title' field return sorted data, based on the hidden 'titleID' field.

 

Does that make sense?

[DarkerAngel]

<?php
//query details table begins
$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeMailingCity, tblLodges.strLodgeMailingPostCode, tblLodges.strLodgeCounty, tblOfficers.lngLodgeID, tblOfficers.Title, tblOfficers.strFirstName, tblOfficers.strLastName, tblOfficers.BusinessPhone, tblOfficers.PersEmail FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID WHERE tblLodges.intLodgeNumber=$id LIMIT 0, 50")or die(mysql_error());

echo "<center>\n";
echo "<H2>Roster</H2>\n";
echo "<table border='1'>
   <tr>
<th>Office Number</th>
<th>Title</th>
<th>First</th>
<th>Last</th>
<th>Email</th>
<th>Phone</th>

</tr>";

if (mysql_num_rows($query)) {
while ($row = mysql_fetch_array($query)) {
	switch($row["Title"]) {
		case "President":
			$p .= formattable($row);
			break;
		case "Vice President":
			$vp .= formattable($row);
			break;
   			case"Assistant To The President":
			$attp .= formattable($row);
			break;
   			case"Assistant to the Vice President":
			$attvp .= formattable($row);
			break;
		default:
			$other .= formattable($row);
	}
}
echo($p.$vp.$attp.$attvp.$other);
}
function formattable($row) {
        $variable1=$row["intLodgeNumber"];
        $variable2=$row["Title"];
        $variable3=$row["strFirstName"];
        $variable4=$row["strLastName"];
        $variable5=$row["PersEmail"];
        $variable6=$row["BusinessPhone"];
       
        //table layout for results
     		$html = "<tr>"
     		$html .= "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";
       		$html .= "<td class=\"td_id\">$variable1</td>\n";
        	$html .= "<td class=\"td_id\">$variable2</td>\n";
        	$html .= "<td class=\"td_id\">$variable3</td>\n";
        	$html .= "<td class=\"td_id\">$variable4</td>\n";
        	$html .= "<td class=\"td_id\">$variable5</td>\n";
        	$html .= "<td class=\"td_id\">$variable6</td>\n";
        	$html .= "</tr>"

return $html;
}
?> 

 

I dunno if that don't work I might give up

[JTapp]

SOLVED.

 

Gosh.. sorry you wrote all that new code.  I added a "Group BY" and the Title ID field and we're good to go.

Thank you sooo much.  My  head hurts- I don't know how you Gurus do it!