return results from 2 tables with a one to many relationship

jtpiper · #1 15-Sep-2007, 15:17

I am trying to print a table where the first set of columns are a single row.
The last column will display 2 results separated by a ..

Example
chapter, address, city, st, officer1 officer2 officer3

I have 2 tables one that contains the officers and on the chapter names and addresses - linked together by chapter number $num

I have tried several foreach's and do while's without any luck.

The results I get are a row for each officer and the first 4 columns are duplicated for each officer.

My code below

PHP Code:


print "<center><table border=1>";
print "<tr><th align=center>Chapter</th><th>Name</th><th>City</th><th>Stated</th><th>Officers</th></tr>";

 $sql = mysql_query("select a.num, a.name as chapter, a.address, a.city, a.county, a.meeting, a.website, b.name, b.office from tbl_lodges as a, tbl_officers as b where a.num=b.num and a.body=\"RAM\" and  b.body=\"RAM\" order by $order ");
   while ($row = mysql_fetch_assoc($sql)) {
   extract($row);

print "<td>$num</td><td>$chapter</td><td>$city</td><td>$meeting</td><td><nobr>$num</td><tr>";
}
print "</table></center>";

Any help would be much appreciated..

Thanks

TurboPT · #2 16-Sep-2007, 09:14

To make that more clear, can you post an example of the results you get?

jtpiper · #3 16-Sep-2007, 10:10

The results I get are:

chapter1, address, city, state, officer1
chapter1, address, city, state, officer2
chapter1, address, city, state, officer3
chapter2, address, city, state, officer1
chapter2, address, city, state, officer2
etc....

what i want is:
Chapter1, address, city, state, officer1 officer2 officer3
Chapter2, address, city, state, officer1 officer2 officer3
etc..

TurboPT · #4 16-Sep-2007, 20:38

You can do this with two queries: (there might be nifty ways to do something different within the PHP api, but I don't have time to get into that right now)

1. gather the chapters, and locations (which you mainly have now), but group by the chapter, for the goal is to get each chapter/address to appear only once in the results.
2. then another query to get all the officers, based on the chapter. It appears that 'num' is some sort of referencing id? If so, then use this within the query too.

There problem is (well, it's not actually a problem, but just how the database gives the information) that the information from table 'a' is being 'paired' to information from table 'b'. A grouping clause will not help here either, because the officer names are unique, and there will still be three rows per chapter (assuming that there are three matches in table 'b' for each table 'a' entry)

So that working with the data will look something like this pseudo-code:

Code:

   execute query1 (for chapter/addresses only)
   outer loop (query1 results)
      print a row of chapter information
      execute query2 (use each chapter to get an officer list)
          inner loop (query2 results)
            output each officer
          end inner loop 
   end outer loop

Hope that helps.

jtpiper · #5 17-Sep-2007, 08:46

Thank You, I thought I had tried that, but evidently my 2nd query was outside the first query. Now that I have it inside, it works like a champ..

Thanks,

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
yet another linked list program	aijazbaig1	C Programming Language	15	11-Sep-2007 13:18
need help with a console menu system	BullBuchanan	C++ Forum	6	20-Aug-2006 14:46
Reading non ASCII with read()	Atomical	C Programming Language	8	13-Sep-2005 14:30
Problem with one variable	bretter	C++ Forum	1	16-May-2005 07:20
C++ file I/O	CronoX	C++ Forum	36	09-Mar-2004 17:28

return results from 2 tables with a one to many relationship

Re: return results from 2 tables with a one to many relationship

Re: return results from 2 tables with a one to many relationship

Re: return results from 2 tables with a one to many relationship

Re: return results from 2 tables with a one to many relationship