How do you sort records?

ppowell

Very simplistic but I am utterly STUMPED at this one.

I have a db table "person" that I can sort by title, first_name, last_name, or city with no problems at all because "title", "first_name", "last_name", and "city" are columns in the table.

The requirement is to ALSO sort by department NAME. Problem is, I can't do that in the "person" table because the column is "department_id" which is a foreign key constraint to the department table.

How do I sort the person records by department_name based on this?

I am using PHP 4.3.2 and MySQL 4.0.10

Now, second problem and even more of a problem:

The requirement is to ALSO sort by state NAME and country NAME. The fields "state" and "country" in the "person" table contain only an abbreviation of state and country; the corresponding full name of each state and country are found in respective XML files in /xml/state.xml and /xml/country.xml

So, NOW... how do I sort?

Thanx
Phil

nemonoman

Join and sort on non-displayed fields

SELECT person.*
FROM person, department, country
WHERE person.departmentid=department.id
AND person.countryid=country.id
ORDER BY department.name, person.name, country.name, department.fieldofstudy

etc.,etc.

ppowell

A bit more complicated than that in the long run, but you were almost right.

The joined query must be a LEFT JOIN because the left field p.department_id can be null and still has to default sort anyway.

That handles only ONE of them, the others are purely done through a class method that will compare a result with a parsed XML file and rearrange it through @uksort($resultWrapperArray, 'strnatcasecmp');

Phil

nemonoman

Gosh I'm so glad I was ALMOST right!

Here's another fact that might ALMOST be relevant.

You can indeed ORDER BY the null-valued department.name in an OUTER JOIN

You could import the XML data into a table and join on that. Why it's not in a data table already is beyond me.

Then YOU could ALMOST solve this problem as you stated it.

ppowell

Wow, for a senior member that was such a non-senior-like response..

Actually, FYI, requirement states XML files stay as XML files and the schema structure not be altered.. no XML into data tables, why will remain.. beyond you.. and into my bank account when I get paid.

Phil

nemonoman

And when you figure it out.