Jump to content

PrestaShopDatabaseException

Shapeir

By Shapeir
in Configuring and using PrestaShop

 Share

Recommended Posts

Shapeir Newbie

Shapeir

Posted
Posted

Hello,
I am nearing the completion of my webshop and unfortunately something is not working properly anymore.
I'm just in debug mode in the front office. When I get a product I get the following message:

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 1 AND tc.`id_lang` = 1 AND tc.`id_shop` = 1 A' at line 5
  

                SELECT t.`id_lang`, t.`name`, tc.`counter` AS times
                FROM ps_tag_count tc
                LEFT JOIN `ps_tag` t ON (t.id_tag = tc.id_tag)
                LEFT JOIN ps_product_tag pt ON (pt.id_tag = t.id_tag)
                WHERE tc.id_group = = 1
                 AND tc.`id_lang` = 1 AND tc.`id_shop` = 1
                AND pt.`id_product`=117


at line 746 in file classes/db/Db.php

  

741.         if ($webservice_call && $errno) {
742.             $dbg = debug_backtrace();
743.             WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
744.         } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) {
745.             if ($sql) {
746.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
747.             }
748. 
749.             throw new PrestaShopDatabaseException($this->getMsgError());
750.         }
751.     }

 

I hope someone can help me to fix the problem :(.

 

 

Thank you for any help.
David

Link to comment
Share on other sites
Shapeir Newbie

Shapeir

Posted
  • Author
Posted

HI. Thanks for answering.

  

 /**
     * Get Product Tags
     *
     * @param int $idProduct Product ID
     *
     * @return array|bool
     */
    public static function getProductTags($idProduct)
    {
        $context = \Context::getContext();
        $idLang = $context->language->id;
        $whereTags = "";
        $whereTags .= ' AND tc.`id_lang` = '.(int) $idLang;
        $whereTags .= ' AND tc.`id_shop` = '.(int) $context->shop->id;
        if (Group::isFeatureActive()) {
            $groups = FrontController::getCurrentCustomerGroups();
            $sqlTagProduct = '
                SELECT t.`id_lang`, t.`name`, tc.`counter` AS times
                FROM '._DB_PREFIX_.'tag_count tc
                LEFT JOIN `'._DB_PREFIX_.'tag` t ON (t.id_tag = tc.id_tag)
                LEFT JOIN '._DB_PREFIX_.'product_tag pt ON (pt.id_tag = t.id_tag)
                WHERE tc.id_group = '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').'
                '.$whereTags.'
                AND pt.`id_product`='.(int) $idProduct;
                if (!$tmp = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sqlTagProduct)) {
                    return false;
                }
        }else{
            $sqlTagProduct = '
                SELECT t.`id_lang`, t.`name`, tc.`counter` AS times
                FROM '._DB_PREFIX_.'tag_count tc
                LEFT JOIN `'._DB_PREFIX_.'tag` t ON (t.id_tag = tc.id_tag)
                LEFT JOIN '._DB_PREFIX_.'product_tag pt ON (pt.id_tag = t.id_tag)
                WHERE tc.id_group = 0
                '.$whereTags.'
                AND pt.`id_product`='.(int) $idProduct;
                if (!$tmp = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sqlTagProduct)) {
                    return false;
                }
        }

        return $tmp;
    }

Thank David

Link to comment
Share on other sites
  • 3 months later...
  • 1 month later...
mohamed3w Newbie

mohamed3w

Posted
Posted (edited)

Yes Solved.

The solution is what NemoPS says, i edited the file /override/classes/Tag.php

Line 244 was :  

WHERE tc.id_group = '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').'

I replace it with : 

WHERE tc.id_group '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').'

 

Edited by mohamed3w (see edit history)
  • Like 1
  • Thanks 1
Link to comment
Share on other sites
  • 5 months later...
Gokz Newbie

Gokz

Posted
Posted 
class TagCore extends ObjectModel
{
    /** @var int Language id */
    public $id_lang;

