Need mySQL Query Help

programming
1

I have Joomla 1.13 installed and am running the most current version of Mosets Tree for a directory. The directory lists places in the US. I have custom built and installed a component that allows users to collect those places into custom travel guides. They can then pull up a listing of all of the places in a specific guide and print it out. This all worked find until Mosets updated Tree. They moved the custom fields from the mt_links table to a new table called cf_values. Each custom field gets a cf_id and then a value. For instance, I geocode a location and save the latitude and longitude value into this table. Lat values have a cf_id of 28, long values are cf_id 30. The the column Values has the actual lat and long data.

I have a query pulling the data for each trip listing:

$query = "SELECT dtd_link2trip.trip_id, dtd_mt_links.*, dtd_mt_cfvalues.*"
      ."
 FROM dtd_link2trip LEFT JOIN dtd_mt_links"
      ."
 ON dtd_link2trip.trip_id = $trip_id"
      ."
 AND dtd_link2trip.link_id = dtd_mt_links.link_id"
      ."
 LEFT JOIN dtd_mt_cfvalues"
      ."
 ON dtd_link2trip.trip_id = $trip_id" 
      ."
 AND dtd_link2trip.link_id = dtd_mt_cfvalues.link_id"
      ."
 WHERE dtd_link2trip.trip_id = $trip_id"
      ."
 AND dtd_mt_cfvalues.cf_id = 30"
      ."
 ORDER BY dtd_mt_links.city, dtd_mt_cfvalues.value ASC"
      ;
$database->setQuery( $query );
$rows = $database->loadObjectList();

   foreach ((array) $rows AS $row ) {

Then I can call $row->link_id, $row->address, etc. Since these custom fields were in the mt_links table, I could just call $row->cust_3, but now that doesn’t work. I’m not sure what to do to pull these cf_values rows beyond the one you already see in the query above. (Cf_id 30 is type of listing…hotel, battlefield, etc.) I can then use the results (in part) to map the listing location on Google Maps.

To make matters more confusing, the lat and long values will only be one for each listing, but there are a couple of other custom fields that may be pulling more than one value per listing, so they would be arrays within this foreach.

Any suggestions on how to handle pulling this data for these listings?

Can anyone offer me some guidance please, please, please?! I’m going nuts over this.

Thanks!