Copying Product Image to Thumbnail image in Access when thousands of products

[graphicz]

Sellerdeck uses the thumbnail image instead of the product image in image layouts within the checkout sequence (why???)... Anyway.

For example...

 <div class="image">
<a href="<actinic:variable name="SearchCGIURL" />?PRODREF=<Actinic:Variable Name="ProdRef"/>&amp;NOLOGIN=1<actinic:block if="%3cactinic%3avariable%20name%3d%22HiddenFields%22%20%2f%3e%20%21%3d%20%22%22">&amp;SHOP=<Actinic:Variable Name="HiddenFields"/></actinic:block>">
<Actinic:REMOVE TAG="ImageLine"><img class="img-responsive" src="<actinic:variable name="Thumbnail" />" /></Actinic:REMOVE>
</a>
</div>

So I usually copy the Product Image to Thumbnail image in ActinicCatalog.mdb and this works, going forward the client has to just enter the product image twice, once for the thumbnail.

However, this client has 9275 rows in the product table.

I copied the Product Image column but when I went to paste in the thumbnail columns I got this sequence of events - I clicked 'OK' each time:


Several cups of coffee later I still have the hour glass and the column has not pasted.

1. Is there a better way of doing this?

2. How log should I have to wait per thousand products?

3. Is there a php script or an Access piece of wizardry that will automatically populate the thumnbail image with the product image?

4. Do we think that Sellerdeck would realise how plain awkward this arrangement is and make all representations of the product image the product image and remove the thumbnail variable completely?

Thank you!

[graphicz]

So eventually it did paste most of the image values. It failed to paste 1070 out of 9275 so not bad. The ones it did not paste went into a new table 'Paste Errors' but this column did not match up with the remaining items in the product image column.

Anyway, all the images had pasted into thumbnails down to 8205 and they all seemed to match the respective product image entry, so I copied the rest of the product image column from 8206 down to 9275 and they pasted into the thumbnail column OK - and they all seem to line up!

I suppose the moral of the story is that you can paste about 8000 rows into Access before it starts to complain, unless anyone knows better?

Thanks

[NormanRouxel]

Here's my variable copier program. It's for a set of user Variables with numeric suffixes.

<b>
Automatically copy fields:
This is for multiple variables with a suffix like LightboxImage1 ... LightBoxImage10 being copied to a similar set (ZoomImage1 ... ZoomImage10).
<br>Put in a Fragment Layout, create Fragment using that layout.  Preview that Fragment.
<br>Close and restart SD after running.
<br>Once you know that it's done its job, remove Fragment.
</b>
<actinic:block php="true" >
	// default values to set
	$sourcevarnames = array('LightboxImage', 'LightboxTitle');			// fields to copy (without numeric suffix)
	$destvarnames = array('ZoomImage', 'ZoomAlt');					// destinations (without numeric suffix)
	$sourcesuffix = 1;								// source start suffix
	$maxsourcesuffix = 10;								// maximum number of vars
	$destsuffix = 1;								// destination start suffix
	$includefragments = true;
	$includeduplicates = false;

	$connect = odbc_pconnect("ActinicCatalog<actinic:variable name="ECMajorVersion" selectable="false" />","","", SQL_CUR_USE_ODBC); 

	// get ID's for user Variables
	$varstoprocess = count($sourcevarnames);
	$sourcevarIDs = array();
	$destvarIDs = array();
	for ( $varidx=0; $varidx<$varstoprocess; $varidx++ )
		{
		$sourcevarname = $sourcevarnames[$varidx];
		$destvarname = $destvarnames[$varidx];
		$sourcevarIDs[$varidx] = array();
		$destvarIDs[$varidx] = array();
		$mzpindex = $destsuffix;
		for ($j=$sourcesuffix; $j<=$maxsourcesuffix; $j++)
			{
			echo "<br>Copying $sourcevarname$j to $destvarname$mzpindex";
			$query = "SELECT nID from Variable WHERE sName='$sourcevarname$j'";
			$mpresult = odbc_exec($connect, $query); 
			$sourcevarIDs[$varidx][$j] = odbc_result($mpresult, 1);

			$query = "SELECT nID from Variable WHERE sName='$destvarname$mzpindex'";
			$zmresult = odbc_exec($connect, $query); 
			$destvarIDs[$varidx][$mzpindex] = odbc_result($zmresult, 1);
			$mzpindex++;
			}
		}

	// Product fields we want
	$f = array('Product Reference' => '',
		'Short description' => '');

	$fieldlist = ''; foreach($f as $fieldname => $fvalue) $fieldlist .= '`' . $fieldname . '`,';

	$producttypes = 'nProductType=0';	// Products
	if ($includeduplicates) $producttypes .= ' OR nProductType=1';	// Products 
	if ($includefragments) $producttypes .= ' OR nProductType=2';	// Products 
 
	$query = "SELECT " . rtrim($fieldlist, ',') . "	FROM Product WHERE $producttypes";
	echo "<br><br>Getting Products using: $query<br>";
	$result = odbc_exec($connect, $query);
	$count = 0;
	$updated = 0;
	$altupdated = 0;

	while ( odbc_fetch_row($result) )
		{
		$i = 1;	foreach($f as $fieldname => $fvalue) $f[$fieldname] = addslashes(odbc_result($result, $i++));	
		$pid = $f['Product Reference'];
		$desc = $f['Short description'];
		$count++;
		for ( $varidx=0; $varidx<$varstoprocess; $varidx++)
			{
			$sourcevarname = $sourcevarnames[$varidx];
			$destvarname = $destvarnames[$varidx];
			$mzpindex = $destsuffix; 
			for ($j=$sourcesuffix; $j<=$maxsourcesuffix; $j++)
				{
				// SourceVar<n>
				$eivariableID = $sourcevarIDs[$varidx][$j];
				$query = "SELECT sValue, nID FROM UserDefinedProperties" 
						 . " WHERE sContentID='$pid'" 
						 . " AND nVariableID=$eivariableID";

				$mpresult = odbc_exec($connect, $query); 
				odbc_fetch_row($mpresult);
				$svalue = odbc_result($mpresult, 1);

				// now move to the Destinatione
				if ( $svalue != '' )
					{
					echo "<br>Ref: $pid - $desc $destvarname$j: $svalue";
					$zmvariableID = $destvarIDs[$varidx][$mzpindex];
					$updatequery = "UPDATE UserDefinedProperties"
							. " SET sValue='$svalue', nUseParentSetting=0"
							. " WHERE sContentID='$pid' AND nVariableID=$zmvariableID";
					$updateresult = odbc_exec($connect, $updatequery);
					$matches = odbc_num_rows($updateresult);
					echo (($matches == 0) ? ' Already set.' :  '');
					$updated += $matches;
					if ( $updateresult === FALSE ) echo "<h4 style=\"color:red\">" . preg_replace('/\./', '&#46;', $updatequery) . "</h4>";
					}

				$mzpindex++;
				}
			}
		}
	odbc_close_all();
	echo "<br>Records processed: $count - Updated: $updated";
