Add A field from to database to product-list

kesaven · October 7, 2013

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 · October 7, 2013

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 · October 7, 2013

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 · October 7, 2013

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

kesaven · October 7, 2013

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 · October 7, 2013

just add additional join:

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

kesaven · October 8, 2013

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 · October 8, 2013

That code does not contain the code from vekia

vekia · October 8, 2013

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

kesaven · October 9, 2013

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 · October 10, 2013

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 · October 10, 2013

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

Modify the getProducts in Category class.

vekia · October 10, 2013

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 · October 10, 2013

Hello

I changed both classes

vekia · October 10, 2013

ok, so your table name is "annonces" ?

kesaven · October 14, 2013

yes my table is named: vs_annonces

kesaven · November 19, 2013

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)}

<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>

{/if}

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

kesaven · November 19, 2013

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 · November 19, 2013

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 · November 20, 2013

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.

Add A field from to database to product-list

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in