Add A field from to database to product-list

[kesaven]

I am currently working on the following site: http://www.vente2site.fr/

I have a table called annonces which contains a field "debut"

 

I need to add this to my page in the place of the visit counter

Can anyone guide me on how to this field to the GetProducts function so that I can display it?

 

below is my table stucture:

 

Thank you in advance

[vekia]

hello

 

sorry but i totally don't understand what you expect

you want to display "debut" field from database in the visit counter block? and it is related to getProducts function?

sorry but i'm totally lost

[gonebdg - webindoshop.com]

It depends on the sql SELECT statement used in your sql query
 

You should check the getProducts() function that you used.
Is`debut` field is selected ?

 

If the getProducts() function is use SELECT * , you can retrieve the `debut` value with variable $row['debut'] in your foreach statement

if the getProducts() function is use SELECT `field_1`, `field_2`, you should add `debut` in SELECT statement
e.g SELECT `field_1`, `field_2`, `debut`
and then you can retrieve the `debut` value

[vekia]

debut is a part of other table, so in this case it will be necessary to change the query in core.

[kesaven]

Hello

 

below is my query

public static function getProducts($id_lang, $start, $limit, $orderBy, $orderWay, $id_category = false, $only_active = false)
    {
        if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
            die (Tools::displayError());
        if ($orderBy == 'id_product' OR    $orderBy == 'price' OR    $orderBy == 'date_add')
            $orderByPrefix = 'p';
        elseif ($orderBy == 'name')
            $orderByPrefix = 'pl';
        elseif ($orderBy == 'position')
            $orderByPrefix = 'c';

        $rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
        SELECT p.*, pl.* , t.`rate` AS tax_rate, m.`name` AS manufacturer_name, s.`name` AS supplier_name
        FROM `'._DB_PREFIX_.'product` p
        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
            AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
            AND tr.`id_state` = 0)
        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
        LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.
        ($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'
        WHERE pl.`id_lang` = '.(int)($id_lang).
        ($id_category ? ' AND c.`id_category` = '.(int)($id_category) : '').
        ($only_active ? ' AND p.`active` = 1' : '').'
        ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).
        ($limit > 0 ? ' LIMIT '.(int)($start).','.(int)($limit) : '')
        );
        if ($orderBy == 'price')
            Tools::orderbyPrice($rq,$orderWay);
        return ($rq);
    }

 

Where will i get the field "debut" here?

[vekia]

just add additional join:

LEFT JOIN `'._DB_PREFIX_.'annonces` ann ON (ann.`id_product` = p.`id_product`)

[kesaven]

Hello

 

I have added the code as you have suggested

below is my function after insertion of your code

 

public static function getProducts($id_lang, $start, $limit, $orderBy, $orderWay, $id_category = false, $only_active = false)
    {
        if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
            die (Tools::displayError());
        if ($orderBy == 'id_product' OR    $orderBy == 'price' OR    $orderBy == 'date_add')
            $orderByPrefix = 'p';
        elseif ($orderBy == 'name')
            $orderByPrefix = 'pl';
        elseif ($orderBy == 'position')
            $orderByPrefix = 'c';

        $rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
        SELECT p.*, pl.* , t.`rate` AS tax_rate, m.`name` AS manufacturer_name, s.`name` AS supplier_name
        FROM `'._DB_PREFIX_.'product` p
        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
            AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
            AND tr.`id_state` = 0)
        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
        LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.
        ($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'
        WHERE pl.`id_lang` = '.(int)($id_lang).
        ($id_category ? ' AND c.`id_category` = '.(int)($id_category) : '').
        ($only_active ? ' AND p.`active` = 1' : '').'
        ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).
        ($limit > 0 ? ' LIMIT '.(int)($start).','.(int)($limit) : '')
        );
        if ($orderBy == 'price')
            Tools::orderbyPrice($rq,$orderWay);
        return ($rq);
    }

 

And this my template:

 

<div class="right_block">
<span class="visiteur_prod" >{l s='Date de parution :'}<span>{$product.debut}</span></span><br />

 

However the field "debut" is stll empty.

Is there a way i can test the query?

I want to get the query that is being run on the database.

 

Thanks

[PhpMadman]

That code does not contain the code from vekia

[vekia]

i analysed your code several times, there is no LEFT JOIN with annonces table

[kesaven]

i analysed your code several times, there is no LEFT JOIN with annonces table

 

Hello

 

I have added the code as you suggested, but the field "debut" is still blank

