link qty to an mjoin

Jimber

I'm fairly new here and not good at php, but I'll try to explain this as clearly as I can. I have 2 tables. The first contains Part Numbers and everything that goes with them (description, file attachments, etc.). The second has Order numbers and I did an mjoin between the tables so I can link multiple Part numbers from the first table to each Order number. I have to be able to link the same Part no. to multiple orders with only the Quantity changing. I did this by making another table on mysql with 3 fields: Order no., Part no. & Quantity. Everything works as expected except that I can figure out how to get the quantities with php.
Here's my mjoin.

->join(
    Mjoin::inst( 'tblParts' )
        ->link( 'tblOrders.id', 'order_part_quants.order_id' )
        ->link( 'tblParts.id', 'order_part_quants.part_id' )
        ->order( 'part_ asc' )
        ->fields(
            Field::inst( 'id' )
                ->validator( Validate::required() )
                ->options( Options::inst()
                    ->table( 'tblParts' )
                    ->value( 'id' )
                    ->label( 'part_' )
                ),
            Field::inst( 'part_' )
        )
)

The three tables involved are
tblOrders
tblParts
order_part_quants
I have successfully linked parts to orders, but I need to return the quantities from the same table row of order_part_quants table at the same time. I'm sure it's possible, but have spent hours and can't seem to get it on my own.

Do I need to create a separate join to get the quantities? This has me stuck, because both order number and part number have to match for a quantity. Sorry about my terminology. I'm very inexperienced in php. If more information is needed, I can give that. I included only what I thought would be needed. I'll appreciate any suggestions. Thanks in advance!

Jimber

I meant to say can't instead of can, in the 2nd last sentence before the code.

Jimber

Typing out exactly what my problem was, helped me to solve it. I think I've just about got it now. I was using the wrong table in "options".

allan

Hi Jimber,

Thanks for the update, great to hear you are on the right path with it. Give me a shout if you run into any further issues.

Allan