sql question

[crompton]

Hi,

I added a second language to an already existing shop with hundreds of products.

In the database table 'products_description' I want to copy the data of 'products_name'(language_id=1) to 'products_name'(language_id=2)

May someone help me with the sql statement?

 

Thanks!

[GuZzie]

Should be something like this

 

INSERT INTO products_description (products_name, language_id)

SELECT products_name, 2

FROM products_description

WHERE language_id = 1

 

---------------------------------------------------------------

 

Please note: backup your table first before executing this statement, else your whole products_description table will be lost including all your products!

backup , backup , backup :)

 

Good luck!

[crompton]

Thanks for your answer.

The table row for language_id=2 already exists but the fields are empty. So I suppose it has to be an UPDATE statement instead of INSERT INTO.

Any idea?

[GuZzie]

I do not fully understand what you are trying to accomplish.

 

Do you want to change the language_id to 2 where now it's 1?

Or do you have duplicates in your table with a language_id set to null?

 

Can you rephrase the question with some more information? thanks

[♥FWR Media]

Hi,

I added a second language to an already existing shop with hundreds of products.

In the database table 'products_description' I want to copy the data of 'products_name'(language_id=1) to 'products_name'(language_id=2)

May someone help me with the sql statement?

 

Thanks!

 

I wrote this for you but be sure to backup the products_description table before trying this in case you make a mistake.

 

Add a file called pd.php to your shop root containing ..

 

<?php

 if ( !array_key_exists( 'add_pd_language', $_GET ) ) {
return false;
 }
 if ( !is_numeric( $_GET['add_pd_language'] ) ) {
return false;
 }

 include_once 'includes/application_top.php';

 $all_query = "SELECT * FROM `products_description` WHERE language_id = 1";
 $insert_query = "INSERT INTO `products_description` ( :KEYS ) VALUES ( :VALUES )"; 

 $result = tep_db_query( $all_query );

 while ( $row = tep_db_fetch_array( $result ) ) {
$insert_query_populated = str_replace( ':KEYS', implode( ', ', array_keys( $row ) ), $insert_query );
$value_string = '';
foreach( $row as $key => $value ) {
 	if ( $key == 'language_id' ) $value = (int)$_GET['add_pd_language'];
 	$value_string .= "'" . tep_db_input( $value ) . "', ";
}
$value_string = rtrim( $value_string, ', ' );
$insert_query_populated = str_replace( ':VALUES', $value_string, $insert_query_populated );
tep_db_query( $insert_query_populated );
 }
 tep_db_free_result( $result ); 	

 include_once 'includes/application_bottom.php';

 

Access the file using the new language you want to add as a querystring like ..

 

www.mysite.com/pd.php?add_pd_language=2

 

You MUST delete pd.php after use DO NOT leave it on the server

[crompton]

Thanks, but that does not work, I get an error: Duplicate entry for key 'PRIMARY'

 

And, I only want to update the products_name of the new language, not the other data like products_description ......

 

 

my products_description table looks like this:

 

products_id   |   language_id   |   products_name   |   products_d...
---------------------------------------------------------------------
31    	|   	1     	|	apple      	|
---------------------------------------------------------------------
31    	|   	2     	|               	|
---------------------------------------------------------------------
32    	|   	1     	|	banana     	|
---------------------------------------------------------------------
32    	|   	2     	|               	|
---------------------------------------------------------------------
33    	|   	1     	|	lemon      	|
---------------------------------------------------------------------
33    	|   	2     	|               	|
---------------------------------------------------------------------

 

and I want it to look like that:

 

products_id   |   language_id   |   products_name   |   products_d...
---------------------------------------------------------------------
31    	|   	1     	|	apple      	|
---------------------------------------------------------------------
31    	|   	2     	|	apple      	|
---------------------------------------------------------------------
32    	|   	1     	|	banana     	|
---------------------------------------------------------------------
32    	|   	2     	|	banana     	|
---------------------------------------------------------------------
33    	|   	1     	|	lemon      	|
---------------------------------------------------------------------
33    	|   	2     	|	lemon      	|
---------------------------------------------------------------------

 

 

I hope I could clarify my needs ;)

 

 

[crompton]

Problem solved by exporting the first three columns of the table, editing the file and importing it again into the database.