Community Forums

Category Listing, Product Page blank in Frontend

General support for technical problems with OpenCart v1.x

Category Listing, Product Page blank in Frontend

Postby aji » Wed Mar 13, 2013 9:35 am

Hello,

I have an exisiting opencart multi store setup, which is working just fine. I am just now setting up a dev site to be able to do some testing. So I installed the opencart on my local server, and have copied over the DB from the live site.

I am able to log into the admin page, perfectly fine, all categories and products are where they need to be.

One the frontend, I can access the home page, and it list all the categories correctly (with the number of products). BUT when I try to access the category listing, or a product page, I get only a blank page.

In the error log I get two messages:

Code: Select all
2013-03-13 14:31:40 - PHP Notice:  Undefined offset: -1 in /root/catalog/controller/common/header.php on line 187
2013-03-13 14:31:48 - PHP Notice:  Error: Column 'product_id' cannot be null<br />Error No: 1048<br />/root/opencart_dev/system/database/mysql.php on line 49


The line in the header it is referring to is:

Code: Select all
$this->data['navdata'] = showCategories($this, $navmenus, $pathparts, $pathparts[count($pathparts)-1], $hide);


Does anybody have an idea what could be going on?
aji
 
Posts: 28
Joined: Wed Mar 13, 2013 9:25 am

Re: Category Listing, Product Page blank in Frontend

Postby aji » Thu Mar 14, 2013 11:55 am

Ok, so the function which causes the error the getProduct($product_id) function in /catalog/model/catalog/product.

The product ID gets passed on correctly, but apparently there is a discrepancy because of the MySQL error.
aji
 
Posts: 28
Joined: Wed Mar 13, 2013 9:25 am

Re: Category Listing, Product Page blank in Frontend

Postby rph » Thu Mar 14, 2013 1:36 pm

That looks like custom code. You'll need to contact the developer for support.
-Ryan
VQMod Manager: FREE extension to manage VQMods in Admin!
Admin Enhancement Suite: Powerful Admin features and enhancements
Dependent Options: Option values displayed based on customer input
Catalog Mode: Disable "Add to Cart" and display your store in view-only
OpenCart Community Edition | Unofficial OpenCart Wiki | Commercial Support and Development
User avatar
rph
 
Posts: 3967
Joined: Thu Jan 07, 2010 4:05 pm
Location: Lincoln, Nebraska

Re: Category Listing, Product Page blank in Frontend

Postby aji » Thu Mar 14, 2013 2:23 pm

I suspect it has something do to with the MySQL and PHP settings between the local dev site and the online live site. When I run the same query on the live site, it runs without any issue.

Because, again, live site works without any issue, it's the dev site that causes the problem.
aji
 
Posts: 28
Joined: Wed Mar 13, 2013 9:25 am

Re: Category Listing, Product Page blank in Frontend

Postby rph » Thu Mar 14, 2013 4:51 pm

No way to tell from that little snippet.
-Ryan
VQMod Manager: FREE extension to manage VQMods in Admin!
Admin Enhancement Suite: Powerful Admin features and enhancements
Dependent Options: Option values displayed based on customer input
Catalog Mode: Disable "Add to Cart" and display your store in view-only
OpenCart Community Edition | Unofficial OpenCart Wiki | Commercial Support and Development
User avatar
rph
 
Posts: 3967
Joined: Thu Jan 07, 2010 4:05 pm
Location: Lincoln, Nebraska

Re: Category Listing, Product Page blank in Frontend

Postby straightlight » Thu Mar 14, 2013 10:32 pm

This error originates from an insert query on a specific contribution which the author has untestedly tried to add a null statement which an auto-incremented field will reject especially starting on mySQL 5.1+ . This methodology from mySQL developers has been blocked ages ago.

Contact the author to address this issue by stating that the auto-incremented field does NOT require to be addressed in the query as it is already creating an incremented value automatically along with the executed query.
Regards,
Straightlight
straightlight
 
Posts: 2242
Joined: Mon Nov 14, 2011 10:38 am
Location: Canada, ON

Re: Category Listing, Product Page blank in Frontend

Postby aji » Fri Mar 15, 2013 8:39 am

