Forums

Hello all,

I have a drop down menu that contains all the unique airportcodes. It gets the codes from the table in my mysql database. This is a list of all the airportcodes that are in the column "origin". I also have a table with all the airportcodes in the world, the full name of the airport and the country. In my drop down menu I want to show the full airport name and the airportcode, sorted by country (with the <optgroup> thing).

Okay, this sound a bit difficult, so here is my situation.

movements_ewas


movements_ewas_airportlist (contains the airportcode, fullname, country)


So the thing I would like to have is as follow:
The drop down gets the airportcode from "orig". It checkes the airportcode in the "icao" column and if it matches, it gives the fullname ("airport").

I have to work with joins. But I don't know how to fix this. I made a simple php file with a working example, but I don't know how to put it into CC.
PHP-code from file:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT DISTINCT movements_ewas_airportlist.icao,movements_ewas_airportlist.airport,movements_ewas_airportlist.country, movements_ewas.orig ".
 "FROM movements_ewas, movements_ewas_airportlist ".
	"WHERE movements_ewas_airportlist.icao = movements_ewas.orig";
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
	echo $row['airport']."-". $row['country'];
	echo "<br />";
}

This is the result:
http://www.aviation-pics.com/mysqltest.php

Thi is my code in CC:

<?php
$db =& JFactory::getDBO();
$count = $db->loadResult();

$query = "
SELECT `movements_ewas_airportlist.icao`, `movements_ewas.orig` FROM `movements_ewas_airportlist`, `movements_ewas` WHERE `movements_ewas_airportlist.icao` = `movements_ewas.orig`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<select style='width: 210px;' class='cf_inputbox validate-selection' name='orig' id='select' size='1'>
<option value=''>Kies een herkomst</option>

<?php
foreach ( $data as $d ) {
$selected = '';
if ( $d->id == $icao ) {
$selected = "selected='selected'";
}
echo "<option value='".$d->icao."'>".$d->icao."</option>";
}
?>
</select>

I hope someone can help me with this🙂

Hi flyboeing,

I'm not clear where the outputs of this are going? Is it a drop-down to display in the Header of the listing, or something that you want to show in the row detail?

Bob

It is for a filter.

I have made a searchform in ChronoForms (with the action="" it goes to the CC form).
It the drop-down list I would like to show the airports (fullname) where the user can choose from, sorted by country.

I am not getting it.
When I make a query to select the different airportcodes from 'orig' column it is working. But when I am trying to connect to the other table (making a connection to 2 tables), I get an empty drop down menu.

I think I am making some kind of mistake in the query. Can someone help me with this?

Hi flyboeing,

What query are you using? ChronoConnectivity doesn't support compound queries for the main data but it should be possible to write one to include a drop-down in the Header box.

Bob