Adding another table to a multitable query

[Buchead]

Hello.........

I've been presented with a query that works and have been asked to expand what it does. Unfortunately I know what it should do but not how to code it.

The database has 4 tables: "products" which contains product info. "product_tree" which lists the productID from "products" and a sectionID. "advancedpricing" which contains info regarding pricing. "sections" which contain the sectionID and relationships between sections.

The currect query is:

select products.* ,advancedpricing.price1 as advPrice1 from products LEFT JOIN advancedpricing ON products.productID=advancedpricing.productID and advancedpricing.priceType=0 and (advancedpricing.accTypeID=0 or advancedpricing.accTypeID=1) ) where (products.productID > 1) and (accTypes like '%;1;%' or accTypes like '%;0;%') and (visible = 'Y') and visible = "Y" and ((code like "%$search%" or name like "%$search%" or description like "%$search%" or keywords like "%$search%")) group by products.productID order by name LIMIT 0,10

This works, but I've been asked to have it so it only searches for products in a given section, and it's sub-sections, so adding the products_tree table into the mix. I've tried adding "products_tree.sectionID=$section" after the where identifier, and also products_tree before and after the LEFT JOIN, but it fails. I've resorted to seemingly adding obviously incorrect code into the query in a bid to get it working!

Can anyone point out where I'm going wrong?

Many thanks,

Clive.

[shoz]

If everything currently works as it should then adding

[code]
INNER JOIN product_tree ON products.productID = product_tree.productID
[/code]

after "from products" and then adding "AND sectionID = $section" after "(products > 1)" should work. I recommend you test the current query and the new query throroughly

[Buchead]

That does the job (thanks), however I also need to do a check in the sections table for any parent/child relationships. products_tree lists all the products and which section they go in, while in sections, it lists all the sections, their sectionID and the sectionID of their parent.

So if main = 1, sub1 = 2, sub2 = 3, and sub3 is beneath sub1 it will have a sectionID of 4 but with a parent of 2 - the others will have a parent of 1.

Thanks!

[shoz]

[quote author=Buchead link=topic=112054.msg454646#msg454646 date=1161290034]
That does the job (thanks), however I also need to do a check in the sections table for any parent/child relationships. products_tree lists all the products and which section they go in, while in sections, it lists all the sections, their sectionID and the sectionID of their parent.

So if main = 1, sub1 = 2, sub2 = 3, and sub3 is beneath sub1 it will have a sectionID of 4 but with a parent of 2 - the others will have a parent of 1.

Thanks!
[/quote]

Is there a limit on the depth of the subcategories that can be made or is it unlimited? For instance could sectionID 4 have a subcategory and then could that subcategory have another subcategory ....?

[Buchead]

At the moment there's only 1 level of sub-category, and there are no plans for any further levels.

Hope this helps. Thanks.

[shoz]

With the current limit you can use the following to retrieve the section ids of subcategories. Note that This could possibly be worked into the original query.
[code]
<?php
$section = (int)$section;
$section_ids = array($section);
$query = 'SELECT '
        .'sectionID '
        .'FROM '
        .'sections AS s'
        .'WHERE '
        .'s.parentID  = '.$section;
$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result))
{
    while ($row = mysql_fetch_assoc($result))
    {
        $section_ids[] = $row['sectionID'];
    }

}
$section_ids = implode(',', $section_ids);
?>
[/code]

You can use the following to retrieve the section_ids of the subcategories regardless of depth.
[code]
<?php
$section_ids = array();
$new_ids = array((int)$section);
do
{
    $section_ids = array_merge($section_ids, $new_ids);
    $query = 'SELECT sectionID FROM sections WHERE parentID IN ('.implode(',', $new_ids).')';
    $result = mysql_query($query) or die(mysql_error());

    $new_ids = array();
    if (mysql_num_rows($result))
    {
        while ($row = mysql_fetch_assoc($result))
        {
            $new_ids[] = $row['section_id'];
        }
    }
} while (count($new_ids));

$section_ids = implode(',', $section_ids);
?>
[/code]

In each case you'll change "AND sectionID = $section" to "AND sectionID IN ($section_ids)".

You should have a test for whether or not the section is 1, in which case you'd generate a query that either  removes the sectionID comparison in the WHERE clause or makes it "AND sectionID" only or completely removes the JOIN on table "sections", removing the related WHERE conditions as well.

EDIT: Btw, you'll probably want a multicolumn index on (parent, section) and (section, parent)

[Buchead]

Fantastic, that worked perfectly. Thanks very much for the assistance.

[shoz]

Note that I've changed the first query. It was using a JOIN when there didn't need to be one. Which really means that the second code snippet can/should be used regardless.