2

I got table in my DB with pairs EVENT_ID - ATTENDANTS varchar(300) containing data in form of string concatenated from repetitive attributes and unique user names. Delimiter between attribute and name is ":" and every collection of "attribute:user" ends with ";" There are spaces in some attributes names. Amount of pairs in every line is unknown.

"attributeC:user_name1;attributeA:user_name3;attributeA:user_name4;attributeA:user_name10;attributeB:user_name42;"

Then i got table with pairs USER_ID - USER_NAME and destination table with fields USER_ID - EVENT_ID - ATTRIBUTE.

What is the best way to extract users and attributes and insert them into destination table?

I would love to do this in SQL (MySQL), in case of using functions etc, making them temporary, script will be run only one time.

If it would be to much complicated, I am open to use PHP with OOP mysqli functions. (It will be run in set of other $mysqli->query() commands)

I found, that MySQL functions SUBSTRING(), LOCATE(), LENGTH() could come handy.

I am able to do this in some laborious way via PHP

  1. make array based on SELECT(USER_ID - USER_NAME)
  2. make SELECT(EVENT_ID - ATTENDANTS) and for every row call explode(), get attribute, EVENT_ID, USER_ID (from array above, instead of SQL querying for every username in every string for every event)
  3. paralelly insert data to destination table with mysqli prepared statement

But I think (hope?) there is some better way. What do you think?

Improve this question

2 Answers 2

Reset to default
1

You can do this rather painfully in MySQL. Here is an example for getting the first three such strings:

select t.col, n.n,
       substring_index(substring_index(t.col, ';', n.n), ';', -1) as pair,
       substring_index(substring_index(substring_index(t.col, ';', n.n), ';', -1), ':', 1) as name,
       substring_index(substring_index(substring_index(t.col, ';', n.n), ';', -1), ':', -1) as value
from t join
     (select 1 as n union all select 2 union all select 3
     ) n
     on length(t.col) - length(replace(t.col, ';', '') + 1 <= n.n;

You just need to add the additional numbers to the n subquery to get more pairs.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I suppose: t.col = my_table.ATTENDANTS, n = number of repeats, what exactly is n.n ? I had to add, the number of pairs is unknown and most likely different for every EVENT line.
@Faramos . . . The on clause should handle that. You just need values of n that are big enough to get every pair.
0

After some research, I solved it and it´s not as dumb way as it may look like.

Code first:

select OUTPUT.ID, OUTPUT.name, OUTPUT.attribute from
(
select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 1), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 1), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 2), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 2), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 3), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 3), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 4), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';',4), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 5), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 5), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 6), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 6), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 7), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 7), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 8), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 8), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 9), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 9), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 10), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 10), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 11), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 11), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 12), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 12), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 13), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 13), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 14), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 14), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 15), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 15), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 16), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 16), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 17), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 17), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 18), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 18), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 19), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 19), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 20), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 20), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 21), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 21), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 22), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 22), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 23), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 23), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 24), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 24), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 25), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 25), ';', -1), ':', -1) as name
from AKCE

order by ID
) AS OUTPUT WHERE OUTPUT.name!='' AND OUTPUT.attribute!=''

As you can see, it is long, full of redundant code etc.

When you use substring_index() with position greater than number of deimiters inside, it will return empty field. And because of unions, those empty fields are returned just one time per original table row (AKCE.ID).

Those empty rows are filtered with WHERE ... != ' '

It certainly isn´t the right way, but on 150 rows with maximum 23 pairs it works pretty good nad fast (only 10 times slower than basic select on that table)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.