1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

splitting apart a mysql column with mutliple values. Not sure how to display

Discussion in 'Databases' started by xbat, Nov 27, 2017.

  1. #1
    How would I make this dynamic?? for example -1 and -21 I have to change..
    but in some columns I only have 11 or less.I am trying to display all the data between 'add to bob' AND '€' from one column. I tried searching for pivot table etc..

    IMPORTANT >> I want each record for the column to display as a row..

    I have tried functions and other stuff.. So far this is the best maunal way I can do it but in some cases
    only a record of 11 may exist.. so I am thinking maybe some sort of left join
    or union to get the count first??? or maybe a better way?
    SELECT id, selected_y, selected_m, selected_p, substring_index(
    substring_index( `all` , 'add to bob', -21 ) , '&uro;',
    1 ) AS Rowshow, substring_index( substring_index( `all` ,
    'add to cart', -1 ) , '&uro;', 1 ) AS Rowshow2
    FROM `tp`
    WHERE `all` REGEXP 'add to bob|&uro;'
    Code (SQL):
     
    Solved! View solution.
    xbat, Nov 27, 2017 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #2
    can you create a sql fiddle with some sample data for us to play with please.
    We can probably be more helpful if we know what the actual data looks like

    http://sqlfiddle.com/
     
    sarahk, Nov 27, 2017 IP
  3. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #3
    Please let me know if this helps any -

    I edited this again.. this is almost what I need

    http://sqlfiddle.com/#!9/b101e4/18


    this should say -
    1

    YA MA add to bobTHIS IS WHAT I NEED TO DISPLAY IN ITS OWN ROW (A)
    1 YA MA add to bobTHIS IS WHAT I NEED TO DISPLAY IN ITS OWN ROW (B)
    1 YA MA add to bobTHIS IS WHAT I NEED TO DISPLAY IN ITS OWN ROW (C)
    etc....

    Also if it helps i want to add rows not columns.. I know how to add columns manually just not automatic. I know there is some way to do it with a count I am just not sure. thank you
     
    Last edited: Nov 28, 2017
    xbat, Nov 28, 2017 IP
  4. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #4
    hmm I am not sure what a efficient way to do this.. but if I could get my counts correct then I believe I could do it correctly.. or maybe there is a better way to go about it. This is what I have so far. http://sqlfiddle.com/#!9/558beb5/6 hmm

    i'm thinking of using my select union all select etc.. as a count to run X many times??
     
    xbat, Nov 28, 2017 IP
  5. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #5
    It appears you are not storing your data correctly. Unless I am mistaken, you are storing multiple pieces if data in the same field. If that is the case, you need to look up and learn Database Normalization and then fix your Database accordingly.
     
    Benanamen, Nov 28, 2017 IP
  6. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #6
    Please do not take this the wrong. I truley do appreicate the effort I really do. 2 heads are always better than one, and yes you are completely correct. I totally agree with you. It should not be stored this way at all. All in a perfect world it would not be stored that way. However..... haha it was stored this way and now I am stuck sorting out someone elses mess aghhh.. But I am still looking for a answer to fix the problem that should not be. I know there is a way to do. I did something like that a long time ago I just can't remember how I did it. Anyways thank you again for the effort.
     
    xbat, Nov 28, 2017 IP
  7. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #7
    If you knew how, do you have the option to correct the DB? If so, if you want to provide an SQL dump of the DB I will see about fixing it for you.
     
    Benanamen, Nov 28, 2017 IP
  8. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #8
    Yes once I get that figured out of how to break it down then I am going to restructure the database. Its about 1half a millon records, and i found some fields have as many as 71 in them too haha to make things even better.
     
    xbat, Nov 28, 2017 IP
  9. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #9
    Do you know about ETL? That is one option. Depending on the multi-data you may be able to do it with SQL. If you can provide a small set of records, 100 or so, I can tell you what approach to take. It could be easy, or could be not.
     
    Benanamen, Nov 28, 2017 IP
  10. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #10

    I'm not sure what you are talking about when it comes to ETL. I have a example in sql fiddle - http://sqlfiddle.com/#!9/558beb5/6
     
    xbat, Nov 28, 2017 IP
  11. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #11
    Does your actual data say "THIS IS WHAT I NEED TO DISPLAY IN ITS OWN ROW"? I need to see the data as it actually is in the DB.
     
    Benanamen, Nov 28, 2017 IP
  12. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #12
    yes but there is just a handful of those. Its exactly like the examples.
     
    xbat, Nov 28, 2017 IP
  13. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #13
    If you have the authority I'd start by fixing up the database
    • create the correct structure
    • change the forms that capture the data
    • change the historic info to fit the correct structure
    It'll be painful but if you leave it like this the job will just get harder and harder.

    Using views you can create a normalised looking version of the data and then query it but I'd be putting your current request on hold until the data is clean.

    BTW I learnt SQL in the '90s and data storage in the '80s and I've never seen crap like this! There are no excuses for the person who saddled you with this data.
     
    sarahk, Nov 28, 2017 IP
  14. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #14
    haha thank you. yea in all honesty this is one of the easier jobs as much as I hate to say. haha and some people wonder why it takes me so long to figure this stuff out. haha I totally agree with you on the old stuff but I still need to find a solution to doing this. Thank you for the effort though. In case anyone is interested - I do have this - I found out how do a count. and now I think about it a row or column should work... - so if I can split this into rows or columns that would work too i am going to try some sort of union join but with a number of some sort... believe it or not I did something like this years ago.. just can't find my old notes. - http://sqlfiddle.com/#!9/8a8a8/2
     
    Last edited: Nov 28, 2017
    xbat, Nov 28, 2017 IP
  15. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #15
    Actually just came up with another idea on that last sqlfiddle i sent you... I could use that then use a foreach loop count and break it apart at &uro; HA that should work.... Not completely mysql but that should work well enough. I will post back whats going on.
     
    Last edited: Nov 28, 2017
    xbat, Nov 28, 2017 IP
  16. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #16
    You should stop what your doing for a minute and learn what ETL is and how to do it.
    https://en.wikipedia.org/wiki/Extract,_transform,_load
     
    Benanamen, Nov 28, 2017 IP
  17. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #17
    What do you mean by stop what I am doing? If you have another solution to extract that data I am all ears. I went to the page but I am sorry I do not understand what you are trying to tell me. Maybe dumb it down for me?
     
    xbat, Nov 28, 2017 IP
  18. #18
    try something like this and then query the table TempBob
    better still, build it into a view

    Note: myProc doesn't compile - years since I've needed to do procedures, I'm sure the error will be obvious to @xbat who is doing this stuff all the time

    
    CREATE TABLE IF NOT EXISTS `tp` (
      `id` int(11) unsigned NOT NULL,
      `selected_y` varchar(200) NOT NULL,
      `selected_m` varchar(200) NOT NULL,
      `all` TEXT,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    INSERT INTO `TP` (`id`,`selected_y`,`selected_m`,`all`) VALUES
      ('1', 'YA','MA', 'add to bobTextString (1A)&uro; add to bobTextString (1b)&uro; add to bobbobTextString (1c)&uro; add to bobTextString (1d)&uro;'),
      ('2', 'YB','MB', 'add to bobTextString (2A)&uro; add to bobTextString (2b)&uro; add to bobTextString (2c)&uro;'),
      ('3', 'YC','MC',  'add to bobTextString (3A)&uro; add to bobTextString (3b)&uro; add to bobTextString (3c)&uro;'),
      ('4', 'YD','MD',  'add to bobTextString (4A)&uro; add to bobTextString (4b)&uro; add to bobTextString (4c)&uro;');
    
    CREATE TABLE IF NOT EXISTS TempBob(tp_id int(11), str varchar(255));
    
    CREATE FUNCTION SPLIT_STRING(str VARCHAR(255)
                                 , delim1 VARCHAR(25)
                                 , delim2 VARCHAR(25)
                                 , pos INT)
    RETURNS VARCHAR(255)
    RETURN replace(REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim1, pos+1),
           LENGTH(SUBSTRING_INDEX(str, delim2, pos-1)) + 1),
           delim1, ''), delim2,'');
           
    CREATE FUNCTION count_str(haystack TEXT, needle VARCHAR(32))
      RETURNS INTEGER DETERMINISTIC
      RETURN ROUND((CHAR_LENGTH(haystack) - CHAR_LENGTH(REPLACE(haystack, needle, ""))) / CHAR_LENGTH(needle));
    
    
    CREATE PROCEDURE myProc() MODIFIES SQL DATA 
      BEGIN
    
           DECLARE done         INT DEFAULT 0;
           DECLARE counter      INT;
           DECLARE loop_counter INT;
       
           DECLARE tp_id        INT;
           DECLARE tp_all       varchar(255);
    
           DECLARE tpcursor CURSOR FOR SELECT `id`, `all` FROM `tp`;
           DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
                     
           OPEN tpcursor;
           
             bob_loop: LOOP
             
             FETCH tpcursor INTO tp_id, tp_all;
       
             IF done=1 THEN       /* No more rows*/
                  LEAVE bob_loop;
             END IF;
             
            set counter = count_str(tp_all, 'add to bob');
            set loop_counter = 1;
       
            WHILE loop_counter  <= counter DO
               insert into TempBob 
                   (tp_id, SPLIT_STRING(tp_all, 'add to bob', '&uro;', loop_counter));
               SET  loop_counter = loop_counter + 1; 
           END WHILE;
                         
          END LOOP bob_loop;
          CLOSE tpcursor;
                             
    END;
    
    call myProc();
    Code (markup):
     
    sarahk, Nov 28, 2017 IP
  19. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #19

    wow thank you. haha just because I get all the hard stuff all the time doesn't mean the easy stuff is any easier. I have done some stupid things myself. I will try it and go from there. thank you again
     
    xbat, Nov 28, 2017 IP
  20. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #20
    @sarahk I am absolutely still amazed you did all that. I was looking for a dumber simpler solution but that works too thanks again so much. I am very impressed.
     
    xbat, Nov 30, 2017 IP
    sarahk likes this.