New plugin based on Hikashop Google Products Plugin

  • Posts: 14
  • Thank you received: 1
4 years 10 months ago #307047

Hello,

I'm working on new plugin based on Hikashop Google Products Plugin that exports the products from multilingual website with Falang according to the used Content Languages ID.

I am using the following code for the name of the product:

if($plugin->params['fb_lang_id'] == 0){
				$xml .= "\t".'<g:title><![CDATA[ '.$product->product_name.' ]]></g:title>'."\n";
		} else {
			$published = "\n AND published=1";
			$query = "SELECT language_id, reference_id, reference_table, reference_field, value"	
			. "\n FROM #__falang_content"		
			. "\n WHERE language_id=".$plugin->params['fb_lang_id']			
			. $published
			. "\n AND reference_field='product_name'"
			; 			
			$db->setQuery( $query );			
			$products = $db->loadObjectList();
			$ids = array();
			foreach($products as $key => $row) { 
				$ids[] = (int)$row->reference_id;
			} 		
			$xml .= "\t".'<g:title><![CDATA[ '.$row->value.' ]]></g:title>'."\n";		
		}

In result, I always get the translation of the name of 1 product, that repeats for all of them.

Can you assist me with the solution?

Best regards,

Mihail

Last edit: 4 years 10 months ago by nicolas.

Please Log in or Create an account to join the conversation.

  • Posts: 81478
  • Thank you received: 13062
  • MODERATOR
4 years 10 months ago #307050

Hi,

That piece of code displays the product name of the current product being processed.
So first, you should add a condition to that MySQL query to retrieve on the entry matching the id of the current product as you don't need the entries of the other products. Then, you should do a loadObject instead of a loadObjectList since you should always get only one entry. And also, you check if $row->value is empty, because if it is, you still want to use the product_name of the main product object.
Also, your use of $row->value is after/outside the foreach which initialize $row for each loop. So in such case, $row contains the last element of the array.
Finally, ideally, you should move your MySQL query before the foreach on the products to load all the product_name entries in falang_content of all the products and replace product_name with "value" if value is found for the product and thus leave the code

$xml .= "\t".'<g:title><![CDATA[ '.$product->product_name.' ]]></g:title>'."\n";
"as is".
That way, you would reduce tremendously the resources necessary as it would only need one MySQL query instead of 1 per product.
On a XML with thousands of products, that will be critical.

Please Log in or Create an account to join the conversation.

  • Posts: 14
  • Thank you received: 1
4 years 9 months ago #307852

Thank you for your help, Nicolas.

Following your advice I moved MySQL request before foreach of the products and I replaced product_name with value.

Here is the code:

		$query = 'SELECT * FROM '.hikashop_table('product').' WHERE product_access=\'all\' AND product_published=1 AND product_type=\'main\'';
					
		if(!empty($plugin->params['fb-in_stock_only'])){
			$query .= ' AND product_quantity!=0';
		}
		
		$db->setQuery($query);			
		$products = $db->loadObjectList();

		if(empty($products)){
			return true;
		}
		
		//=========================================
		   $queryNametr = "SELECT a.*"
			. "\n FROM #__falang_content a"
			. "\n INNER JOIN amkal_hikashop_product b "
			. "\n ON a.reference_id = b.product_id "
			. "\n WHERE language_id=".$plugin->params['fb_lang_id']	
			. "\n AND published=1"
			. "\n AND reference_field = 'product_name'"						
			//. "\n AND reference_field = 'product_description'"	
			;
			$db->setQuery($queryNametr);	
			$products = $db->loadObjectList();
		//================================================== */
		
		$ids = array();
		foreach($products as $key => $row){
			$ids[] = (int)$row->product_id;					
			
			if($plugin->params['fb_lang_id'] == 0){
			$products[$key]->alias = JFilterOutput::stringURLSafe($row->product_name);
			
			} else {
				$products[$key]->alias = JFilterOutput::stringURLSafe($row->value);
			}
		}

In this case I receive the following SQL syntax error:
1064 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 ') AND a.category_published=1 AND a.category_type='product' AND b.category_id!=2 ' at line 1

If I upload the request before

$query = 'SELECT * FROM '.hikashop_table('product').' WHERE product_access=\'all\' AND product_published=1 AND product_type=\'main\'';