straightlight wrote:This error originates from an insert query on a specific contribution which the author has untestedly tried to add a null statement which an auto-incremented field will reject especially starting on mySQL 5.1+ . This methodology from mySQL developers has been blocked ages ago.

Contact the author to address this issue by stating that the auto-incremented field does NOT require to be addressed in the query as it is already creating an incremented value automatically along with the executed query.


Yes, this appears to be the case. I switched the DB over to one installed on the live server, and it is working correctly. Actually the query it is running is not even an insert, it is the getProduct query.

The begin of the full query is here:

Code: Select all
SELECT DISTINCT *, pd.name, pd.creator, pd.keyword, pd.circa, pd.year, pd.century, pd.decade, pd.scenelocation, pd.fm_quicksearch AS fm_quicksearch, p.image, m.name AS manufacturer, (SELECT price FROM product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '8' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '8' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '8') AS reward, (SELECT ss.name FROM stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (select (r.sum + p.rat)/(r.tot + p.tot) from (select COALESCE(j.sum,0) as sum , COALESCE(j.tot,0) as tot from (select SUM(rating) as sum , COUNT(rating) as tot, product_id from rating where product_id = '9206' group by product_id) j right join product on product.product_id = j.product_id where product.product_id = '9206') r, (select product_id, COALESCE(rating,0) as rat, count(rating) as tot from product where product_id = '9206' and rating > 0) p ) AS rating, p.sort_order FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '9206' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '5'


That is when it aborts.

By author - you mean the developer which created the customization to this particular install, or the OC developer?
aji
 
Posts: 28
Joined: Wed Mar 13, 2013 9:25 am

Re: Category Listing, Product Page blank in Frontend

Postby straightlight » Fri Mar 15, 2013 9:30 am

A -1 from a SELECT statement means that the identified row could either not be found or fetched into the object before being returned for a specific reason. Since the query above is the outputted version on the browser / log file, would it be possible to post the method block from the model from where exactly this query originates from ?

As for the author, I meant for the contribution, correct.
Regards,
Straightlight
straightlight
 
Posts: 2242
Joined: Mon Nov 14, 2011 10:38 am
Location: Canada, ON

Re: Category Listing, Product Page blank in Frontend

Postby aji » Fri Mar 15, 2013 11:25 am

The relevant code block is:

Code: Select all
 public function getProduct($product_id) {
        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getCustomerGroupId();
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
        }

        $query = $this->db->query("SELECT DISTINCT *, pd.name, pd.creator, pd.keyword, pd.circa, pd.year, pd.century, pd.decade, pd.scenelocation, pd.fm_quicksearch AS fm_quicksearch, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (select (r.sum + p.rat)/(r.tot + p.tot) from (select COALESCE(j.sum,0) as sum , COALESCE(j.tot,0) as tot from (select SUM(rating) as sum , COUNT(rating) as tot, product_id from  " . DB_PREFIX . "rating where product_id = '" . (int)$product_id . "' group by product_id) j right join  " . DB_PREFIX . "product on product.product_id = j.product_id where product.product_id = '" . (int)$product_id . "') r, (select product_id, COALESCE(rating,0) as rat, count(rating) as tot from  " . DB_PREFIX . "product where product_id = '" . (int)$product_id . "' and rating > 0) p ) AS rating, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id').  "'" .(!$this->customer->isAdmin()? " AND p.status = '1'" : "") . " AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

        if ($query->num_rows) {
            $query->row['price'] = ($query->row['discount'] ? $query->row['discount'] : $query->row['price']);
            $query->row['rating'] = (int)$query->row['rating'];

            return $query->row;
        } else {
            return false;
        }
    }


located in /catalog/model/catalog/product.php

During my earlier testing I did establish that a product_ID gets passed along.

And as I said, it apparently has to do with the MySQL version, since the same install works when connected to the live site, which is on a different server. The MySQL version installed on the dev server is 5.0.92
aji
 
Posts: 28
Joined: Wed Mar 13, 2013 9:25 am


Return to General Support

Who is online

Users browsing this forum: loukiad, philfoot, uksitebuilder and 103 guests

Hosted by Arvixe Web Hosting