PHP Question

[ChetUbetcha]

I tried finding this on other PHP forums, but got no reply.  Can someone help me?

Hypothetically, I have two tables:

1. main_office, with field main_office (unique)

2. sub_office, with fields main_office (matches above main_office) & sub_office

If main_office table field main_office is populated with:
MAIN1
MAIN2
MAIN3

and sub_office table has fields main_office and sub_office populated with:
MAIN1, SUB1
MAIN1, SUB2
MAIN1, SUB3
MAIN2, SUB1
MAIN2, SUB2
MAIN3, SUB1

I want to find all sub_offices from the 2nd table (sub_office) that match to their corresponding main_office from the first table (main_office).

I'd like the display to be:
MAIN 1
...SUB1
...SUB2
...SUB3

MAIN 2
...SUB1
...SUB2

MAIN 3
...SUB1

How do I do this? JOIN? LEFT JOIN? INNER JOIN? And how do I get it to go through everything and have main_office listed once with all the associated sub_office fields listed below?

[jacortina]

Is it possible to have a 'MAIN' entry with NO (zero) sub_office entries in the second table? If not, all the information you want will always be in the second table. If so, you want to use a LEFT JOIN (something like, I believe):

Code Select Expand

    $query2 = db_query("
        SELECT m.main_office, s.sub_office
        FROM main_office as m
        LEFT JOIN sub_office as s ON (m.main_office = s.main_office)
        ORDER BY m.main_office, s.sub_office", __FILE__, __LINE__);

Presentation is another thing.

Code Select Expand

    $last_main = '';
    while ($row = mysql_fetch_assoc($query)) {
        if ($row['main_office'] != $last_main)
            {
            echo '<br />';
            echo $row['main_office'], '<br />';
            $last_main = $row['main_office'];
            }
        if (isset($row['sub_office'])
            echo '.&nbsp;.&nbsp;.&nbsp;',$row['sub_office'], '<br />';
    }

[ChetUbetcha]

Yes, there can be main offices with no sub offices.  The way they will be created is to make a main office and then click Create Sub Office while on the display page of available main and sub offices.

[bloc]

Seems rather uncessary to have two tables for this, when both Main office and Sub office are essentially same type of data.

If it was me I would rather use just one table and add a "parent" field, that way you can search for sub-offices of a main offcie just be specifying "WHERE parent=main-office-id". If you need a listing of all, you could simply read the whole table and do some simple php sorting, thats probably better than using joins for this.

On the other hand..if the data for sub-office are different than main offices, then use two tables.

[ChetUbetcha]

duh...  I totally forgot about that.  Now that you mention it, I can picture that example they have on php.net.  Thanks for mentioning that and dislodging the brain flatulence.

* poot *

[ChetUbetcha]

Seems rather uncessary to have two tables for this, when both Main office and Sub office are essentially same type of data.

If it was me I would rather use just one table and add a "parent" field, that way you can search for sub-offices of a main offcie just be specifying "WHERE parent=main-office-id". If you need a listing of all, you could simply read the whole table and do some simple php sorting, thats probably better than using joins for this.

On the other hand..if the data for sub-office are different than main offices, then use two tables.

Worked like a charm.  Thanks again!