0

I have this output from SQL table:

array{
   "data" => "line1
line2
line3"
}

I am using something like this query: SELECT data FROM table WHERE ...;

Can I somehow make this sql query return:

array{
   "data1" => "line1",
   "data2" => "line2",
   "data3" => "line3",
}
Stoic
  • 10,536
  • 6
  • 41
  • 60

3 Answers3

2

The short answer is: No, better do it in PHP:

list($the_array['data1'], $the_array['data2'], $the_array['data3']) =
explode("\n", $the_array['data'], 3);

The longer (or shorter) answer is: See this question of mine and dig through the links.

Community
  • 1
  • 1
AndreKR
  • 32,613
  • 18
  • 106
  • 168
1

I think the main problem here is the way you have stored the data in your database. You don't really want to be storing multiple pieces of data in one field. The data needs to be better normalised then you would not encounter this problem in the first place. Look into Database Normalisation.

But if you don't want to change your data structure you could using the explode function in php

$lineArray = explode("\n",$data);

But I would recommend looking more closely at the database structure you are using.

Chris
  • 3,036
  • 5
  • 36
  • 53
0

I don't know any SQL for that matter. Alternative: break in PHP.

$arr= explode("\n", $result['data']); // \n or space or \r\n 
$i = 0;
foreach($arr as $line){
$i++;
   $final[$i] = $line;
}
Moe Sweet
  • 3,683
  • 2
  • 34
  • 46
  • Thanks Moe. I know this can be done in PHP. I was hoping more of MySQL solution. Also, should not we rather use "PHP_EOL" constant as our delimiter in explode? – Stoic Nov 16 '11 at 02:03
  • Not at all! PHP_EOL is the line ending style of the platform the PHP script currently runs on. This has nothing to do with the data and breaks portability of your script. – AndreKR Nov 16 '11 at 02:14