Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 21 April 2006 - 09:35 PM

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.

$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";
}
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!

#2 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 21 April 2006 - 09:53 PM

[!--quoteo(post=367328:date=Apr 21 2006, 02:35 PM:name=michaellunsford)--][div class=\'quotetop\']QUOTE(michaellunsford @ Apr 21 2006, 02:35 PM) View Post[/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.

$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";
}
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:


SELECT contacts.*, titles.* FROM contacts, titles WHERE titles.title_id = contacts.contact_id ORDER BY title


#3 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 21 April 2006 - 10:57 PM

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

thanks!

#4 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 23 April 2006 - 03:20 AM

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?

#5 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 23 April 2006 - 04:28 AM

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!

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 April 2006 - 07:49 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users