[CDATA[ '.$product->product_name.' ]] - the main language remains.

In order to test MySQL request, I return it back to its previous place and then naturally I receive all the translations names in <item> </item>

Here is the code:
$db->setQuery($queryNametr);			
			$products = $db->loadObjectList(); 			
			$ids = array();
			foreach($products as $key => $row){
			$ids[] = (int)$row->reference_id;			
			$products[$key]->alias = JFilterOutput::stringURLSafe($row->value);
			$xml .= "\t".'<2g:title><![CDATA[ '.$row->value.' ]]></2g:title>'."\n"; 
		    }

Please Log in or Create an account to join the conversation.

  • Posts: 81478
  • Thank you received: 13062
  • MODERATOR
4 years 9 months ago #307854

Hi,

Your MySQL query is not correct. You have only a.* in the select. So only the columns from falang_content are loaded.
But the columns from hikashop_product are not loaded.
You should replace a.* by * in order to load both the data from Falang and the data from HikaShop in $products.
The MySQL error you get is because in some other piece of the code, it does a MySQL query to get the categories and it assume that each entry in $products has a product_id which isn't the case as you don't load the data from hikashop_product.

Please Log in or Create an account to join the conversation.

  • Posts: 14
  • Thank you received: 1
4 years 9 months ago #307899

Thank you, Nicolas.

I corrected the request and the error is gone, but:

[CDATA[ '.$product->product_name.' ]] - is still on the main language.

The translation is in [CDATA[ '.$row->value.' ]] but again with the name of the last product only.

$products[$key]->alias = JFilterOutput::stringURLSafe($row->value); is in foreach of the products.

I changed the code to:
$ids[] = (int)$row->reference_id;

$ids = array();
		foreach($products as $key => $row){

			if($plugin->params['fb_lang_id'] == 0){
				$ids[] = (int)$row->product_id;
			$products[$key]->alias = JFilterOutput::stringURLSafe($row->product_name);
			
			} else {
				$ids[] = (int)$row->reference_id;
				$products[$key]->alias = JFilterOutput::stringURLSafe($row->value);
			}
		}

But the result is still the same.

Best regards,

Please Log in or Create an account to join the conversation.

  • Posts: 81478
  • Thank you received: 13062
  • MODERATOR
4 years 9 months ago #307903

Hi,

The fact that with this MySQL query, you still have the product_name with the main name and not the translated one is normal since there is no mechanism in your code to copy the text from $row->value to $row->product_name when $row->value is not empty.
I think that's what you're missing.
I don't see how $row->value could only have the value of the last product. That's only possible if you display it outside of the foreach, after it when the PHP looped through the array and the variable $row is left with the last element on which it looped.

Please Log in or Create an account to join the conversation.

  • Posts: 14
  • Thank you received: 1
4 years 9 months ago #307925

Thank you, Nicolas.

I did not understand how to copy $row->value to $row->product_name

Can you help me with the code?