public static function getProducts($id_lang, $start, $limit, $orderBy, $orderWay, $id_category = false, $only_active = false)

    {

        if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))

            die (Tools::displayError());

        if ($orderBy == 'id_product' OR    $orderBy == 'price' OR    $orderBy == 'date_add')

            $orderByPrefix = 'p';

        elseif ($orderBy == 'name')

            $orderByPrefix = 'pl';

        elseif ($orderBy == 'position')

            $orderByPrefix = 'c';

 

        $rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('

        SELECT ann.*,p.*, pl.* , t.`rate` AS tax_rate, m.`name` AS manufacturer_name, s.`name` AS supplier_name

        FROM `'._DB_PREFIX_.'product` p

LEFT JOIN `'._DB_PREFIX_.'annonces` ann ON (ann.`id_product` = p.`id_product`)

        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)

        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`

            AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'

            AND tr.`id_state` = 0)

        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)

        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)

        LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.

        ($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'

        WHERE pl.`id_lang` = '.(int)($id_lang).

        ($id_category ? ' AND c.`id_category` = '.(int)($id_category) : '').

        ($only_active ? ' AND p.`active` = 1' : '').'

        ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).

        ($limit > 0 ? ' LIMIT '.(int)($start).','.(int)($limit) : '')

        );

        if ($orderBy == 'price')

            Tools::orderbyPrice($rq,$orderWay);

        return ($rq);

    }

 

Is there other data you require in order to help me out?

[kesaven]

Hello

 

I have found a clue to my problem

 

public static function getProducts($id_lang, $start, $limit, $orderBy, $orderWay, $id_category = false, $only_active = false)
    {
        if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
            die (Tools::displayError());
        if ($orderBy == 'id_product' OR    $orderBy == 'price' OR    $orderBy == 'date_add')
            $orderByPrefix = 'p';
        elseif ($orderBy == 'name')
            $orderByPrefix = 'pl';
        elseif ($orderBy == 'position')
            $orderByPrefix = 'c';

        $rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
        SELECT "Test Debut" as debut,p.*, pl.* , t.`rate` AS tax_rate, m.`name` AS manufacturer_name, s.`name` AS supplier_name
        FROM `'._DB_PREFIX_.'product` p
LEFT JOIN `'._DB_PREFIX_.'annonces` ann ON (ann.`id_product` = p.`id_product`)
        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
            AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
            AND tr.`id_state` = 0)
        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
        LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.
        ($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'
        WHERE pl.`id_lang` = '.(int)($id_lang).
        ($id_category ? ' AND c.`id_category` = '.(int)($id_category) : '').
        ($only_active ? ' AND p.`active` = 1' : '').'
        ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).
        ($limit > 0 ? ' LIMIT '.(int)($start).','.(int)($limit) : '')
        );
        if ($orderBy == 'price')
            Tools::orderbyPrice($rq,$orderWay);
        return ($rq);
    }

 

It seems that the $products variable is being filled elsewere

I have modified the class as shown above but no change on the site

 

I also have a overide class for product, i modified this also but nothing

[PhpMadman]

The product-list.tpl is controlled by the CategoryController and the Category class if i remember correctly.

Modify the getProducts in Category class.

[vekia]

Hello

 

I have found a clue to my problem

 

public static function getProducts($id_lang, $start, $limit, $orderBy, $orderWay, $id_category = false, $only_active = false)

    {

        if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))

            die (Tools::displayError());

        if ($orderBy == 'id_product' OR    $orderBy == 'price' OR    $orderBy == 'date_add')

            $orderByPrefix = 'p';

        elseif ($orderBy == 'name')

            $orderByPrefix = 'pl';

        elseif ($orderBy == 'position')

            $orderByPrefix = 'c';

 

        $rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('

        SELECT "Test Debut" as debut,p.*, pl.* , t.`rate` AS tax_rate, m.`name` AS manufacturer_name, s.`name` AS supplier_name

        FROM `'._DB_PREFIX_.'product` p

LEFT JOIN `'._DB_PREFIX_.'annonces` ann ON (ann.`id_product` = p.`id_product`)

        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)

        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`

            AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'

            AND tr.`id_state` = 0)

        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)

        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)

        LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.

        ($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'

        WHERE pl.`id_lang` = '.(int)($id_lang).

        ($id_category ? ' AND c.`id_category` = '.(int)($id_category) : '').

        ($only_active ? ' AND p.`active` = 1' : '').'

        ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).

        ($limit > 0 ? ' LIMIT '.(int)($start).','.(int)($limit) : '')

        );

        if ($orderBy == 'price')

            Tools::orderbyPrice($rq,$orderWay);

        return ($rq);

    }

 

It seems that the $products variable is being filled elsewere

I have modified the class as shown above but no change on the site

 

I also have a overide class for product, i modified this also but nothing

What class you changed?

[kesaven]

Hello

 

I changed both classes

[vekia]

ok, so your table name is "annonces" ? 

[kesaven]

yes my table is named: vs_annonces

[kesaven]

Hello

 

I still have not been able to correct my issue.

Below is my function

 

