SQL - Channel, Query, or Parsing Issues

This one is beating me down and i’m running into walls all over the place.

SCENARIO: We sell products and get those products via a daily database dump. This dump has a different DB prefix (stn_) than expression engine (exp_). I’d like to relate a product in our stn database with exp. This relation goes beyond a simple relationship select as i want it to be auto related. My problem is the mix of PHP query and expression engine channel entries.

HERE IS SOME SAMPLE CODE:

{exp:query sql=”SELECT entry_id AS entry_ids FROM exp_channel_data a INNER JOIN stn_offer_destinations b ON a.field_id_106 = b.offer_dest_id INNER JOIN stn_product_offer_destinations c ON b.offer_dest_id = c.offer_dest_id INNER JOIN stn_product_offers d ON c.offer_id = d.offer_id WHERE d.sup_id = ‘3723’ GROUP BY b.offer_dest_id”}

{exp:channel:entries channel="at_destinations" entry_id="{entry_ids}|” status=”open|featured|Home Page”}

HTML markup including {title} {images..} etc.

{/exp:channel:entries}
{/exp:query}

THE PROBLEM: In the case above i’m using the offer_dest_id to relate a field in our exp_ database to the stn_ database. This shoots out entry id’s of different destinations relating to the product. The above query works great, except that the sup_id needs to be dynamic (not 3723). I’ve used PHP in the past to pull segment_2 and relate it do a field that contains this sup_id in EE, then use a PHP echo to populate. See below:

{exp:channel:entries channel="at_vendors" url_title="{segment_2}” orderby=”date” sort=”asc” dynamic=”off” status=”open|featured|homepage” disable=”member_data|trackbacks|categories”} {vendor_id} <?php $vendor_id = ‘{value}’; ?> {/vendor_id} {/exp:channel:entries}

The problem is that i cannot echo $vendor_id in the exp:query.

I’ve also tried doing all of this with PHP, but then the PHP loads after the exp:channel so it’s not populating the entry_id= field. I tried loading the PHP first using input, but then it loads before the exp:channel to pull sup_id with segment_2. The combination of PHP an EE are killing me here.

Any suggestions?