Post by casde-it » Thu Sep 19, 2019 12:42 am

Hello,
--
OC Version: 2.3.0.2
--
I'm trying to make a new table to put in other info for products.

here's the database structure:

Code: Select all

CREATE TABLE `oceo_product_custom` (
  `product_id` int(11) NOT NULL,
  `article_nr` int(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `inner` varchar(255) NOT NULL,
  `master` varchar(255) NOT NULL,
  `location1` varchar(255) NOT NULL,
  `aantal1` varchar(255) NOT NULL,
  `location2` varchar(255) NOT NULL,
  `aantal2` varchar(255) NOT NULL,
  `location3` varchar(255) NOT NULL,
  `aantal3` varchar(255) NOT NULL,
  `location4` varchar(255) NOT NULL,
  `aantal4` varchar(255) NOT NULL,
  `total_quantity` int(255) NOT NULL,
  `price` int(255) NOT NULL,
  `fob_price` int(255) NOT NULL,
  `ean` int(255) NOT NULL,
  `grp_code` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `sub_category` varchar(255) NOT NULL,
  `seasons` varchar(255) NOT NULL,
  `color` varchar(255) NOT NULL,
  `finish` varchar(255) NOT NULL,
  `unit` varchar(255) NOT NULL,
  `net_wt_gram` varchar(255) NOT NULL,
  `buy_price` int(255) NOT NULL,
  `min_qty` int(255) NOT NULL,
  `itm_brdth` int(255) NOT NULL,
  `itm_height` int(255) NOT NULL,
  `cbms` int(255) NOT NULL,
  `smallest_size` int(255) NOT NULL,
  `pict_path` varchar(255) NOT NULL,
  `size_unit` varchar(255) NOT NULL,
  `currency` varchar(255) NOT NULL,
  `comments` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexen voor geëxporteerde tabellen
--

--
-- Indexen voor tabel `oceo_product_custom`
--
ALTER TABLE `oceo_product_custom`
  ADD PRIMARY KEY (`product_id`);
COMMIT;
I will instert everything form an excel sheet a customer gave me.

Now, I want to be able to show every column in the product info field, on the category.tpl, product.tpl and featured products.
I need to know where OC gets the product data, change the query so it adds these values and add them to the language file.
If this is done, I can simply use the following code:

Code: Select all

<?php echo $location1
as example to show this value

Does anyone know how I can achieve this?

If you need any more info, don't hesitate to ask me.

Kind Regards

Newbie

Posts

Joined
Sat Jan 19, 2019 1:11 am

Post by OSWorX » Thu Sep 19, 2019 1:24 am

Basically a great Idea to use seperate a db tablefor additional infos instead expandig the existing!
Second, if you want to have access to these data (fields), you have to adopt also your model, the controller, a new and seperate language file and the templates of the desired output.
Create a new model, call that from the controllers, add the new language vars to that and display all in the template.

That's it basically.

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.


User avatar
Guru Member

Posts

Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria

Post by developer@avi » Thu Sep 19, 2019 1:25 am

Hi you need to edit admin/model/catalog/product.php

getProduct
getProducts
getTotalProducts

Function

For frontend

You need you need to update

catalog/model/calalog/product.php

getProduct
getProducts
getTotalProducts

Newbie

Posts

Joined
Mon Aug 20, 2018 1:05 am

Post by casde-it » Thu Sep 19, 2019 8:30 pm

Thanks for both of your ideas,

I found the fucntions, i added one:

Code: Select all

$data['product_custom'] = $this->getProductCustom($product_id);
where product_custom is my table (oceo_product_custom

This doesn't give any erros when i go to a product in the admin panel.

Now, when i go to around line 675 in the product.php of the admin area i find this code

Code: Select all

	public function getProduct($product_id) {

		$query = $this->db->query("SELECT DISTINCT *, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "') AS keyword FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");



		return $query->row;

	}
I tried editting this to the following:

Code: Select all

	public function getProduct($product_id) {

		$query = $this->db->query("SELECT DISTINCT *, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "') AS keyword FROM " . DB_PREFIX . "product_custom," . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE " . DB_PREFIX . "product.product_id = " . DB_PREFIX . "product_custom.product_id AND  p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");



		return $query->row;

	}
Now, when I try to edit a product i get a HTTP 500 error.

What am I doing wrong here? I also tried replacing

Code: Select all

" . DB_PREFIX . "
with

Code: Select all

oceo_
but this doesn't change anything

I know that there are way more getProduct functions like getProductByDescriptions etc. but I'm trying to make this one work first.

EDIT:
I was looking at the wrong function. Here's the right one (+ my table is in here.)

Code: Select all

	public function getProducts($data = array()) {

		$sql = "SELECT *
		FROM " . DB_PREFIX . "product p
		LEFT JOIN " . DB_PREFIX . "product_custom cp
		ON (p.product_id = cp.product_id)
		LEFT JOIN " . DB_PREFIX . "product_description pd 
		ON (p.product_id = pd.product_id)
		WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

Newbie

Posts

Joined
Sat Jan 19, 2019 1:11 am

Post by OSWorX » Thu Sep 19, 2019 10:18 pm

Code: Select all

getProduct()
and

Code: Select all

getProducts()
are complete different function for different tasks.

With the first, you get the data for one (1) single product (e.g. edit a product).
The second get all products (filter ist $data) for example a list.

And important, admin or catalog (both may have same function names).

But why so complicated and editing existing functions and db-queries?
For example, you will have somewhere in the controller:

Code: Select all

$result = $this->getProduct( $product['product_id'] );
Now simply merge this result with your own data:

Code: Select all

$result = array_merge( $result, $this->getMyProductAdditionalData( $product['product_id'] );
There you will have both and you have a clean structure, because you will have seperate controller(s) and model(s) which can be combined together.
The more, if you ever plan to make an update, you are on the save(r) side.

And you are able to call these additional fields from everywhere - just with a single call.

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.


User avatar
Guru Member

Posts

Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria

Post by OSWorX » Thu Sep 19, 2019 10:20 pm

Forgot: never change DB_PREFIX to something else!

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.


User avatar
Guru Member

Posts

Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria

Post by casde-it » Fri Sep 20, 2019 1:22 am

OSWorX wrote:
Thu Sep 19, 2019 10:20 pm
Forgot: never change DB_PREFIX to something else!
Hey, I meant change the text " DB_PREFIX " to the literal prefix wich is oceo_

But I found it anyway, my problem is solved. Thanks for the advice tho!

Newbie

Posts

Joined
Sat Jan 19, 2019 1:11 am

Post by OSWorX » Fri Sep 20, 2019 1:57 pm

casde-it wrote:
Fri Sep 20, 2019 1:22 am
OSWorX wrote:
Thu Sep 19, 2019 10:20 pm
Forgot: never change DB_PREFIX to something else!
Hey, I meant change the text " DB_PREFIX " to the literal prefix wich is oceo_

But I found it anyway, my problem is solved. Thanks for the advice tho!
DB_PREFIX IS the database table prefix.
No reason to change.

You asked, I answered .. but at the end you can do what you want ..

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.


User avatar
Guru Member

Posts

Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria

Post by casde-it » Sat Sep 21, 2019 4:58 pm

I've got one small problem.

when i change the sql query in catalog/model/catalog/product.php
at public function getProduct($product_id) (lne 13)
and add:

Code: Select all

LEFT JOIN " . DB_PREFIX . "product_custom pc ON (p.model = pc.article_nr)
at the end of the query, next to the other left joins i get the following error

Code: Select all

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes) in /home/notimportant/public_html/webshop/system/library/db/mysqli.php on line 24
This is only for the search page. I can get on a product page when i put in a link manually.

Newbie

Posts

Joined
Sat Jan 19, 2019 1:11 am
Who is online

Users browsing this forum: Amazon [Bot] and 21 guests