if(isset($row->value)) { ...

Best regards.

Please Log in or Create an account to join the conversation.

  • Posts: 81478
  • Thank you received: 13062
  • MODERATOR
4 years 9 months ago #307961

Hi,

You can do like that:

foreach($products as $key => $row){
 if(!empty($row->value))
   $products[$key]->product_name = $row->value;
}

The following user(s) said Thank You: marchela-apostolova

Please Log in or Create an account to join the conversation.

  • Posts: 14
  • Thank you received: 1
4 years 9 months ago #308010

Thank you, Nicolas.

The translations of the names are great with this code.

I tried to translate the same way the description of the products, using new sql request AND reference_field = 'product_description'.

Now it seems that only the description is translated and the name remains on the main language. Here is the code:

		$query = 'SELECT * FROM '.hikashop_table('product').' WHERE product_access=\'all\' AND product_published=1 AND product_type=\'main\'';
					
		if(!empty($plugin->params['fb-in_stock_only'])){
			$query .= ' AND product_quantity!=0';
		}
		
		$db->setQuery($query);			
		$products = $db->loadObjectList();

		if(empty($products)){
			return true;
		}
		
		//name $queryNametr ==========================
		if($plugin->params['fb_lang_id'] > 0){
		   $queryNametr = "SELECT *"
			. "\n FROM #__falang_content a"
			. "\n INNER JOIN #__hikashop_product b "
			. "\n ON a.reference_id = b.product_id "
			. "\n WHERE language_id=".$plugin->params['fb_lang_id']	
			. "\n AND published=1"
			. "\n AND product_published=1"
			. "\n AND reference_field = 'product_name'"						
			;
			if(!empty($plugin->params['fb-in_stock_only'])){
			$queryNametr .= ' AND product_quantity!=0';
		   }
			$db->setQuery($queryNametr);	
			$products = $db->loadObjectList();
		}	
		//========================================== */

		
		$ids = array();
		foreach($products as $key => $row){

			if($plugin->params['fb_lang_id'] == 0){
			$ids[] = (int)$row->product_id;
			$products[$key]->alias = JFilterOutput::stringURLSafe($row->product_name);
			
			} else {

		    $ids[] = (int)$row->reference_id;
			$products[$key]->alias = JFilterOutput::stringURLSafe($row->value);
				if(!empty($row->value))
               $products[$key]->product_name = $row->value;
			}
		}
		
		//description $queryDestr ===================
		if($plugin->params['fb_lang_id'] > 0){
		$queryDestr = "SELECT *"
			. "\n FROM #__falang_content a"
			. "\n INNER JOIN #__hikashop_product b "
			. "\n ON a.reference_id = b.product_id "
			. "\n WHERE language_id=".$plugin->params['fb_lang_id']	
			. "\n AND published=1"
			. "\n AND product_published=1"					
			. "\n AND reference_field = 'product_description'"	
			;

			$db->setQuery($queryDestr);	
			$products = $db->loadObjectList();
			
			foreach($products as $k => $row){
				$products[$k]->product_description = $row->value;
			}
		}	
		//======================================= */

Could you correct me again with the code?

Best regards!

Please Log in or Create an account to join the conversation.

  • Posts: 81478
  • Thank you received: 13062
  • MODERATOR
4 years 9 months ago #308011

Hi,

You should change the variable name $products for the second code to $products2 and then copy the value from $products2 to $products with a double foreach on both arrays.
Something like that:

foreach($products2 as $k2 => $p2) {
foreach($products as $k => $p) {
if($p->product_id==$p2->product_id)
 $products[$k]->product_description = $p2->value;
}
}

The following user(s) said Thank You: marchela-apostolova

Please Log in or Create an account to join the conversation.

  • Posts: 14
  • Thank you received: 1
4 years 9 months ago #308076

Thank you very much for your help, Nicolas!

The translation of the products works great.

Now the users can upload their catalogues in different languages, regarding their wishes.

Do you want me to send you this Plugin?

But I would like to add another function in Hikashop Facebook Products Plugin - most probably changing the currency from ID. This could be useful for online shop with more currencies.

I looked up the tab hikashop_config but I didn't find how main_currency is written and saved, where the option that defines the main_currency is saved?

Please Log in or Create an account to join the conversation.

  • Posts: 81478
  • Thank you received: 13062
  • MODERATOR
4 years 9 months ago #308077

Hi,

No but you can post it here. Someone else might be interested.

Regarding the main currency, it is stored in hikashop_config. The entry with it has the namekey "main_currency". Note however that what is stored is the currency_id of the currency as per the table hikashop_currency. So you would to "convert" the id into the currency_code if you need the currency_code.

Please Log in or Create an account to join the conversation.

  • Posts: 14
  • Thank you received: 1
4 years 9 months ago #308257

Special thanks to Nicolas for the co-operation!

I uploaded Facebook Products Pluging for HikaShop Business.

Its name is Facebook Products Plugin, because it was inspired by the possibility of uploading catalogues in Facebook from multilingual sites in Facebook pages or for dynamic ads.

I think it should work correctly for Google, but I haven't tested it.

It's a modification Google Products Plugin and has two extra options:

1.Possibility of catalogue translation according to the ID of the language (FaLang installed).

2. Possibility of changing the price of the products according to the ID of the currency.

Now you can upload your catalogue in language different from the main language and you can add currency different from the main one.

File Attachment:

File Name: fb_product....2-L.zip
File Size:8 KB

Attachments:
The following user(s) said Thank You: nicolas

Please Log in or Create an account to join the conversation.

Time to create page: 0.083 seconds
Powered by Kunena Forum