Jump to content
Shapeir

PrestaShopDatabaseException

Recommended Posts

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

Share this post


Link to post
Share on other sites

The Tag override you use is bugged, have a look at override/classes/Tag.php at line 247. Feel free to copy it over here if you need specific advice on how to fix it

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

In Stackoverflow someone told me:

 

You have to = in this line WHERE tc.id_group = = 1, remove one.

But i dont know how to to do it.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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;
    }
}

 

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More