[PhilJ]

If you use Clean URLs for SEO, which I suspect most of you are, here's some handy SQL patches (tested), for those with a medium-large size product catalog.

If you've already optimised your product SEO titles and meta descriptions, this isn't for you.

If you're going to try them, use with caution and MAKE A DB BACKUP FIRST!

1) Auto-populate empty SEO titles with the product names...

# Set all empty SEO titles to the product names
UPDATE xcart_products t1 
  INNER JOIN xcart_products_lng_en t2 
    ON t1.productid = t2.productid 
SET t1.title_tag = t2.product 
WHERE t1.title_tag = '';

If you want to just test on an individual product, for the last line above use...

WHERE t1.title_tag = '' AND t1.productid = '1234';

2a) Auto-populate empty SEO meta descriptions with the product descriptions... (read 2b first)

# Set all empty SEO meta descriptions to the product descriptions
UPDATE xcart_products t1 
  INNER JOIN xcart_products_lng_en t2 
    ON t1.productid = t2.productid 
SET t1.meta_description = t2.descr 
WHERE t1.meta_description = '';

If you want to just test on an individual product, for the last line above use...

WHERE t1.meta_description = '' AND t1.productid = '1234';

2b) Yikes, it's included product description HTML tags in the SEO meta descriptions...

Don't panic, you can strip HTML / Carriage Returns / Line Feeds with this patch... (assuming you have clean product description HTML)

# Remove HTML / Carriage Returns / Line Feeds and left trim
UPDATE xcart_products set meta_description=replace(meta_description, '<p>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</p>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<div>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</div>', ' '); 
UPDATE xcart_products set meta_description=replace(meta_description, '<b>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</b>', ' '); 
UPDATE xcart_products set meta_description=replace(meta_description, '<i>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</i>', ' '); 
UPDATE xcart_products set meta_description=replace(meta_description, '<em>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</em>', ' '); 
UPDATE xcart_products set meta_description=replace(meta_description, '<u>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</u>', ' '); 
UPDATE xcart_products set meta_description=replace(meta_description, '<ul>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</ul>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<ol>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</ol>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<li>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</li>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<span>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</span>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<hr>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<hr />', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<br>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<br />', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<h1>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</h1>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<h2>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</h2>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<h3>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</h3>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<h4>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</h4>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<h5>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</h5>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '<h6>', '');
UPDATE xcart_products set meta_description=replace(meta_description, '</h6>', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '  ', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '   ', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, '    ', ' ');
UPDATE xcart_products set meta_description=replace(meta_description, CHAR(9), '');
UPDATE xcart_products set meta_description=replace(meta_description, CHAR(10), ''); 
UPDATE xcart_products set meta_description=replace(meta_description, CHAR(13), '');
UPDATE xcart_products SET meta_description = LTRIM(meta_description);

If you've made any mistakes and want to start over...

# Clear all SEO titles
UPDATE xcart_products SET title_tag= '';

or...

# Clear SEO title on product
UPDATE xcart_products SET title_tag= '' WHERE productid = '1234';

# Clear all SEO meta descriptions
 UPDATE xcart_products SET meta_description = '';

or...

# Clear SEO meta description on product
UPDATE xcart_products SET meta_description = '' WHERE productid = '1234';