weemikey Posted July 5, 2007 Share Posted July 5, 2007 Hi all! Thanks in advance for any advice you can give. I'm used to temp tables in other DB's, but I'm working with php/mysql now and I'm not sure how to do what I need to do. Here's my scenario: - I have three tables that I want to draw info out of and sort - one table is contact_beat, which holds the ID of a contact and the ID of a beat (this is for a PR company, so a beat is like "advertising" or "travel/tourism") - the other table is contacts, which of course holds the contact.id and more importantly the NAME of the contact. - the last table is address, which again holds the contact.id and the email/phone info I need to display So in the end I'm displaying a bit of contact info for all contacts that are associated with a specific beat. My query goes through contact_beat looking for all records for a specific beat, then I have a query to find the contact name from the contacts table. Then one last query to find address info for the contact. The trouble is that the sorting is done on the contact_beat table, so the names are all out of order. I thought I could just create a temp table with columns for the contact info (which is all I want to display) and SORT it by last name. However I can't find an example of a temp table creation that makes sense to me. Any advice on how to create the table and how you add records to it? I'm a bit lost! Thanks, Mike Quote Link to comment Share on other sites More sharing options...
B34ST Posted July 5, 2007 Share Posted July 5, 2007 are the two contacts tables larger than your example? If not why not combine them? then you can just use 1 query too pull all the data and sort it however u like? how are the beats pulled out? Quote Link to comment Share on other sites More sharing options...
soycharliente Posted July 5, 2007 Share Posted July 5, 2007 Why can't you just make one table with id, name, address, beat, etc.? Or just use a join. SELECT contact_beat.contactid, contact_beat.beatid, contacts.contactid, contacts.name, address.contactid, address.email, address.phone FROM contact_beat JOIN ON contacts, address WHERE contact_beat.contactid=contacts.contactid AND contact_beat.contactid=address.contactid ORDER BY name NOT TESTED!!!! Open your phpMyAdmin and run that query to see what it returns. Quote Link to comment Share on other sites More sharing options...
weemikey Posted July 5, 2007 Author Share Posted July 5, 2007 Well, it works like this: - the user chooses a "beat" from a list, which takes them to a new page - the beat.id is passed in and the contact records associated with that BEAT are found. Contacts can be associated with any number of beats, which is why I made the contact_beat table. It holds the contact.id and the beat.id. So I'm finding a list of contact id's from this table. However to find the names associated with the contact.id I have to look in the contact table (which holds just basic contact info such as name and title). The structure as I have it now is a loop through the contact_beat table finding records with the chosen beat.id. When it finds a record it goes to my next query which finds the contact.name for the contact.id that was found in the contact_beat record. It then takes the same contact.id and finds the address record(s) for that contact. So for each record in contact_beat I then find two more records that are associated. Does that make sense? Quote Link to comment Share on other sites More sharing options...
soycharliente Posted July 5, 2007 Share Posted July 5, 2007 I made an edit in my last post I think while you were typing your last response. Quote Link to comment Share on other sites More sharing options...
weemikey Posted July 5, 2007 Author Share Posted July 5, 2007 hey Charlie! Thanks for the suggestion. I didn't really know how to use joins. So I got a bunch of things working! Quote Link to comment Share on other sites More sharing options...
soycharliente Posted July 5, 2007 Share Posted July 5, 2007 Just add another field after ORDER BY, Separate them by commas, and put them in the order of sorting. SO if I wanted to sort by last name, then first name, then age. ORDER BY last, first, age ASC ASC = ascending (low/beginning to high/end) DESC = desc (high to low) Quote Link to comment Share on other sites More sharing options...
weemikey Posted July 5, 2007 Author Share Posted July 5, 2007 Thanks again! Damn, this place moves quickly. All is well. I have joined three tables and sorted perfectly. I'm VERY happy now. :D :D Quote Link to comment Share on other sites More sharing options...
soycharliente Posted July 5, 2007 Share Posted July 5, 2007 You'll find that most people here are pretty responsive. Make sure that once your topic is finished, i.e., you've gotten the help you wanted/found a solution to your problem, that you mark the topic as solved. Only the OP (original poster) has the option so you have to be the one to do it (maybe an admin can too, but it's better if you do so they don't have to go through 4000 thread a day and people will stop coming to the thread to see if it's been solved yet). It should on the far left button on the menu bar (on right side) above these posts. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 5, 2007 Share Posted July 5, 2007 Seems Weemikey isn't quite as responsive. I'll mark it solved. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.