Is there any query that can do database modification from one column to multiple columns?

Question

I am using MySQL. I have a table called entries. This table has a column called body. The body column has string data (about 500 words long).

Now, I want transfer above body column into column_1,column_2,.... column_300 which contains nth word in each body column. So, if body column has a data like "I ate a lunch today", then column_1 would have 'I' , column_2 would have 'ate', and so on. I guess I can work this in PHP, but I have been wondering if it is possible in query in MySQL.

Why you want such thing? You are just doing replication and wasting time and effort. — Harry Joy, Apr 29 '11 at 06:57
I am doing it to make csv file (by exporting) which has 300 words separated by commas... I guess i can do the job done by PHP. — user482594, Apr 29 '11 at 07:00
Then just make the CSV and output it using PHP. No need to make a physical table structure with 300 columns just for that purpose. — deceze, Apr 29 '11 at 07:01

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

See: Split value from one field to two

edited May 23 '17 at 11:55

Community

1
1

answered Apr 29 '11 at 06:59

entropo

2,481
15
15

score 0 · Answer 2 · answered Apr 29 '11 at 07:08

Here you have a php solution.

<?php
  //Connect to mysql server
  $cn = mysql_pconnect("server", "username", "password");
  mysql_select_db("database_name");
  $rs = mysql_query("SELECT id, body FROM entries", $cn);  

  //Traverse each entry row      
  while($row = mysql_fetch_array($rs)){
     list($id, $body) = $row;
     $words = explode(" ", $body); //split by spaces (can be improved)
     $sqlUpdate = "UPDATE entries SET ";
     $sets = array();
     //Traverse words
     for($i=1; i<=count($words); $i++){
        $word_for_mysql = mysql_escape_string($words[$i-1]); //$i-1 cause arrays are zero-based index
        $sets[] = "column_$i = '$word_for_mysql'"; //i.e: column_1 = 'lorem'
     }
     $sqlUpdate.= join(", ", $sets)." WHERE id='$id'";     
     mysql_query($sqlUpdate, $cn);
  }
?>

Anyway, I wonder why you want to do this

Is there any query that can do database modification from one column to multiple columns?

2 Answers2