</actinic:block>

[NormanRouxel]

P.S. I've only used it on groups of User Variables (E.g. LightboxImage1... LightboxImage10). I haven't tried it with single SellerDeck ones. Hang on and I'll test.

[graphicz]

Thank you very much Norman. I will try it in the morning.
Best wishes.

[NormanRouxel]

Here's the code for when you want to copy one SellerDeck Product variable to another.

<b>
Automatically copy one SellerDeck Product field into another.
<br>Put in a Fragment Layout, create Fragment using that layout.  Preview that Fragment.
<br>Close and restart SD after running.
<br>Once you know that it's done its job, remove Fragment.
</b>
<actinic:block php="true" >
	// default values to set
	$sourcevarname = 	'Image FileName';			// field to copy - use names as per Product table within ActinicCatalog.mdb
	$destvarname = 		'sThumbnailImage';			// destination - use names as per Product table within ActinicCatalog.mdb
	$includefragments = 	true;					// whether to include fragments
	$includeduplicates = 	false;					// whether to include duplicates
	$overwrite =		true;					// whether to overwrite destination if not empty
	$copyempty =		true;					// whether to copy empty fields

	$producttypes = '(nProductType=0';	// Products
	if ($includeduplicates) $producttypes .= ' OR nProductType=1';	// Products 
	if ($includefragments) $producttypes .= ' OR nProductType=2';	// Products 
	$producttypes .= ')';

	$copymodes = '';
	if ( $overwrite == false ) $copymodes .= " AND `$destvarname` IS NULL";
	if ( $copyempty == false ) $copymodes .= " AND `$sourcevarname` IS NOT NULL";

	$connect = odbc_pconnect("ActinicCatalog<actinic:variable name="ECMajorVersion" selectable="false" />","","", SQL_CUR_USE_ODBC); 
	$updatequery = "UPDATE Product"
			. " SET `$destvarname`=`$sourcevarname`"
			. " WHERE $producttypes$copymodes";

	echo "<br>$updatequery";

	$updateresult = odbc_exec($connect, $updatequery);
	$matches = odbc_num_rows($updateresult);
	if ( $updateresult === FALSE ) echo "<h4 style=\"color:red\">" . preg_replace('/\./', '&#46;', $updatequery) . "</h4>";

	odbc_close_all();
	echo "<br>Records Updated: $matches";
</actinic:block>

[graphicz]

Thank you Norman

[NormanRouxel]

I've amended both tweaks.

[graphicz]

That is an amazing contribution - thank you Norman - so easy. Please can I repost it with acknowledgement in my blog?

Best wishes

[NormanRouxel]

No problem with you reposting this. I'm off to hospital on Thursday so won't be back for some time.

[Mike Hughes]

I'm off to hospital on Thursday so won't be back for some time.

I hope it's nothing too serious and all goes OK.

[graphicz]

Thank you Norman. All the best this week. Take good care.

[graphicz]

Is there a modification for when one or both is a custom variable and therefore not in the product table or would this be paid work. Happy to if it is. Thank you.