0

I do wanted to extract certain values from database to reload files into new server. I could do copy and paste from old server into new one, but I do not wanted to have old files that are no longer in use there. So that's what I'm trying to do.

In this products_description table, it stored HTML codes.

Here is the schema

Sample Product Data

I wanted to pulled off the following substring:

/zc156/product_pdf/[manufacturer]/[name].pdf
/zc156/product_img/[manufacturer]/[name].jpg
/zc156/product_img/[manufacturer]/[name].png

I did managed to obtained most (if not all PDFs) files by using the following 2 queries...

For first PDF file

SELECT
    SUBSTRING(`products_description`.`products_description`, 
      LOCATE('/zc156/product_pdf/', `products_description`.`products_description`),
      LOCATE('.pdf', `products_description`.`products_description`) - LOCATE('/zc156/product_pdf/', `products_description`.`products_description`) + 4)
AS 'ProductFile' FROM `products_description`
INNER JOIN `products` ON `products`.`products_id` = `products_description`.`products_id`
WHERE CONVERT(`products_description` USING utf8) LIKE '%/zc156/product_pdf/%'
AND `products`.`products_status` = 1

For the rest of PDF files

SELECT
    SUBSTRING(`products_description`.`products_description`, 
      LOCATE('</a>', `products_description`.`products_description`),
      LOCATE('</tr>', `products_description`.`products_description`) - LOCATE('</a>', `products_description`.`products_description`))
AS 'ProductFile' FROM `products_description`
INNER JOIN `products` ON `products`.`products_id` = `products_description`.`products_id`
WHERE CONVERT(`products_description` USING utf8) LIKE '%/zc156/product_pdf/%'
AND `products`.`products_status` = 1

However, when it comes to pull off JPG or PNG filenames... It's rougher to do substring as image filenames are span more than one cells and they repeated in certain pattern... I tried to search here and I found this topic.

Extract substring with a specific pattern in MySql

So, I tried to put some of my own data... but my result is not what I wanted. SQL Fiddle Attempt

Therefore, I wonder if there is a cleaner way to do this. Possibly may have to stack with couple substring and/or left function.

PanK
  • 3
  • 2
  • buddy, why don't you consider to handle this operation on your application side? it would be much more simpler than this. – Derviş Kayımbaşıoğlu Feb 13 '19 at 22:03
  • "why don't you consider to handle this operation on your application side? it would be much more simpler than this" @Simonare is right i once posted a "HTML parser and data replacer" in [pure MySQL code](https://stackoverflow.com/a/49561238/2548147) – Raymond Nijland Feb 13 '19 at 22:12
  • Thank you @Simonare0 & Raymond for comments. I found a way to extract based on Raymond's earlier replies. Here is my version: https://pastebin.com/FzF7urYN I substituted the HTML text as a field `SELECT products_description.products_description AS data FROM products_description` and put `WHERE` conditions to match my needs. I added couple more `AND` conditions into the final line of `WHERE` statement to only show results of HTML tags I needed. `WHERE tags.tag != '>' AND tag LIKE '% – PanK Feb 14 '19 at 17:34
  • It is not fastest way to extract for sure (I have to divided into 300 product IDs per query and it took about 120 seconds (3 minutes) to perform the query), but it does get the job done without using PHP file or User Defined function. – PanK Feb 14 '19 at 17:36

0 Answers0