sort one table's results by another table's field?

[michaellunsford]

Just some structure advice (maybe it's already built into mysql?)

I have an address book table. Instead of using a freeform title field, I am using an integer field that records the keyfield from a titles table. When the people in the address book are displayed, I just convert the title's integer into the description recorded in the title's table.

[code]$result=mysql_query("SELECT * FROM `contacts`");
$title_result=mysql_query("SELECT * FROM `titles`");
while($title_rows=mysql_fetch_assoc($title_result)) {
   $title[$title_rows['keyfield']]=$title_rows['value'];
}
while($my_contact=mysql_fetch_assoc($result)) {
   echo "Name: ".$my_contact['name'];
   echo "<br>\n";
   echo "Title: ".$title[$my_contact['title']];
   echo "<br><br>\n";
}[/code]
The title table also has a sort-by field -- which is what I want to sort the contacts results by. This would allow the "president" of a company to show up first, and his/her "assistant" to show up later on.

I've been hacking on it for a few hours -- but I just can't figure out how to accomplish this without writing some kind of bubble sort. Any ideas would be greatly appreciated.

Thanks!

[gizmola]

[!--quoteo(post=367328:date=Apr 21 2006, 02:35 PM:name=michaellunsford)--][div class=\'quotetop\']QUOTE(michaellunsford @ Apr 21 2006, 02:35 PM) [snapback]367328[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Just some structure advice (maybe it's already built into mysql?)

I have an address book table. Instead of using a freeform title field, I am using an integer field that records the keyfield from a titles table. When the people in the address book are displayed, I just convert the title's integer into the description recorded in the title's table.

[code]$result=mysql_query("SELECT * FROM `contacts`");
$title_result=mysql_query("SELECT * FROM `titles`");
while($title_rows=mysql_fetch_assoc($title_result)) {
   $title[$title_rows['keyfield']]=$title_rows['value'];
}
while($my_contact=mysql_fetch_assoc($result)) {
   echo "Name: ".$my_contact['name'];
   echo "<br>\n";
   echo "Title: ".$title[$my_contact['title']];
   echo "<br><br>\n";
}[/code]
The title table also has a sort-by field -- which is what I want to sort the contacts results by. This would allow the "president" of a company to show up first, and his/her "assistant" to show up later on.

I've been hacking on it for a few hours -- but I just can't figure out how to accomplish this without writing some kind of bubble sort. Any ideas would be greatly appreciated.

Thanks!
[/quote]


You are going about this all wrong. Relational databases by their nature can join tables together. Remember that the result of a query is always a "result set" aka a table. Rather than using 2 seperate queries and trying to bind them together, you can simply join them in one query and use ORDER BY to order the result set in the way you want it to be.

For example, experiment with phpMyAdmin and try something like:


[code]
SELECT contacts.*, titles.* FROM contacts, titles WHERE titles.title_id = contacts.contact_id ORDER BY title
[/code]

[michaellunsford]

Awesome -- I knew mysql could handle it. It works GREAT!

thanks!

[michaellunsford]

Ouch... new problem. I have the field "keyfield" in both tables. The second table is overwriting the first's keyfield number in the query results -- that's a problem.

Is there a way to say I want the contact's keyfield but not the title's keyfield?

[michaellunsford]

got it working by specifying the one title field I needed:

SELECT contacts.*, titles.title FROM `contacts`,`titles` WHERE contacts.title = titles.keyfield ORDER BY titles.order ASC

One day the exclusion of fields will be important -- but this works for now. If you do know how to exclude a field, though, please reply.

thanks!

[fenway]

Excluding fields is impossible -- the "proper" way to do this is to use column aliases, e.g. "SELECT contacts.keyfield AS contactKeyField, titles.keyfield AS titleKeyField...." It's not perfect, but unless you have a wrapper class to handle read/writes, this is the best way to go.