Forums

Hello all,

I am trying to make some kind of database with CC V4. This database will contain the airline name and what type of aircraft(s) it uses.
The first item (the airline name) I know what do to with it, but the second one (aircraft name) I am not sure what the best thing is to do.

Most of the airlines uses different types of aircraft. For example the Boeing 737 and the Airbus A330.
To add them to my database, do I need to make a seperate cell for each type? Or can I use a komma to seperate them in 1 cell?

On my website I want to do the following.
On my airline page I want to show the types of aircraft it uses and link them to the page of that aircraft. So if Delta Airlines uses the Boeing 767, you can click on it and go to the Boeing 767 page. I don't know if that is still possible when I use the komma seperate option.

The same thing I want to do on my aircraft page. When you are on the Boeing 767 page, you can see a list of airlines that uses the Boeing 767. Just like the airline, you can click on the airline name to go to the Airline page (in this example the Delta Airlines page).

I hope I am clear enough. If not, just ask😉

Can someone help me out with this? What is the best option to do: each aircraft type in a seperate cell or komma seperated in 1 cell?

Kind regards,
Ruud

Hi Ruud,

As this is a many-many relationship i.e. each airline can use many aircraft and each aircraft can be used by many airlines; the best solution is probably a third linking table with just two columns 'airline_id' and 'aircraft_id'. So you'd have:

Table 1 : airlines
airline_id, name . . .

Table 2 : aircraft
aircraft_id, name . . .

Table 3 : airline_aircraft
id*, airline_id, aircraft_id

Then you can interrogate the tables wither way round to get any combination of data.

Bob

* This ID is just an auto-incremented record id.

Hello Bob,

Thank you for pointing me in the right direction.
But what do you do with an airline with multiple types of aircraft? In table 3 (airline_aircraft) you will get multiple rows with the same airline_id and other aircraft_id?

Hi Ruud,

You need to make sure that there aren't any duplicate entries (though it doesn't matter too much if there are). You are aiming at one entry per airline+aircraft pair.

Bob

Hi Bob,

For each different aircrafttype of an airline I make a new row. So if an airline has 2 types, I have to rows.

I made my 3 tables and filled them with some information. Now I am trying to figure out how the CC works with multiple tables...But still no luck with that.

Is there some kind of tutorial about joining several tables in CC?

Kind regards,
Ruud

EDIT
I got the first one (aircraft name) working. In the Multi Tables Setting I set the Number of Relations to 2. In the first Relation I put my aircraft. As I join Rule I have :

Aircraft.aircraft_id1 = Airline_Aircraft.aircraft_id

In the Field Tab I use in the Field List the following field: Aircraft.name1 (Name1 is the column name that contains the aircraft name).

I save the settings and go to the Frontend List Settings. In the 'List View Fields' I place the "Aircraft.name1" (without "").

This is working. Now I go back to the Multi Tables Settings and make the second Relation. I change every value to the right one to connect my Airlines table. Also I add the field to the List View Fields in the Frontend List Settings. Save everything and then view it in the frontend. The aircraftname is visisble, but the second column is empty, only the name of the column (Airlines.name) is visible.

Hi Ruud,

Unfortunately I don't know much about the details of the new CC :-(

I would expect what you have described - reversing the settings of the aircraft_name setup - to work for the airline. The first check I would make is for typos in the table or column names like Airline instead of airline.

Bob

I will do that :wink: Thank you so far for your help!

Hi,

Just as Bob has explained, you need a 3rd table to tie many to many relationship tables, you can get the results with 2 relations under the multi tables area, 1 relation between the main table and the auxiliary table, 2nd relation is between the auxiliary table and the 2nd table.

I suggest that you enable the connection debug and see how the loaded data array is structured, you will receive a sub array of 2nd table records for every 1 record of the main table, you will need a custom listing to be able to represent this on the page, OR you can use the multi value field output format, e.g:

If your main table model is "Airline", then you can display the name using:

Airline.name

But if your 2nd table is "Aircraft", and you get multiple air crafts for the 1st airline, then you need to use this:

Aircraft.[n].name

Which should print a list of values under the name field of the 2nd table.

I hope this explains it a bit more🙂

Regards,
Max

Hello Max,

Thank you for your reply.
I am not understanding it completely.

I have 3 tables:
Aircraft_Airlines

| Airline_id | Aircraft_id |
+------------+-------------+
|      1     |      2      |
|      1     |      3      |

Aircraft

| Aircraft_id |     Name    |
+-------------+-------------+
|      2      | Airbus A319 |
|      3      | Boeing 747F |

Airline:

| Airline_id |       Name      |
+------------+-----------------+
|      1     | United Airlines |
|      2     | British Airways |

I use the Aircraft_Airline as my main table (I think that is the right one).

The last couple days I didn't try CC anymore, but I found my solution with plain php/mysql and using a custom code module to show it on my website.

This is wat I use (sorry, my php/mysql knowledge ain't very good😛. So if there are any mistakes I made in the code, just correct me :wink: ):

<?php
$con = mysql_connect("xxxx","xxxx","xxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("xxxx", $con);

$option = JRequest::getCmd('option');
$view = JRequest::getCmd('view');
if ($option=="com_content" && $view=="article") {
$ids = explode(':',JRequest::getString('id'));
$article_id = $ids[0];
$article =& JTable::getInstance("content");
$article->load($article_id);
$title_artikel = $article->get("title");

// Construct our join query
$query = "
SELECT DISTINCT 
	Airline_Aircraft.airline_id, Airlines.airline_id, Airlines.name, Airline_Aircraft.aircraft_id, Aircraft_id1, Aircraft.name1, d7ul5_content.id, d7ul5_content.title, d7ul5_content.alias "."
FROM 
	Airline_Aircraft"." 
LEFT JOIN
	Aircraft "."
ON 
	Airline_Aircraft.aircraft_id = Aircraft.aircraft_id1
LEFT JOIN 
	Airlines "."
ON 
	Airline_Aircraft.airline_id = Airlines.airline_id
LEFT JOIN 
	d7ul5_content "."
ON 
	Aircraft.name1 = d7ul5_content.title

WHERE "." Airlines.name = '$title_artikel'" ;
	 
$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 "<a href='../aircrafts/".$row['id']."-".$row['alias']."'>".$row['name1']."</a><p />";
echo "<br />";
}
}
?>

This code makes a connection between 4 tables: the 3 tables I mentioned above and the content table where all the data of the articles is stored (I uses the id and alias of an article to create the link). It also shows all the aircrafts that the airline uses by using the Airlines.name = $title_artikel.

Maybe I am doing this the long way (making a lot of LEFT JOINs), but this is the way it works for me (with my knowledge of php😛).

Well, the reason of using Connectivity is to easily build complicated relations and list data, but now you have done it the hard way!🙂