Problem With Php Code To Generate A Stock List

[Sickboards]

Hi,

 

I am making a small php script to generate a stock list but for some reason it does not seem to execute the 2nd sql statement, probably a really silly mistake, some help is appreciated.

<?php 
include '../config/settings.inc.php';
include '../config/defines.inc.php';
include '../config/config.inc.php';
include '../init.php';

$sql = 'SELECT DISTINCT p.id_product, pl.name, p.price, p.quantity FROM ps_product as p INNER JOIN ps_product_lang as pl ON pl.id_product = p.id_product INNER JOIN ps_product_shop as ps ON ps.id_product = p.id_product WHERE pl.id_lang = 1 AND pl.id_shop = 1 AND ps.id_shop = 1 AND ps.active = 1 ORDER BY pl.name';
echo "<table width='600' cellpadding='1' cellspacing='1' border='1'>";
echo "<tr><td><b>Product</td><td>Attribute</td><td align=\"center\"><b> Quantity </td><td><b>Actual Quantity</td></tr>";

if ($results = Db::getInstance()->ExecuteS($sql)) 
{
	foreach ($results as $row) 	
	{
	//generate product name and id
		$name = $row['name'];
		$id_product = $row['id_product'];

		// check if product has attribute and loop through them
		$sql1 = 'SELECT * FROM ps_product_attribute WHERE id_product = $id_product';
		if ($results1 = Db::getInstance()->ExecuteS($sql1)) 
		{
			foreach ($results1 as $row1) 
			{
				$id_prod_att = $row1['id_product_attribute'];
				// loop through and merge the attributes 
				$sql2 ='SELECT DISTINCT pac.id_product_attribute, al.name, sa.quantity FROM ps_product_attribute_combination as pac INNER JOIN ps_attribute_lang as al ON al.id_attribute = pac.id_attribute INNER JOIN ps_stock_available as sa ON sa.id_product_attribute = pac.id_product_attribute WHERE pac.id_product_attribute = $id_prod_att AND al.id_lang = 1';
				$att_name ='';
				if ($results2 = Db::getInstance()->ExecuteS($sql2)) 
				{
					foreach ($results2 as $row2) 
					{
						$att_name = $att_name . " - " . $row2['name']; 
						$quantity = $row2['quantity'];
					}
				}
			}
		} else {
			// no attributes
			$sql3 = 'SELECT quantity FROM ps_stock_available WHERE id_product = $id_product';
			$quantity = Db::getInstance()->getValue($sql3);
		}
	echo "<tr><td>" . $name . "</td><td>" . $att_name . "</td><td align=\"center\"> " . $quantity . " </td><td width = \"50\"></td></tr>";
	}
}
echo "</table>"; 
?>

[Sickboards]

Thanks! 

 

It is working (for who whats to use it), here is the working code (there were still a few mistakes):

<?php
include '../config/settings.inc.php';
include '../config/defines.inc.php';
include '../config/config.inc.php';
include '../init.php';

$sql = 'SELECT DISTINCT p.id_product, pl.name, p.price, p.quantity
        FROM ps_product as p
        INNER JOIN ps_product_lang as pl ON pl.id_product = p.id_product
        INNER JOIN ps_product_shop as ps ON ps.id_product = p.id_product
        WHERE pl.id_lang = 1
        AND pl.id_shop = 1
        AND ps.id_shop = 1
        AND ps.active = 1
        ORDER BY pl.name';
echo "<table width='600' cellpadding='1' cellspacing='1' border='1'>";
echo "<tr><td><b>Product</td><td>Attribute</td><td align=\"center\"><b> Quantity </td><td><b>Price</td></tr>";

if ($results = Db::getInstance()->ExecuteS($sql))
{
    foreach ($results as $row)
    {
        //generate product name and id
        $name = $row['name'];
        $id_product = (int)$row['id_product'];
		$price = (float)$row['price'];
		$att_name ='';
        // check if product has attribute and loop through them
        $sql1 = 'SELECT id_product_attribute, price FROM ps_product_attribute WHERE id_product = '.(int)$id_product;
        if ($results1 = Db::getInstance()->ExecuteS($sql1))
        {
            foreach ($results1 as $row1)
            {
                $id_prod_att = $row1['id_product_attribute'];
				(float)$att_price = $price + (float)$row1['price'];
				//echo $name ." attribute nr: " . $id_prod_att . "<br>";
                // loop through and merge the attributes
                $sql2 ='SELECT DISTINCT pac.id_product_attribute, al.name, sa.quantity
                        FROM ps_product_attribute_combination as pac
                        INNER JOIN ps_attribute_lang as al ON al.id_attribute = pac.id_attribute
                        INNER JOIN ps_stock_available as sa ON sa.id_product_attribute = pac.id_product_attribute
                        WHERE pac.id_product_attribute = '.(int)$id_prod_att.'
                        AND al.id_lang = 1';
                $att_name ='';
                if ($results2 = Db::getInstance()->ExecuteS($sql2))
                {
                    foreach ($results2 as $row2)
                    {
                        $att_name = $att_name . " " . $row2['name'];
                        $quantity = $row2['quantity'];
                    }
                }
				echo "<tr><td>" . $name . "</td><td>" . $att_name . "</td><td align=\"center\"> " . $quantity . " </td><td width = \"50\">" . $att_price . "</td></tr>";
            }
        } else {
            // no attributes
            $sql3 = 'SELECT quantity FROM ps_stock_available WHERE id_product = '.(int)$id_product;
            $quantity = Db::getInstance()->getValue($sql3);
			echo "<tr><td>" . $name . "</td><td>" . $att_name . "</td><td align=\"center\"> " . $quantity . " </td><td width = \"50\">" . $price . "</td></tr>";
        }

    }
}
echo "</table>";