    /** @var string Name */
    public $name;

    /**
     * @see ObjectModel::$definition
     */
    public static $definition = array(
        'table' => 'tag',
        'primary' => 'id_tag',
        'fields' => array(
            'id_lang' => array('type' => self::TYPE_INT, 'validate' => 'isUnsignedId', 'required' => true),
            'name' => array('type' => self::TYPE_STRING, 'validate' => 'isGenericName', 'required' => true, 'size' => 32),
        ),
    );

    protected $webserviceParameters = array(
        'fields' => array(
            'id_lang' => array('xlink_resource' => 'languages'),
        ),
    );

    public function __construct($id = null, $name = null, $idLang = null)
    {
        $this->def = Tag::getDefinition($this);
        $this->setDefinitionRetrocompatibility();

        if ($id) {
            parent::__construct($id);
        } elseif ($name && Validate::isGenericName($name) && $idLang && Validate::isUnsignedId($idLang)) {
            $row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
            SELECT *
            FROM `' . _DB_PREFIX_ . 'tag` t
            WHERE `name` = \'' . pSQL($name) . '\' AND `id_lang` = ' . (int) $idLang);

            if ($row) {
                $this->id = (int) $row['id_tag'];
                $this->id_lang = (int) $row['id_lang'];
                $this->name = $row['name'];
            }
        }
    }

    public function add($autoDate = true, $nullValues = false)
    {
        if (!parent::add($autoDate, $nullValues)) {
            return false;
        } elseif (isset($_POST['products'])) {
            return $this->setProducts(Tools::getValue('products'));
        }

        return true;
    }

    /**
     * Add several tags in database and link it to a product.
     *
     * @param int $idLang Language id
     * @param int $idProduct Product id to link tags with
     * @param string|array $tagList List of tags, as array or as a string with comas
     *
     * @return bool Operation success
     */
    public static function addTags($idLang, $idProduct, $tagList, $separator = ',')
    {
        if (!Validate::isUnsignedId($idLang)) {
            return false;
        }

        if (!is_array($tagList)) {
            $tagList = array_filter(array_unique(array_map('trim', preg_split('#\\' . $separator . '#', $tagList, null, PREG_SPLIT_NO_EMPTY))));
        }

        $list = array();
        if (is_array($tagList)) {
            foreach ($tagList as $tag) {
                if (!Validate::isGenericName($tag)) {
                    return false;
                }
                $tag = trim(Tools::substr($tag, 0, self::$definition['fields']['name']['size']));
                $tagObj = new Tag(null, $tag, (int) $idLang);

                /* Tag does not exist in database */
                if (!Validate::isLoadedObject($tagObj)) {
                    $tagObj->name = $tag;
                    $tagObj->id_lang = (int) $idLang;
                    $tagObj->add();
                }
                if (!in_array($tagObj->id, $list)) {
                    $list[] = $tagObj->id;
                }
            }
        }

        $data = array();
        foreach ($list as $tag) {
            $data[] = array(
                'id_tag' => (int) $tag,
                'id_product' => (int) $idProduct,
                'id_lang' => (int) $idLang,
            );
        }
        $result = Db::getInstance()->insert('product_tag', $data);

        if ($list != array()) {
            self::updateTagCount($list);
        }

        return $result;
    }

    /**
     * Update tag count.
     *
     * @param array|null $tagList
     */
    public static function updateTagCount($tagList = null)
    {
        if (!Module::getBatchMode()) {
            if ($tagList != null) {
                $tagListQuery = ' AND pt.id_tag IN (' . implode(',', array_map('intval', $tagList)) . ')';
                Db::getInstance()->execute('DELETE pt FROM `' . _DB_PREFIX_ . 'tag_count` pt WHERE 1=1 ' . $tagListQuery);
            } else {
                $tagListQuery = '';
            }

            Db::getInstance()->execute('REPLACE INTO `' . _DB_PREFIX_ . 'tag_count` (id_group, id_tag, id_lang, id_shop, counter)
            SELECT cg.id_group, pt.id_tag, pt.id_lang, id_shop, COUNT(pt.id_tag) AS times
                FROM `' . _DB_PREFIX_ . 'product_tag` pt
                INNER JOIN `' . _DB_PREFIX_ . 'product_shop` product_shop
                    USING (id_product)
                JOIN (SELECT DISTINCT id_group FROM `' . _DB_PREFIX_ . 'category_group`) cg
                WHERE product_shop.`active` = 1
                AND EXISTS(SELECT 1 FROM `' . _DB_PREFIX_ . 'category_product` cp
                                LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cgo ON (cp.`id_category` = cgo.`id_category`)
                                WHERE cgo.`id_group` = cg.id_group AND product_shop.`id_product` = cp.`id_product`)
                ' . $tagListQuery . '
                GROUP BY pt.id_tag, pt.id_lang, cg.id_group, id_shop ORDER BY NULL');
            Db::getInstance()->execute('REPLACE INTO `' . _DB_PREFIX_ . 'tag_count` (id_group, id_tag, id_lang, id_shop, counter)
            SELECT 0, pt.id_tag, pt.id_lang, id_shop, COUNT(pt.id_tag) AS times
                FROM `' . _DB_PREFIX_ . 'product_tag` pt
                INNER JOIN `' . _DB_PREFIX_ . 'product_shop` product_shop
                    USING (id_product)
                WHERE product_shop.`active` = 1
                ' . $tagListQuery . '
                GROUP BY pt.id_tag, pt.id_lang, id_shop ORDER BY NULL');
        }
    }

    /**
     * Get main tags.
     *
     * @param int $idLang Language ID
     * @param int $nb number
     *
     * @return array|false|mysqli_result|null|PDOStatement|resource
     */
    public static function getMainTags($idLang, $nb = 10)
    {
        $context = Context::getContext();
        if (Group::isFeatureActive()) {
            $groups = FrontController::getCurrentCustomerGroups();

            return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
            SELECT t.name, counter AS times
            FROM `' . _DB_PREFIX_ . 'tag_count` pt
            LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (t.id_tag = pt.id_tag)
            WHERE pt.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . '
            AND pt.`id_lang` = ' . (int) $idLang . ' AND pt.`id_shop` = ' . (int) $context->shop->id . '
            ORDER BY times DESC
            LIMIT ' . (int) $nb);
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
            SELECT t.name, counter AS times
            FROM `' . _DB_PREFIX_ . 'tag_count` pt
            LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (t.id_tag = pt.id_tag)
            WHERE pt.id_group = 0 AND pt.`id_lang` = ' . (int) $idLang . ' AND pt.`id_shop` = ' . (int) $context->shop->id . '
            ORDER BY times DESC
            LIMIT ' . (int) $nb);
        }
    }

    /**
     * Get Product Tags.
     *
     * @param int $idProduct Product ID
     *
     * @return array|bool
     */
    public static function getProductTags($idProduct)
    {
        if (!$tmp = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
        SELECT t.`id_lang`, t.`name`
        FROM ' . _DB_PREFIX_ . 'tag t
        LEFT JOIN ' . _DB_PREFIX_ . 'product_tag pt ON (pt.id_tag = t.id_tag)
        WHERE pt.`id_product`=' . (int) $idProduct)) {
            return false;
        }
        $result = array();
        foreach ($tmp as $tag) {
            $result[$tag['id_lang']][] = $tag['name'];
        }

        return $result;
    }

    /**
     * Get Products.
     *
     * @param bool $associated
     * @param Context|null $context
     *
     * @return array|false|mysqli_result|null|PDOStatement|resource
     */
    public function getProducts($associated = true, \Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }
        $idLang = $this->id_lang ? $this->id_lang : $context->language->id;

        if (!$this->id && $associated) {
            return array();
        }

        $in = $associated ? 'IN' : 'NOT IN';

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
        SELECT pl.name, pl.id_product
        FROM `' . _DB_PREFIX_ . 'product` p
        LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.id_product = pl.id_product' . Shop::addSqlRestrictionOnLang('pl') . '
        ' . Shop::addSqlAssociation('product', 'p') . '
        WHERE pl.id_lang = ' . (int) $idLang . '
        AND product_shop.active = 1
        ' . ($this->id ? ('AND p.id_product ' . $in . ' (SELECT pt.id_product FROM `' . _DB_PREFIX_ . 'product_tag` pt WHERE pt.id_tag = ' . (int) $this->id . ')') : '') . '
        ORDER BY pl.name');
    }

    /**
     * Set products.
     *
     * @param array $array
     *
     * @return bool
     */
    public function setProducts($array)
    {
        $result = Db::getInstance()->delete('product_tag', 'id_tag = ' . (int) $this->id);
        if (is_array($array)) {
            $array = array_map('intval', $array);
            $result &= ObjectModel::updateMultishopTable('Product', array('indexed' => 0), 'a.id_product IN (' . implode(',', $array) . ')');
            $ids = array();
            foreach ($array as $idProduct) {
                $ids[] = '(' . (int) $idProduct . ',' . (int) $this->id . ',' . (int) $this->id_lang . ')';
            }

            if ($result) {
                $result &= Db::getInstance()->execute('INSERT INTO ' . _DB_PREFIX_ . 'product_tag (id_product, id_tag, id_lang) VALUES ' . implode(',', $ids));
                if (Configuration::get('PS_SEARCH_INDEXATION')) {
                    $result &= Search::indexation(false);
                }
            }
        }
        self::updateTagCount(array((int) $this->id));

        return $result;
    }

    /**
     * Delete tags for product.
     *
     * @param int $idProduct Product ID
     *
     * @return bool
     */
    public static function deleteTagsForProduct($idProduct)
    {
        $tagsRemoved = Db::getInstance()->executeS('SELECT id_tag FROM ' . _DB_PREFIX_ . 'product_tag WHERE id_product=' . (int) $idProduct);
        $result = Db::getInstance()->delete('product_tag', 'id_product = ' . (int) $idProduct);
        Db::getInstance()->delete('tag', 'NOT EXISTS (SELECT 1 FROM ' . _DB_PREFIX_ . 'product_tag
        												WHERE ' . _DB_PREFIX_ . 'product_tag.id_tag = ' . _DB_PREFIX_ . 'tag.id_tag)');
        $tagList = array();
        foreach ($tagsRemoved as $tagRemoved) {
            $tagList[] = $tagRemoved['id_tag'];
        }
        if ($tagList != array()) {
            self::updateTagCount($tagList);
        }

        return $result;
    }
}

 

