Jump to content

Home Page blank and got error message : [PrestaShopDatabaseException]

buchampx
 Share

Recommended Posts

buchampx Newbie

buchampx

Posted
Posted

How Ill fix the following problem I got my home page blank after modify header.tpl file. Than I restore again the header.tpl file still my home page opening blank. so any one help me..?

 

 

I got the following error:

 

[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,4,5,6,7,8,10,11,12,13,14,15,16,20,23,24,25,26,27,30,32,34,35,38,39,41,42,43,44' at line 29
 

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
                    pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
                    pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
                    m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
                    MAX(image_shop.`id_image`) id_image, il.`legend`,
                    ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
                    DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
                    INTERVAL 20 DAY)) > 0 AS new
                FROM `ps_product_sale` ps
                LEFT JOIN `ps_product` p ON ps.`id_product` = p.`id_product`
                 LEFT JOIN ps_product_shop product_shop
        ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
                LEFT JOIN `ps_product_lang` pl
                    ON p.`id_product` = pl.`id_product`
                    AND pl.`id_lang` = 1 AND pl.id_shop = 1
                LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop
        ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
                LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
                LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
                LEFT JOIN `ps_tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`)
                    AND tr.`id_country` = 110
                    AND tr.`id_state` = 0
                LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
                 LEFT
            JOIN ps_stock_available stock
            ON (stock.id_product = p.id_product AND stock.id_shop = 1 )
                WHERE product_shop.`active` = 1
                    AND p.`visibility` != 'none'
                    AND p.`id_product` IN (,1,4,5,6,7,8,10,11,12,13,14,15,16,20,23,24,25,26,27,30,32,34,35,38,39,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,203,204,205,206,207,208,211,212,213,214,215,216,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,302,303,304,305,306,307,308,310,311,312,313,315,316,317,318,319,320,321,323,328,329,330,332,333,334,336,337,338,339,340,341,342,343,344,345,346,347,348,350,351,353,355,356,357,359,360,361,362,363,365,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,444,445,446,447,448,449,450,452,453,454,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,472,473,474,475,478,479,480,481,482,486,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,509,510,511,512)
                GROUP BY product_shop.id_product
                ORDER BY `sales` DESC
                LIMIT 0, 20
at line 613 in file classes/db/Db.php

 

607.             WebserviceRequest::getInstance()->setError(500, '
 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
608.         }
609.         else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
610.         {
611.             if ($sql)
612.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
613.             throw new PrestaShopDatabaseException($this->getMsgError());
614.         }
615.     }
616.
617.     /**

 

Link to comment
Share on other sites
bellini13 Newbie

bellini13

Posted
Posted

There are 2 issues in that statement

(,1,4,5,6,7,8,10,11,12,13,14,15,16,20,23,24,25,26,27,30,32,34,35,38,39,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,

First issue is that the list product ids start with a comma

(,1,4

The second issue is that the list of product ids ends with a comma, instead of closing the parenthesis.

60,61,

It should look like this...

(1,4,5,6,7,8,10,11,12,13,14,15,16,20,23,24,25,26,27,30,32,34,35,38,39,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61)
Link to comment
Share on other sites
buchampx Newbie

buchampx

Posted
  • Author
Posted

Thanks.

i have re installed the module but another problem is there. so what ill do please tell me... follows

 

[PrestaShopDatabaseException]

Column 'price' in order clause is ambiguous
 

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
                    pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
                    pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
                    m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
                    MAX(image_shop.`id_image`) id_image, il.`legend`,
                    t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
                    DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
                    INTERVAL 20 DAY)) > 0 AS new
                FROM `ps_product` p
                 LEFT JOIN ps_product_shop product_shop
        ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
                LEFT JOIN `ps_product_lang` pl
                    ON p.`id_product` = pl.`id_product`
                    AND pl.`id_lang` = 1 AND pl.id_shop = 1
                LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop
        ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
                LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
                LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
                LEFT JOIN `ps_tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`)
                    AND tr.`id_country` = 110
                    AND tr.`id_state` = 0
                LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
                 LEFT
            JOIN ps_stock_available stock
            ON (stock.id_product = p.id_product AND stock.id_shop = 1 )
                WHERE product_shop.`active` = 1
                    AND p.`visibility` != 'none'
                    AND p.`id_product` IN (
                        1,2,3,4,5,6,7,8
                    )
                GROUP BY product_shop.id_product
                ORDER BY `price` asc
                LIMIT 0, 20
at line 613 in file classes/db/Db.php

 

607.             WebserviceRequest::getInstance()->setError(500, '
 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
608.         }
609.         else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
610.         {
611.             if ($sql)
612.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
613.             throw new PrestaShopDatabaseException($this->getMsgError());
614.         }
615.     }
616.
617.     /**
Link to comment
Share on other sites
bellini13 Newbie

bellini13

Posted
Posted

find a developer that knows how to properly write SQL statements.  This is the last one I will assist you on since you are using a custom module here

 

You need to define what table the column `price` comes from.  You are joining multiple tables, and more than 1 table has a column named `price`.  So figure out which table you want to use for sorting, and then append that table name

ORDER BY `price` asc

For example, if you want to use price from the product table, then it should say

ORDER BY p.`price` asc
Link to comment
Share on other sites
  • 2 years later...
omagallanes Newbie

omagallanes

Posted
Posted

Hola amigos buenas noches, yo cuento con un problema similar desearía me puedan ayudar.

 

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MYSQL version for the right syntax to use near ') AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("' at line 29
 

		SELECT			p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`,			IFNULL(product_attribute_shop.id_product_attribute, 0) id_product_attribute,			pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,			pl.`name`, image_shop.`id_image` id_image, il.`legend`, m.`name` AS manufacturer_name,			DATEDIFF(				p.`date_add`,				DATE_SUB(					"2017-08-13 00:00:00",					INTERVAL 20 DAY				)			) > 0 AS new, product_shop.price AS orderprice		FROM `cs_product` p		 INNER JOIN cs_product_shop product_shop		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)		LEFT JOIN `cs_product_attribute_shop` product_attribute_shop			ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)		 LEFT JOIN cs_stock_available stock			ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1  AND stock.id_shop_group = 0  )		LEFT JOIN `cs_product_lang` pl ON (			p.`id_product` = pl.`id_product`			AND pl.`id_lang` = 1 AND pl.id_shop = 1 		)		LEFT JOIN `cs_image_shop` image_shop			ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)		LEFT JOIN `cs_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)		LEFT JOIN `cs_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)		WHERE product_shop.`id_shop` = 1			AND p.`id_product` IN ()             AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY pl.`name` DESC			LIMIT 0,20

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

 

786.         if ($webservice_call && $errno) {787.             $dbg = debug_backtrace();788.             WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);789.         } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) {790.             if ($sql) {791.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');792.             }793. 794.             throw new PrestaShopDatabaseException($this->getMsgError());795.         }796.     }
Link to comment
Share on other sites
DataKick Explorer

DataKick

Posted
Posted

 

Hola amigos buenas noches, yo cuento con un problema similar desearía me puedan ayudar.

 

[PrestaShopDatabaseException]

You have an error in your SQL syntax; check the manual that corresponds to your MYSQL version for the right syntax to use near ') AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("' at line 29

 

 

 

The problem is this line:

 AND p.`id_product` IN ()

Obviously, product id was not passed correctly to the sql. Contact developer of poslistcateproduct module and ask them to fix the problem.

Link to comment
Share on other sites
 Share
Go to topic listing
×
×
  • Create New...