OpenCart Community

Posted: **Tue Oct 17, 2023 10:03 pm**

Hi
I need help with my website please, no idea how this has happened it just started by itself...

Example URL:
https://it-me-up.co.uk/en-gb/product/as ... 3x-m-2-rgb

Error Shown:
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'p.`status` = '1' AND p.quantity > 0 ORDER BY sp.`sort_order` ASC' at line 2
Error No: 1064
SELECT * FROM `jmgu_product_subscription` ps LEFT JOIN `jmgu_subscription_plan` sp ON (ps.`subscription_plan_id` = sp.`subscription_plan_id`) LEFT JOIN `jmgu_subscription_plan_description` spd ON (sp.`subscription_plan_id` = spd.`subscription_plan_id`) WHERE ps.`product_id` = '2126' AND ps.`customer_group_id` = '1' AND spd.`language_id` = '1' AND s p.`status` = '1' AND p.quantity > 0 ORDER BY sp.`sort_order` ASC: in /home/u148625395/domains/it-me-up.co.uk/public_html/system/library/db/mysqli.php on line 76

Thoughts:
I think it is to do with the space between the 's' and 'p' in the SQL below:
AND s p.`status` = '1'
However I do not know how this is being generated or called from the mysqli.php file.

Also If I manually run the SQL and remove the space, the next error is:
Unknown column 'p.quantity' in 'where clause'

Any Help on this please?!?!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Latest OpenCart Version (4.0.2.3)
Latest PHP Version (8.2)
Host - Hostinger
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Posted: **Tue Oct 17, 2023 10:08 pm**

The subscription feature is still work in progress, please wait until at least the upcoming 4.0.2.4. In the meantime, try the master branch.

Posted: **Tue Oct 17, 2023 10:12 pm**

The error message you provided points to a syntax error in your SQL query. By examining the provided SQL query, I noticed a space between the alias and the column name:

Code: Select all

s p.status = '1'

You probably intended:

Code: Select all

sp.status = '1'

if you further help you need to share the full query from your model file.

Posted: **Tue Oct 17, 2023 10:13 pm**

JNeuhoff wrote: ↑
Tue Oct 17, 2023 10:08 pm
The subscription feature is still work in progress, please wait until at least the upcoming 4.0.2.4. In the meantime, try the master branch.

Thanks for the quick reply... how do I roll back an update?

I used the Admin dashboard to run the upgrade to 4.0.2.3, the page shows the information below:
Version Information
Current Version: 4.0.2.3
Latest Version: 4.0.2.3
Release Date: 16/09/2023
Change Log: https://github.com/opencart/opencart/re ... ag/4.0.2.3

I thought this would be the master channel?

Posted: **Tue Oct 17, 2023 10:21 pm**

khnaz35 wrote: ↑
Tue Oct 17, 2023 10:12 pm
The error message you provided points to a syntax error in your SQL query. By examining the provided SQL query, I noticed a space between the alias and the column name:
Code: Select all
s p.status = '1'
You probably intended:
Code: Select all
sp.status = '1'
if you further help you need to share the full query from your model file.

Where would this SQL be generated from? Do you know which file? I cannot see in the mysqli.php where this is.

Posted: **Tue Oct 17, 2023 10:35 pm**

IT-Me-Up.co.uk wrote: ↑
Tue Oct 17, 2023 10:21 pm
Where would this SQL be generated from? Do you know which file? I cannot see in the mysqli.php where this is.

Its catalog side controller and model files, i have quickly take a look at it but seems its need deeper debugging.

these are the part of model code which are handling this query

Code: Select all

these are the code
public function getSubscription(int $product_id, int $subscription_plan_id): array {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_subscription` ps LEFT JOIN `" . DB_PREFIX . "subscription_plan` sp ON (ps.`subscription_plan_id` = sp.`subscription_plan_id`) WHERE ps.`product_id` = '" . (int)$product_id . "' AND ps.`subscription_plan_id` = '" . (int)$subscription_plan_id . "' AND ps.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND sp.`status` = '1'");

		return $query->row;
	}

	/**
	 * @param int $product_id
	 *
	 * @return array
	 */
	public function getSubscriptions(int $product_id): array {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_subscription` ps LEFT JOIN `" . DB_PREFIX . "subscription_plan` sp ON (ps.`subscription_plan_id` = sp.`subscription_plan_id`) LEFT JOIN `" . DB_PREFIX . "subscription_plan_description` spd ON (sp.`subscription_plan_id` = spd.`subscription_plan_id`) WHERE ps.`product_id` = '" . (int)$product_id . "' AND ps.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND spd.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND sp.`status` = '1' ORDER BY sp.`sort_order` ASC");

		return $query->rows;
	}

Posted: **Wed Oct 18, 2023 7:13 pm**

JNeuhoff wrote: ↑
Tue Oct 17, 2023 10:08 pm
The subscription feature is still work in progress

Are you sure? So 4.0.2.3 is available for download as a production release but one of its major features is still a work in progress? That doesn't sound right to me. Perhaps the attempted upgrade failed?

OpenCart Community

Error message when clicking on products...

Error message when clicking on products...

Re: Error message when clicking on products...

Re: Error message when clicking on products...

Re: Error message when clicking on products...

Re: Error message when clicking on products...

Re: Error message when clicking on products...

Re: Error message when clicking on products...