SQL query and active record

[eluser]bugboy[/eluser]
Hello All

Hope your all having a great day.

I have a question and its to do with MySQL. (not my strongest point).

I need to do what seems like a simple search but for the life of me not work out what to do.

I have a table structure like this.

Code:

cms_stove
id (pk)
title
dscpn

cms_flu
id (pk)
title
dscpn

// linking table
cms_stove_flu
stove_id (fk)
flu_id (fk)

each stove thats been inputted can have more then one flu type.

I need to do a search on this so i can from a html check box list of the flu types get all the stoves without duplicates that have those flu types.

I would do a search but i don't even know where to begin on this. Any pointers or links or help would be greatly appecitated.

Thanks

[eluser]Bramme[/eluser]
You'll have to use an inner join if I'm not mistaken.
Err, smth like this should do the trick (might not be 100% correct)

Code:

<input type="checkbox" name="flutype[]" value="type-id-for-foo" />Foo
<input type="checkbox" name="flutype[]" value="type-id-for-bar" />Bar
....

<?php
foreach($_POST['flutype'] as $type) {
    $types .= $type.',';
}
$types = substr($types, 0, strlen($types) - 1); //delete last , you know have a string with all the selected types their id's

$qry = mysql_query("
        SELECT cms_stove.title
        FROM cms_stove
        INNER JOIN cms_flu, cms_stove_flue
            ON (
                cms_flu.id = cms_stove_flu.flu_id
                AND cms_stove.id = cms_stove_flu.stove_id)
        WHERE flu_id IN (".$types.")
        GROUP BY cms_stove.title
");
echo "found stoves:";
while($title = mysql_fetch_assoc($qry)) {
    echo $title;
}
?>

If this code is error free I'm a genius, it's been ages since I've used inner joins and I'm not too sure about the WHERE ... IN syntax either. But I think it should get the job done.

[eluser]bugboy[/eluser]
cheers dude

I'll try that in a bit and get back to you.

Man this community is ace.