Link to comment
Share on other sites
  • 5 weeks later...
ChiaraQ Newbie

ChiaraQ

Posted
Posted (edited)

Hello, I have a similar problem with PrestaShopDatabaseException, this is the result of my debug:

[PrestaShopDatabaseException]

Unknown column 'sdi' in 'field list'

  

INSERT INTO `psgf_address` (`id_customer`, `id_manufacturer`, `id_supplier`, `id_warehouse`, `id_country`, `id_state`, `alias`, `company`, `lastname`, `firstname`, `vat_number`, `sdi`, `pec`, `address1`, `address2`, `postcode`, `city`, `other`, `phone`, `phone_mobile`, `dni`, `deleted`, `date_add`, `date_upd`) VALUES ('7', '0', '0', '0', '10', '210', 'Il mio indirizzo', 'Azienda', 'Cognome', 'Nome', '', '', '', 'Indirizzo', '', 'CAP', 'Citta', '', '', '', '', '0', '', '')

at line 769 in file classes/db/Db.php 

764.         if ($webservice_call && $errno) {
765.             $dbg = debug_backtrace();
766.             WebserviceRequest::getInstance()->setError(500, '[SQL Error] ' . $this->getMsgError() . '. From ' . (isset($dbg[3]['class']) ? $dbg[3]['class'] : '') . '->' . $dbg[3]['function'] . '() Query was : ' . $sql, 97);
767.         } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) {
768.             if ($sql) {
769.                 throw new PrestaShopDatabaseException($this->getMsgError() . '<br /><br /><pre>' . $sql . '</pre>');
770.             }
771. 
772.             throw new PrestaShopDatabaseException($this->getMsgError());
773.         }
774.     }

