You are here:  » Affiliate Window - Image Length Increase to 4096


Affiliate Window - Image Length Increase to 4096

Submitted by ChrisNBC on Fri, 2017-08-25 12:06 in

Hi David,

Hope all is going well. I wonder if I could just check something with you....

I received an email this morning from Affiliate Window advising that they are changing the length of the 'aw_image_url' and 'aw_thumb_url' feed fields to 'at least 4096 characters'. I know the current length of these fields for PT is 255. Obviously, I can easily increase the length but wondered if you could foresee any issues with doing this?

Thanks in advance.

Best regards
Chris

Submitted by support on Fri, 2017-08-25 12:38

Hi Chris,

MySQL's VARCHAR type is actually fluid - it's length is only a maximum length - if defined as, say VARHCAR(4096) but a stored value is only 512 bytes in length, that is all (plus a constant overhead) that is taken up. The following dbmod.php script will convert `image_url` field on `pt_products` table to VARCHAR(4096)...

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
           CHANGE `image_url` `image_url` VARCHAR(4096) NOT NULL default ''"
;
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

Cheers,
David.
--
PriceTapestry.com

Submitted by ChrisNBC on Wed, 2017-08-30 10:07

Hi David,

Hope you had a good weekend. Thanks for your quick response.

I've updated the field using phpMyAdmin and all works fine.

Best regards
Chris

Submitted by Keeop on Wed, 2017-08-30 12:05

Hi David,

Have you got a script that would loop through all the databases on a host and update all instances of any fields named 'image_url' (and 'field_mage_url' for the feeds table)?? I think I have close to 100 databases and all manner of tables that store this sort of information! Would be really helpful and save a lot of manual shenanigans. Anyone know if the 'thumb_url' length needs changing as well?

Cheers.
Keeop

Submitted by support on Wed, 2017-08-30 14:11

Hello Keeop,

The field_{field_name} columns on the feeds tables aren't affected as they hold the field name mapping rather than an actual image URL. The email indicates that both the image and thumbnail URL values may be up to 4096 characters so if you have added a custom field to contain the thumbnail this would need to be increased in size also.

Make sure you have full backups in place of course before applying a significant change like this but to that end, rather than provide a script to execute the actual queries, the following example will scan all tables in all databases on the server (that $config_databaseUsername has access to) and dump the SQL queries with semi-colon termination which you than execute from the command line using the mysql utility. Save as something appropriate e.g. imgmod.php and run from the top level folder of a Price Tapestry installation:

<?php
  header
("Content-Type: text/plain");
  require(
"includes/common.php");
  
$fields  = array("image_url","thumb_url");
  
$sql "SHOW DATABASES";
  
database_querySelect($sql,$dbs);
  foreach(
$dbs as $db)
  {
    
$database $db["Database"];
    
$sql "SHOW TABLES FROM ".$database;
    
database_querySelect($sql,$tbs);
    foreach(
$tbs as $tb)
    {
      
$table $tb["Tables_in_".$database];
      foreach(
$fields as $field)
      {
        
$sql "SHOW COLUMNS FROM ".$database.".".$table." LIKE '".$field."'";
        if (
database_querySelect($sql,$cls))
        {
          
$sql "ALTER TABLE `".$database."`.`".$table."` CHANGE `".$field."` `".$field."` VARCHAR(4096) NOT NULL default '';";
          print 
$sql."\n";
        }
      }
    }
  }
?>

Before uploading, edit the following code at line 4 to contain the list of fields that you want to apply the change to:

  $fields = array("image_url","thumb_url");

(the above script will only output the SQL for tables where each field name is found)

Carefully review the queries and when you are happy to execute them against your database, save the output as a .sql file eg. imgmod.sql and then from the command line, in the same diretory as imgmod.sql run mysql and then use

source imgmod.sql;

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by Keeop on Wed, 2017-08-30 16:16

That's great David, thanks. I'll give that a go after a backup!

Good point about the 'feeds' table. I should really know it all inside out by now!

Cheers.
Keeop

Submitted by Keeop on Fri, 2017-09-01 09:44

Hi David,

Script ran very well - thanks for that, saved a lot of work!

One thing, though, is that I am now having problems with AWIN images using the imagecache.php script. This only seems to have happened since making these changes but I'm sure isjust coincidence. The problem images are when using the URI/AWIMAGE image rather than the merchants' own, so must be something weird with the URL. If I copy the URL from the table straight to a browser, no problems. It's just when I try and use the image caching script. Any ideas please?

Cheers.
Keeop

Submitted by support on Fri, 2017-09-01 09:56

Hi Keeop,

Please could you post an example image URL that's not working with imageCache.php (i'll remove before publishing your reply) and I'll give it a go on my test server...

Thanks,
David.
--
PriceTapestry.com

Submitted by Keeop on Fri, 2017-09-01 10:05

Hi David,

Do you want to carry this on via email as I'm sure this is totally unrelated to the DB changes, so I don't think my comments are any help here! I'll ping you across the current script I'm using.

Cheers.
Keeop

Submitted by support on Fri, 2017-09-01 10:07

No problem!

Cheers,
David.
--
PriceTapestry.com