Jump to content

Archived

This topic is now archived and is closed to further replies.

michaellunsford

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

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!

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
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!

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.