DbCore->displayError - [line 385 - classes/db/Db.php] - [1 Arguments]
DbCore->query - [line 740 - classes/db/Db.php] - [1 Arguments]
DbCore->q - [line 476 - classes/db/Db.php] - [2 Arguments]
DbCore->insert - [line 551 - classes/ObjectModel.php] - [3 Arguments]
ObjectModelCore->add - [line 178 - classes/Address.php] - [2 Arguments]
AddressCore->add - [line 508 - classes/ObjectModel.php] - [2 Arguments]
ObjectModelCore->save - [line 74 - classes/form/CustomerAddressPersister.php]
CustomerAddressPersisterCore->save - [line 157 - classes/form/CustomerAddressForm.php] - [2 Arguments]
CustomerAddressFormCore->submit - [line 109 - classes/checkout/CheckoutAddressesStep.php]
CheckoutAddressesStepCore->handleRequest - [line 59 - classes/checkout/CheckoutProcess.php] - [1 Arguments]
CheckoutProcessCore->handleRequest - [line 265 - controllers/front/OrderController.php] - [1 Arguments]
OrderControllerCore->initContent - [line 281 - classes/controller/Controller.php]
ControllerCore->run - [line 511 - classes/Dispatcher.php]
DispatcherCore->dispatch - [line 28 - index.php]

