[SOLVED] Joining 3 Tables together with different field names for the same thing

[johimbe]

Hi Community! I`m stuck on a (maybe simple) thing but cannot figure it out myself - that`s why im posting here and hoping really hard, that someone could help me:

 

My Mysql Client Version is : MySQL-Client-Version: 4.1.22

and my PHP version is 4.4.4

 

the thing i want to do is as follows - im trying to build an advanced search feature for the zen-cart shop system, and have added some tables to the database which can hold extra information about products:

 

I have 3 Tables called:

 

products_acoustics

products_acoustics_second

products_acoustics_third

 

each table contains a field products_id and a field for the acoustics information which can be values from A to E.

the three fields for the acoustics information are called

class_of_absorbtion (for table products_acoustics)

class_of_absorbtion_second (for table products_acoustics_second)

class_of_absorbtion_third (for table products_acoustics_third).

They represent all the same information,  but a product can have differnet values in each of these tables. For exmple a product can have the value A for class_of_absorbtion, a value B for class_of_absorbtion_second and a value C for class_of_absorbtion_third. They can be all the same as well.

 

so i have only one search field for class_of_absorbtion which should look in all three tables if one value is corresponding to the searched term.

i think i need to "left join" these tables together so i can get the coresponding product with just one "where" clause, right?

 

I tried the following (from clause)

 

$from_str = "FROM (" . TABLE_PRODUCTS . " p

LEFT JOIN " . TABLE_MANUFACTURERS . " m

USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )

LEFT JOIN " . TABLE_PRODUCTS_EXTRA_STUFF. " pu

ON pu.products_id= p2c.products_id


[color=red]LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS. " pac
ON pac.products_id = p2c.products_id
LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS_SECOND. " puc
ON pac.products_id = puc.products_id
LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS_THIRD. " pic
ON pac.products_id = pic.products_id[/color]


LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd

ON mtpd.products_id= p2c.products_id

AND mtpd.language_id = :languagesID";



$from_str = $db->bindVars($from_str, ':languagesID', $_SESSION['languages_id'], 'integer'); 

 

and here is the where clause i tried:

if (isset($_GET['class_of_absorbtion']) && zen_not_null($_GET['class_of_absorbtion'])) {

[color=red]$where_str .= " AND (pac.class_of_absorbtion
OR puc.class_of_absorbtion_second
OR pic.class_of_absorbtion_third) = :absoID";
$where_str = $db->bindVars($where_str, ':absoID', $_GET['class_of_absorbtion'], 'string');[/color]


} 

 

I know i must have gotten something wrong, because still only the first table "products_acoustics" and the field "class_of_absorbtion" is searched and not the products_acoustics_second - and third one. If i change "pac.class_of_absorbtion" to puc.class_of_absorbtion_second, only the second table is searched, so the tables are accessible somehow, but my mysql knowledge is so basic, that i can`t find the problem and am to blind to see the right code even after reading some tutorials about join in mysql. If anyone could help me a little on this, would be sooo incerdibly nice - because i`m stuck on this for days now... Hope anyone does help me. Would be  Thank you!

johimbe!

 

 

[kickstart]

Hi

 

Think you are joining the 3 class_of_absorbtion tables together on the wrong fields.

 

Also you cannot do your WHERe clause how you have done.

 

Try these bits:-

 

$from_str = "FROM (" . TABLE_PRODUCTS . " p
LEFT JOIN " . TABLE_MANUFACTURERS . " m
USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )
LEFT JOIN " . TABLE_PRODUCTS_EXTRA_STUFF. " pu
ON pu.products_id= p2c.products_id

LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS. " pac
ON p2c.products_id = pac.products_id
LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS_SECOND. " puc
ON p2c.products_id = puc.products_id
LEFT JOIN " . TABLE_PRODUCTS_ACOUSTICS_THIRD. " pic
ON p2c.products_id = pic.products_id
LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd
ON mtpd.products_id= p2c.products_id
AND mtpd.language_id = :languagesID";

$from_str = $db->bindVars($from_str, ':languagesID', $_SESSION['languages_id'], 'integer');

if (isset($_GET['class_of_absorbtion']) && zen_not_null($_GET['class_of_absorbtion'])) 
{
$where_str .= " AND :absoID IN (pac.class_of_absorbtion, puc.class_of_absorbtion_second, pic.class_of_absorbtion_third) ";
$where_str = $db->bindVars($where_str, ':absoID', $_GET['class_of_absorbtion'], 'string');
}

 

All the best

 

Keith

[johimbe]

well now i solved it - was just a tiny little thing, but if no one helps these things are sometimes hard to find

 

here`s the code for the correct where clause

$where_str .= " AND (pac.class_of_absorbtion=:absoID
OR puc.class_of_absorbtion_second=:absoID
OR pic.class_of_absorbtion_third=:absoID)";