0

Having this field in my database "description" (MariaDB, PHPMyAdmin):

<ul>
<li><a href="https://onedomain.com/name-of-product-18-8433290610079.html">Product 1 </a></li>
<li><a href="https://onedomain.com/name-of-product-15-8433290610079.html">Product 2</a></li>
</ul>

I want to update it to this (notice categoryName of IdProduct):

<ul>
<li><a href="https://newdomain.com/category-name-IdProduct18/18-name-of-product.html">Product 1</a></li>
<li><a href="https://newdomain.com/category-name-IdProduct15/15-name-of-product.html">Product 2</a></li>
</ul>

I have this query that picks up the pattern correctly (IdProduct = this pattern ([0-9]{1,5}) ) and the IdProduct correctly (second '\2') , the problem comes when making the subquery inside the concat expression since it doesn't pick up the value of IdProduct correctly ((first '\2') in subquery). It works if I put a literal value. For example 1. WHERE p3.id_product = 1

Here is my code:

UPDATE ps_product_lang pl
LEFT JOIN ps_product p ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_lang cl2 ON (p.id_category_default = cl2.id_category)
SET pl.description = REGEXP_REPLACE(pl.description, '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*.html|.html)', 
CONCAT('<a href="https://newdomain.com/'
, Replace((SELECT LOWER(cl3.name) FROM ps_product p3 LEFT JOIN ps_category_lang cl3 ON (cl3.id_category = p3.id_category_default) 
WHERE p3.id_product = '\\2'), ' ', '-') 
, '/' , '\\2', '-' , Replace(LOWER(pl.name), ' ', '-') , '.html'))

I tried with this and it does not work for me either: WHERE p3.id_product = CAST('\\2' AS UNSIGNED)

I have also tried doing another subquey inside of WHERE p3.id_product = (subquery) but it returns only the first link found in the description (in this case productId=18) in all cases. Here is my code and result:

UPDATE ps_product_lang pl
LEFT JOIN ps_product p ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_lang cl2 ON (p.id_category_default = cl2.id_category)
SET pl.description = REGEXP_REPLACE(pl.description, '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*.html|.html)', 
CONCAT('<a href="https://newdomain.com/'
, Replace((SELECT LOWER(cl3.name) FROM ps_product p3 LEFT JOIN ps_category_lang cl3 ON (cl3.id_category = p3.id_category_default) 
WHERE p3.id_product = 
(SELECT REGEXP_REPLACE(REGEXP_SUBSTR(pl2.description, '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*.html|.html)'), '<a href="https://onedomain.com/(.*)-([0-9]{1,5})(?:-.*.html|.html)', '\\2')
FROM ps_product_lang pl2 WHERE pl.id_product = pl2.id_product)), ' ', '-') 
, '/' , '\\2', '-' , Replace(LOWER(pl.name), ' ', '-') , '.html'))

Result (notice both category's names match category-name-IdProduct18):

<ul>
<li><a href="https://newdomain.com/category-name-IdProduct18/18-name-of-product.html">Product 1</a></li>
<li><a href="https://newdomain.com/category-name-IdProduct18/15-name-of-product.html">Product 2</a></li>
</ul>
TheCHE
  • 1
  • 1
  • `\\2` is only valid as a string passed to the `REGEXP_REPLACE` function. It's not valid as a subquery identifier. You'll probably need a separate regex function in the subquery or anything else not passes to `REGEXP_REPLACE` – danblack Jan 15 '23 at 06:26
  • Can I use the information captured with REGEXP in some way? I could make a REGEX function as a subquery but I need to use the information from the link. Thanks. – TheCHE Jan 16 '23 at 14:16

0 Answers0