I also installed a module that can add some fields in the registration form, but only in the first block (personal information), not in the addresses section, I don't think this module can cause problems.
Can someone please help me?

Thank you for any help,
Chiara

Edited by ChiaraQ (see edit history)
Link to comment
Share on other sites
ChiaraQ Newbie

ChiaraQ

Posted
Posted
33 minutes ago, NemoPS said:

A quick solution would be to just add that column in the address table. It does look like a custom field

Hi NemoPS, 

thanks for the answer, I found the solution just adding a column in database table psgf_address.

Now it works correctly, it saves all my data and the costumer can go to the following steps.

Thank you for your reply.

Link to comment
Share on other sites
mir-aus Newbie

mir-aus

Posted
Posted

Dear All,

I have the same problem:

at line 769 in file classes/db/Db.php
764. if ($webservice_call && $errno) { 765. $dbg = debug_backtrace(); 766. WebserviceRequest::getInstance()->setError(500, '

 ' . $this->getMsgError() . '. From ' . (isset($dbg[3]['class']) ? $dbg[3]['class'] : '') . '->' . $dbg[3]['function'] . '() Query was : ' . $sql, 97); 767. } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) { 768. if ($sql) { 769. throw new PrestaShopDatabaseException($this->getMsgError() . '<br /><br /><pre>' . $sql . '</pre>'); 770. } 771. 772. throw new PrestaShopDatabaseException($this->getMsgError()); 773. } 774. }

 

how can I fix it?

Link to comment
Share on other sites
  • 9 months later...
kabalan elsitt Newbie

kabalan elsitt

Posted
Posted
On 2/13/2019 at 1:11 AM, mohamed3w said:

Yes Solved.

The solution is what NemoPS says, i edited the file /override/classes/Tag.php

Line 244 was :  


WHERE tc.id_group = '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').'

I replace it with : 


WHERE tc.id_group '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1').'

 

hello, please i cannot find the file/override/classes/tag.php

Link to comment
Share on other sites
  • 9 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...