how to tokenize string in record to be row

Question

I have the data in a database table named term like this :

          ---------------------------------------
          id_term | keyword                     |
          ------- | -----------------------------
              1   | how to make search engine   |
              2   | application engineering     |
              3   | android application example |
           --------------------------------------

then I want it to be like this table :

           ----------------------------------
           | id_term | keyword              |
           ----------------------------------
                   1 | how                  |
                   1 | to                   |
                   1 | Make                 |
                   1 | search               |
                   1 | engine               |
                   2 | application          |
                   2 | engineering          |
                   3 | example              |
                   3 | application          |
                   3 | android              |
           ----------------------------------

I've tried googling to find references to split the string, but still have not found the appropriate expectations. In an experiment that I've done using substring_index results I could actually like this:

              ---------------------------------------
              id_term | keyword                     |
              ------- | -------------------------------
                    1 | how to make search engine   |
                    1 | how to make search engine   |
                    1 | how to make search engine   |
                 --------------------------------------

there anything you can help me or has the solution of my problem? mysql code that I try something like this:

 select term.id_kata, SUBSTRING_INDEX (SUBSTRING_INDEX (term.keyword, ',', term.id_kata), ',', -1) keyword term from inner join keyword_doc on CHAR_LENGTH (term.keyword) -CHAR_LENGTH (REPLACE (term.keyword , ',', ''))> = term.id_kata-1 ORDER BY `term`.`keyword` DESC

I've tried googling for approximately 5 hours to find a solution, but have not found until I was confused to be asked where. there any ideas or can help provide a solution?

Possible duplicate of [SQL split values to multiple rows](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) — Alex K., Jan 30 '17 at 14:39
MySQL isn't necessarily the best tool to scrub data in this way. Are you also using any application languages? — Tim Biegeleisen, Jan 30 '17 at 14:40
Tim Biegeleisen : yes i use PHP too i want to execute that from php — user3718278, Jan 30 '17 at 14:58

score 0 · Accepted Answer · answered Mar 06 '17 at 02:24

The solution for it problem is please take 'BETWEEN' in SQL SYNTAX. this code 100% work for it problem :

      <?php
      #connection
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'pasword';
      $dbname = 'yourdatabasename';
      error_reporting(E_ALL ^ E_DEPRECATED);
      mysql_connect($dbhost,$dbuser,$dbpass) or die(mysqli_error('cannot   connect to the server')); 
      mysql_select_db($dbname) or die(mysqli('database selection     problem'));

     $frequency = array();
    $datastring = 'SELECT id_term,keyword FROM data_token WHERE id_term   BETWEEN 1 AND 3'; 
    mysql_select_db('yourdatabasename');
    $calldata = mysql_query($datastring);
    while($takedata = mysql_fetch_array($calldata,MYSQL_ASSOC)) 
     {
    $array = explode("\n",$takedata['keyword']);
        foreach ($array as $index => $keyword)
        {
            if(! array_key_exists($keyword,$frequency))
            {
                $frequency[$keyword] = 1;
            }   
            else
            {
                $frequency[$keyword] = $frequency[$keyword] + 1;
            }
        }

        $document = $takedata['id_term'];
        foreach ($frequency as $term => $tf)
        {
            $sqlInput = "INSERT INTO yourtablename  (id_term,keyword,frequency) VALUES ('$dokumen','{$term}', {$tf})";
            mysql_query($sqlInput);
            }
         }

     ?>

how to tokenize string in record to be row

1 Answers1