[SOLVED] Joining 2 tables

Hi all,

A couple of months ago I started to convert my CC V2 data to V4. I hadn't much time to finish it, so I am starting with it again.

I have a piece of code that worked fine in V2, but I can't get it to work in V4. Maybe I am just missing some small details...

<?php
//Get Airport name
    $db =& JFactory::getDBO();
    $db->setQuery("SELECT movements_ewas.orig, movements_ewas_airportlist.icao, movements_ewas_airportlist.airport 
	FROM 
	movements_ewas 
	LEFT JOIN
	movements_ewas_airportlist
	ON
	movements_ewas.orig = movements_ewas_airportlist.icao
	
	WHERE movements_ewas_airportlist.icao = '$row->orig'");
    $results = $db->loadObjectList();
    foreach($results as $result){
echo "<a class='linkhover' title='{icao}'>{orig}</a>";
  }
  ?> 

The code above is needed to display the airport name when you hover over the icao-code. In my table only the ICAO-code is displayed so when people hover over the code, the full airportname is shown.

The airportname and the icao code are stored in a seperated database (movements_ewas_airportlist). In my main table (movements_ewas) there is a column called 'orig'. Here the ICAO-code of the airport is shown. So I need to make a join with an other table (I thought the left join would do the trick).

I hope someone can help me with this.

Regards,
Ruud

Hi Ruud,

are stored in a seperated database

they are really on a different database or a different table you mean ?

You can use the same code you have here since you have the full query already, or this doesn't work ?

Regards,
Max

Hi Ruud,

are stored in a seperated database

they are really on a different database or a different table you mean ?

You can use the same code you have here since you have the full query already, or this doesn't work ?

Regards,
Max

Two different tables (sorry😶).
When I use the code above, it won't display anything.

Hi Ruud,

Instead of adding an extra query for each row can't you use the Multi-Tables settings to Join the two in the listing?

Bob

Hi Bob,

Do you mean the "Multi tables settings" tab? I took a look at that, but I don't understand is completely.

De code above is part of a larger code to display the information. I placed this in the "body" of the "Frontend List Settings".

Regards,
Ruud

Hi Ruud,

Yes, that's the tab I was thinking of (like you, I don't fully understand it but I think that there is a FAQ about it).

The problem with including MySQL queries in the body is that they are run once for each row displayed. It is generally better to get all the information before processing the body section by running the MySQL queries in CC, or in the Header box.

Bob

Hi Bob,

I managed to get it to work😀
I followed this FAQ to get the join function working.
After that I could replace the code above with:

<a class="linkhover" title="{movements_ewas_airportlist.airport}">{movements_ewas.dest}</a>

So when I go to my database I can hover over the ICAO-code to see the airportname.

But now the last part of my database is not working. The code below is not working. This code will check if a column is empty or filled with data. If there is any data in record cell, an image is shown and when you hover over the image you will get some information. If the cell in that record is empty there will be no image shown.

<?php
$icon = '';
if ( $row['owner'] ) {
  $image = 'no_image.png';
  if ( $row['section'] == 'Civil' ) {
    $image = $row['owner2'].'_'.$row['owner'].'.png';
  } elseif ( $row['section'] == 'Militair' ) {
    $image = 'roundels/'.$row['owner'].'.png';
  }
  echo "<a href='#' >
  <img src='http://......./images/logo/{$image}' title='{owner2}' /></a>";
}
?>

I tried to replace the $row['owner'] with {movements_ewas.owner} but that didn't work😟.

Any idea to fix this?

Regards,
Ruud

Hi Ruud,

Please try

$row['movements_ewas']['owner']

Bob

Hi Bob,

So simple, but I didn't think of that😶

Thanks!😀

Regards,
Ruud

Hi Ruud,

The ChronoForms and ChronoConnectivity Model IDs basically create sub-arrays in the $form->data array (for CF) or the $row array (for CC) so the PHP sub-array syntax works.

Bob

Ah ok🙂 thanks for the extra info.