Jump to content

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


michaellunsford

Recommended Posts

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!
Link to comment
Share on other sites

[!--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]
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.