6 replies to this topic

[rudolfl]

Hi, all
Consider situation -- site offers multiple languages, but product description only exists in "English" for some products. Let's say I am viewing shop, selecting "German". If product description is available in German, it will show. If this field is blank, nothing will be shown.

What I want is to check whether description is available in language selected. If yes, use it, if not -- use one from default language.
I wan to implement this as a SQL query. This way, PHP code will not have to change.

I am struggling to figure out how to do it. Can anyone help?

Thanks,
Rudolf

[DunWeb]

@rudolfl

I don't see how an SQL query would check to see if a description is available in the selected language PHP code edits as well to output the results.



Chris

Edited by DunWeb, 11 July 2012 - 03:25 AM.

[rudolfl]

There are conditional statements in SQL one can use and I got something working, but not fully.
SQL can extract one or another based on condition (I used IF inside WHERE), but then for descriptions that do exist in both languages, I am getting both in the result.

I did not include my query on purpose, as i want to see what other people think first.

Rudolf

[FWR Media]

You really should populate the database correctly, perhaps a script to fill the missing descriptions.

Failing that you can try the following untested code: -

Find product_info.php ( about line 135 ) ..

<?php echo stripslashes($product_info['products_description']); ?>

Replace with ...

<?php
  if ( !tep_not_null( $product_info['products_description'] ) ) {
	// Where 1 is the default language id
	$query = "SELECT products_description FROM `products_description` WHERE language_id = 1 AND products_id = " . (int)$HTTP_GET_VARS['products_id'];
	$result = tep_db_query( $query );
	$row = tep_db_fetch_array( $result );
	$product_info['products_description'] = ( array_key_exists ( 'products_description', $row ) && tep_not_null( $row['products_description'] ) ) ?
	$row['products_description'] : '';
	tep_db_free_result( $result );
  }
  echo stripslashes ( $product_info['products_description'] );
?>

Edited by FWR Media, 11 July 2012 - 08:19 AM.

[bruyndoncx]

my solution to this problem where I have some descriptions in english but not in dutch and since almost everyone understands english, I implemented this

	//CB multi-language
	if ( ($product_info['products_description'] == '') or (empty($product_info['products_description'])) ){
		if ($languages_id == '4') { $other_language = '1'; } else {$other_language = '4'; }
		$other_lang_query = tep_db_query("select pd.products_description from " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = '" . (int)$_GET['products_id'] . "' and pd.language_id = '" . $other_language . "'");
		$other_lang_result = tep_db_fetch_array($other_lang_query);
		if ( ($other_lang_result['products_description'] != '') and (!empty($other_lang_result['products_description']))){
			$product_info['products_description'] = '<br /><hr />'. TEXT_NO_DESCRIPTION_IN_THIS_LANGUAGE . '<hr /><br />' . $other_lang_result['products_description'];
		}
	}

[rudolfl]

Thanks to all.

I have implemented something similar, but was wondering if it is possible to do by using SQL only. I guess, the answer is no. Anyway, now I know. (Oracle actually have functionality to do those sort of things -- friend who is working with Oracle told me so).

Rudolf

[bruyndoncx]

SQL inspiration - in tep_db_query format for php, notice the if construction , the group by products_id to retrieve just 1 line, and the having, though in your case this is optional, this report for me lists missing descriptions and shows any existing description to help translation efforts.
I think you just need the  if, and group by in your product_info page

HTH

[code]
tep_db_query("select p.products_status, p.products_id, p.products_model, pd.products_name,
    if(pd.language_id = 1, pd.products_description, '') as EN,
    if(pd.language_id = 4, pd.products_description, '') as NL, p2c.categories_id
    from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, products_to_categories p2c
    where p2c.products_id = p.products_id and p.products_id = pd.products_id
                                and p.products_status = 1
    group by products_id
having (EN is null or EN = '' or NL is null or NL = '' )  
");