Member Avatar for 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 hierarchy - which is not alphabetical?

Here is my beginner's 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!

  • 2 Contributors
  • 3 Replies
  • 91 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

's approach to make the ordering explicit is exactly the right mindset and 's followup shows it works. Below are alternative, practical ways to express a custom (non-alphabetical) order so the intent is clear and maintainable.

One-shot SQL options (no schema change)

SELECT firstname, lastname, title
FROM personnel
ORDER BY FIELD(title,
  'President',
  'Vice President',
  'Assistant To The President',
  'Assistant to the Vice President'
), lastname;
SELECT firstname, lastname, title
FROM personnel
ORDER BY CASE title
  WHEN 'President' THEN 1
  WHEN 'Vice President' THEN 2
  WHEN 'Assistant To The President' THEN 3
  WHEN 'Assistant to the Vice President' THEN 4
  ELSE 5
END, lastname;

Notes: FIELD() is concise but MySQL-specific; CASE is standard SQL and portable. Both evaluate per row (fine for small/medium sets) but are not index-accelerated.

Schema/maintainability options

  • Preferred for production: a small lookup table (titles + sort_order) and JOIN ... ORDER BY t.sort_order so ordering is indexed and easy to change without ALTERing many rows.
  • ENUM can encode order in the column itself but is brittle when values change.
  • If changing schema is impossible, fetch rows and sort in the application with a small map-to-rank array (works well for tiny sets).

Final cautions: normalize stored title text (or use ids) to avoid mismatches, be mindful of collation when comparing strings, and choose the approach that balances performance with ease of future updates.

Recommended Answers

All 3 Replies

Member Avatar for nav33n

Sorting non-alphabetical/non-numerical records is not possible AFAIK. But you can have one more field of type int in the table, eg, type. You can enter the order of title, then sort by type !

Member Avatar for jtapp

SOLVED.
Took your advice and all is well. thanks.

Member Avatar for nav33n

Great! You are welcome !

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.