MySQL joins and linking tables

[deletedUser2352352]

Hello all.

I hope someone can help me out with this challenge.

I have three tables in my database

one called


cms_packages

another called


cms_features

and a linking table called


cms_package_features

the table structures look like this




cms_package
>package_id (pk)
>package_title
>package_dscpn

cms_feature
>feature_id (pk)
>feature_title
>feature_dscpn

cms_package_feature
>package_id (fk)
>fetaure_id (fk)



Now i can never get my head round this always seem to come unstuck.

What i want to do is list all the packages with the features including the features that package hasn't got.

so i can end up with a table looking like this.



packages --> (running along top)
feature 1 |x|x|x|x|o|
feature 2 |x|x|o|x|x|
feature 3 |x|x|o|x|x|
feature 4 |x|o|o|x|x|
feature 5 |x|x|o|o|x|



Now from what i gather i'm supposed to be using left joins but i can't for the life of me get it to look like my example.

Can anyone help please?

[deletedUser2352352]

I have this so far




SELECT a.*, c.package_title
FROM cms_feature a
LEFT JOIN cms_package_feature b
ON a.feature_id=b.feature_id
LEFT JOIN cms_package c
ON c.package_id = b.package_id



This pull out a nice set of information but it doesn't exactly do what i want.

i think what i need to do is list all the features and see if its got a package attached fill if it has leave empty if it hasn't.

[jsauni]

My MySQL syntax isn't that great as I'm still learning it at the moment.


select a.package_title, c.feature_title
from cms_package a left join cms_package_feature b on
a.package_id = b.package_id
left join cms_feature c on b.feature_id = c.feature_id
group by a.package_title

Does that work? I wrote this in tsql but tried to convert similar tags :puzzle:

I'm not sure yet about the output of this cause I don't have the tables to work with but if someone hasn't already supplied a better answer I'll try again tonight when I get home.

You'd might wanna put a where in there like "where c.feature_title is not null" or something. I dunno but good luck.

[prstudio]

K what I'm about to give you is not solid and not 100% correct, but it should get you thinking along the lines of what you need to do. I don't want to give you the whole thing... and I don't have time lol. So here you go:

Think of populating the grid... left to right. 1 2 3 4 5

Then picture drawing it from there down... A1 B2 C3 D4.

so obviously you have to loop through something to go left to right... then loop through something to go top to bottom.

Ok that's great, but it doesn't fill in the blanks... that means these blanks have to be related... hence your thinking about joins and the overall point. This means that if you want to associate features to packages...

well packages was left to right - and features was top to bottom... this means you are going through packages first, then features and seeing if those features relate...

this would make a nested loop:

as noted in the sample code - you can use a join on the second interior loop to gather feature data.

and of course you could probably do this whole thing just using an array. joins wouldn't be the best bet and in my opinion ultimately would not work - as it would not account for every situation.

I would use a nested loop that first:

Called the package records.

then inside that call - i would place another loop that ran through the feature list - each record being a new feature... feature 1 ... feature 2... etc.

then I would do an if/then statement that checked to see if that package id had been assigned to the feature... if so, then place a X if not then place a O.



$packagequery = " SELECT * FROM cms_package ";
$result = mysql_query($packagequery);
while( $row=mysql_fetch_array($result) ){

$package_id = $row['package_id'];
$package_title = $row['package_title'];
$package_dscpn = $row['package_dscpn'];

# echo some sort of horizontal div titles #
echo('<div>$package_title</div>');

# now to populate the rows beneath - you would list the field names - then the data inside #
### Now the feature query and loop. ###
### Technically to display feature information - you would do a JOIN here. ###
$featurequery = " SELECT * FROM cms_package_feature ";
$featureresult = mysql_query($featurequery);

while( $featurerow=mtswl_fetch_array($featurequery)){

$feature_package_id = $featurerow['package_id'];
$feature_feature_id = $featurerow['feature)id'];

if ($feature_package_id = $package_id) {
echo "X";
} else {
echo "O";
}
}


}
mysql_close();

hope it gets you thinking along the right lines - id just sit back and think through it - draw it out on paper - build your actual "html grid" and once you see it visually will hopefully inspire how the code should work...

[deletedUser2352352]

ah ha now this is strange as i have done this to originally get the information. and it works. The next thing is i have a feature_order that contains the order that the features must be in. But it doesn't put them into order.

This is why i was trying to find some joins and such.
I'll take another look thanks both.

:)

[deletedUser2352352]

cheers.

I sorted it using your method above using my exsiting code. I just did an order by on the table i missed.

not pretty but i will sort this out



// get the features
$this->db->orderby("feature_order", "asc");
$query = $this->db->get($this->_feature);
$feature = $query->result();


$sql = 'SELECT a .*
FROM cms_package a, cms_package_global b
WHERE a.package_id = b.package_id
AND b.global_id = '.$this->_global_id.'
ORDER BY a.package_order DESC';
$query = $this->db->query($sql);
$packages = $query->result();

$output = '';

foreach($feature as $key => $value)
{

$sql = 'SELECT a .*, b.package_id
FROM cms_feature a, cms_package_feature b
WHERE a.feature_id = b.feature_id
AND b.feature_id = '.$value->feature_id.'
ORDER BY a.feature_order ASC';
$query = $this->db->query($sql);
$get_packages = $query->result();

$whatPackages = array();
foreach($get_packages as $gfi => $gf){
$whatPackages[] = $gf->package_id;
}

$output .='<tr>';
$output .='<td>'.$value->feature_title.'</td>';

foreach($packages as $no => $get){
if(in_array($get->package_id, $whatPackages)){
$output .='<td><img src="'.base_url().'default_image_folder/tick.png" alt="yes" /></td>';
}else{
$output .='<td><img src="'.base_url().'default_image_folder/cross.png" alt="no" /></td>';

}
}

$output .= '</tr>';
}

return $output;