public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1, $checkAccess = true)
    {
        global $cookie;
        if (!$checkAccess OR !$this->checkAccess($cookie->id_customer))
            return false;

        if ($p < 1) $p = 1;

        if (empty($orderBy))
            $orderBy = 'position';
        else
            /* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
            $orderBy = strtolower($orderBy);

        if (empty($orderWay))
            $orderWay = 'ASC';
        if ($orderBy == 'id_product' OR    $orderBy == 'date_add')
            $orderByPrefix = 'p';
        elseif ($orderBy == 'name')
            $orderByPrefix = 'pl';
        elseif ($orderBy == 'manufacturer')
        {
            $orderByPrefix = 'm';
            $orderBy = 'name';
        }
        elseif ($orderBy == 'position')
            $orderByPrefix = 'cp';

        if ($orderBy == 'price')
            $orderBy = 'orderprice';

        if (!Validate::isBool($active) OR !Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
            die (Tools::displayError());

        $id_supplier = (int)(Tools::getValue('id_supplier'));

        /* Return only the number of products */
        if ($getTotal)
        {
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
            SELECT COUNT(cp.`id_product`) AS total
            FROM `'._DB_PREFIX_.'product` p
            LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
            WHERE cp.`id_category` = '.(int)($this->id).($active ? ' AND p.`active` = 1' : '').'
            '.($id_supplier ? 'AND p.id_supplier = '.(int)($id_supplier) : ''));
            return isset($result) ? $result['total'] : 0;
        }

        $sql = '
        SELECT ann.*,p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`duree_forfait` , pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new,
            (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
        FROM `'._DB_PREFIX_.'category_product` cp
        LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
        LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
        LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
LEFT JOIN `'._DB_PREFIX_.'annonce` ann ON (ann.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
        LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
         AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
          AND tr.`id_state` = 0)
     LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
        LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
        WHERE cp.`id_category` = '.(int)($this->id).($active ? ' AND p.`active` = 1' : '').'
        '.($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');

        if ($random === true)
        {
            $sql .= ' ORDER BY RAND()';
            $sql .= ' LIMIT 0, '.(int)($randomNumberProducts);
        }
        else
        {
            $sql .= ' ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
            LIMIT '.(((int)($p) - 1) * (int)($n)).','.(int)($n);
        }

        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);

        if ($orderBy == 'orderprice')
            Tools::orderbyPrice($result, $orderWay);

        if (!$result)
            return false;

        /* Modify SQL result */
        return Product::getProductsProperties($id_lang, $result);
    }

 

and this is my tpl

{if isset($products)}
<!-- Products list -->
<ul id="product_list" class="clear" >
{foreach from=$products item=product name=products}
<li class="ajax_block_product {if $smarty.foreach.products.first}first_item{elseif $smarty.foreach.products.last}last_item{/if} {if $smarty.foreach.products.index % 2}alternate_item{else}item{/if} clearfix" >
<h2><a href="{$product.link|escape:'htmlall':'UTF-8'}" title="{$product.name|escape:'htmlall':'UTF-8'}">{$product.name|truncate:35:'...'|escape:'htmlall':'UTF-8'}</a></h2>
<div class="left_block">
<a class="product_img_link" title="" href="{$product.link|escape:'htmlall':'UTF-8'}" >
<img src="{$link->getImageLink($product.link_rewrite, $product.id_image, 'home')}" alt="{$product.legend|escape:'htmlall':'UTF-8'}" width="146" height="114" />
</a>
<p class="product_desc">{$product.description_short|truncate:220:'...'|strip_tags:'UTF-8'}</p>
</div>
<div class="right_block">
<span class="visiteur_prod" >{l s='Date de parution :'}<span>{$product.debut}</span></span><br />
<span class="prix_prod" >{l s='Prix de cession :'}
{if isset($product.show_price) && $product.show_price && !isset($restricted_country_mode)}
<span class="price" style="display: inline;" >
{if (double)$product.price >0}
{if !$priceDisplay}
{convertPrice price=$product.price}
{else}
{convertPrice price=$product.price_tax_exc}
{/if}
{else}
Faire offre
{/if}
</span>
{/if}
</span><br />
<a href="{$product.link|escape:'htmlall':'UTF-8'}" class="detail_prod" >{l s='Détail'}</a>
</div>
</li>
{/foreach}
</ul>
    <!-- /Products list -->
{/if}

 

Do i need to add the field $debut in the product class?

[kesaven]

When i enable show errors i get this message:

<span> Notice: Undefined index: debut in /home/vente22site/public_html/tools/smarty/compile/ab4ef3ec958682a6e374dac7c2b5c392f36dd947.file.product-list.tpl.php on line 61 </span>

 

any idea on this?

[PhpMadman]

That means that the $product does not have a value called debut.

 

And also. In your posted code, you do not have any code to get debut.

[kesaven]

Hello

 

I finally found the solution

In fact in the module "BlockLayered" i had to add this:

LEFT JOIN `'._DB_PREFIX_.'annonce` an ON an.`id_product` = pl.`id_product`

and include debut in the select fields.