Mysql query with loop

Question

I have this query, works fine for view and csv export from phpmyadmin.

Is possible create a loop without repeat? thanks!

SELECT
id, date, name,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, '-', 1), '(', -1) AS op,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, '-', 4), '-', -3) AS dt,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, ')', 1), '-', -1) AS hour,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, '(', 2), '-', -1) AS note
FROM center
WHERE center.date BETWEEN '2019-08-01 00:00:00' AND '2019-12-31 00:00:00'
and message!= ''
HAVING op = 'op1' OR op = 'op2'

UNION SELECT 
id, date, name,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, '-', 6), '(', -1) AS op,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, '-', 9), '-', -3) AS dt,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, ')', 2), '-', -1) AS hour,
SUBSTRING_INDEX(SUBSTRING_INDEX(message, '(', 3), '-', -1) AS note
FROM center
WHERE center.date BETWEEN '2019-08-01 00:00:00' AND '2019-12-31 00:00:00'
and message!= ''
HAVING op = 'op1' OR op = 'op2'

UNION SELECT.... more

Something like this: (op4 - 09-09-2019 - 8:36:15) - Lorem ipsum..... (op2 - 12-09-2019 - 8:12:12) - Lorem ipsum..... (op1 - 15-09-2019 - 9:22:11) - Lorem ipsum..... (op3 - 09-09-2019 - 9:36:50) - Lorem ipsum..... (op4 - 18-09-2019 - 7:36:22) - Lorem ipsum..... — cloude
– cloude, Commented Nov 20, 2019 at 13:42
I don't know maximum pieces, for this cause I'd like optimize my query. — cloude
– cloude, Commented Nov 22, 2019 at 10:37

Bernd Buffen · Accepted Answer · 2019-11-21 17:27:03Z

You can test this Query. It split a max. of 10 pieces from a row.

SELECT `id`,`date`,`name`,CONCAT('op',`op`) as op,`dt`,`hour`,`note` 
  ,subid,cols -- only for test. you can remove this line
FROM (
  SELECT c.id,c.date,c.name,
    cnt.*,
    -- count the pieces in one row
    (LENGTH(message)-LENGTH(replace(message,'(op','')))/3 as cols,
    -- Split String in piece and store in @content
    @content := SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(' (op ',c.message,' (op'), ' (op', subid+3), ' (op', -1)
  , SUBSTRING_INDEX(@content, ' - ',1) as op
  , SUBSTRING_INDEX( SUBSTRING_INDEX(@content, ' - ',2), ' - ',-1) as dt
  , TRIM( TRAILING ')' FROM SUBSTRING_INDEX( SUBSTRING_INDEX(@content, ' - ',3), ' - ',-1)) as hour
  , SUBSTRING_INDEX( SUBSTRING_INDEX(@content, ' - ',4), ' - ',-1) as note
  FROM center c
    CROSS JOIN (
      SELECT 0 as subid UNION ALL SELECT 1 UNION ALL SELECT 2
       UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       UNION ALL SELECT 6  UNION ALL SELECT 7  UNION ALL SELECT 8
       UNION ALL SELECT 9
    ) as cnt       
 ) as result
 WHERE 
 subid < cols
 AND `date` BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
 AND op in (1,2)
 ORDER BY id,subid,cols;

Here is a Sample : http://www.sqlfiddle.com/#!9/8bc3b4/60

Collectives™ on Stack Overflow

Mysql query with loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related