CODEWITHSUNDEEP
phpmysqlarraysflatten
Sofyan Sitorus
Sofyan Sitorus

Reputation: 77

Create flat array of individual values from rows of comma-separated values fetched from a database table

Please help me to create an array from a field of my DB. That field has records separated by comma. Below is the illustration:

ID | article_title_fld         | article_tags_fld                     |
----------------------------------------------------------------------
1  | Learn PHP                 | PHP, coding, scripting               |
3  | Javascript Tutorial       | Javascript, scripting, tutorial      |
4  | Styling with CSS          | CSS, tutorial, web design            |

I want to collect all records in the article_tags_fld then put it into 1 array. Perhaps I named it $array1, and the print out as below:

Array
(
[0] => PHP
[1] => coding
[2] => scripting
[3] => Javascript
[4] => scripting
[5] => tutorial
[6] => CSS
[7] => tutorial
[8] => web design
)

Upvotes: 1

Views: 19611

Answers (5)

mickmackusa
mickmackusa

Reputation: 47874

  1. You should normalize your database table so that you don't have comma-separated values in your rows.
  2. mysql_ functions should no longer be used, so I'll demonstrate mysqli's object-oriented syntax.
  3. If your database values are consistently comma-space separated, then explode() is fine. If you might have leading or trailing spaces in your article_tags_fld values, then you can TRIM() that in your SQL. If you might not have spaces after each comma, then you can explode the strings with preg_split() using a pattern like /, */.
  4. The result set object from a mysqli query is iterable by foreach(), so you can safely avoid making iterated fetch() calls to access the column of data.
  5. By pushing the exploded values with the spread operator (...), the result array will be flat.
  6. If you want to remove duplicate values from the result, you can call array_unique() on the result array after the loop is finished.

Modernized Code: (Full MYSQL + PHP Demo)

$result = [];
foreach ($mysqli->query("SELECT article_tags_fld FROM MY_TABLE") as $row) {
    array_push($result, ...explode(', ', $row['article_tags_fld']));
}
var_export(array_unique($result));

Upvotes: 0

Luca Filosofi
Luca Filosofi

Reputation: 31173

UPDATED

you can do also this way by using mysql_fetch_Array

$array1 = array();
$result = mysql_query("SELECT ID , article_tags_fld FROM my_table");

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    $array1[] = $row['article_tags_fld'];
    // OR 
    // $array1[] = $row[1];
}

ADDED: in one line:

// use this version to behave as in your example..
$array1 = array_map('trim',explode(',',implode(',',$array1))); 

// use this version with array_unique for a non duplicate way...    
$array1 = array_unique(array_map('trim',explode(',',implode(',',$array1))));

DISPLAY: 

 print_r( $array1 );

Array
(
    [0] => PHP
    [1] => coding
    [2] => scripting
    [3] => Javascript
    [4] => scripting
    [5] => tutorial
    [6] => CSS
    [7] => tutorial
    [8] => web design
)

Upvotes: 2

Geoff
Geoff

Reputation: 8135

$array1 = array();
$result = mysql_query("SELECT article_tags_fld FROM MY_TABLE");
while ($row = mysql_fetch_assoc($result)) {
   $array1 = array_merge($array1, array_map('trim', explode(",", $row['article_tags_fld'])));
}

explode will split a string by a delimiter.

array_merge will combine two arrays.

array_map will apply trim to all elements.

trim will remove any white space on either side of your tags.

Upvotes: 8

cwallenpoole
cwallenpoole

Reputation: 81988

This should output everything.

$pdo = new PDO( /* CONNECTION */ );
$stmt = $pdo->query( 'SELECT article_tags_fld FROM TABLENAME' );

function mapFunc( $row ){ return explode( ',', $row[ 0 ] ) }

$all = array();
foreach( array_map( 'mapFunc', $stmt->fetchAll() ) as $row )
{
   $all = array_merge( $all, $row );
}
// $all now holds all of the values.

Upvotes: 1

Powerlord
Powerlord

Reputation: 88786

Actually, I would normalize the table into multiple tables first.

articles

article_ID | article_title_fld         |
----------------------------------------
1          | Learn PHP                 |
3          | Javascript Tutorial       |
4          | Styling with CSS          |

tags

tag_ID | tag_title_fld |
------------------------
1      | PHP           |
2      | coding        |
3      | scripting     |
4      | Javascript    |
5      | tutorial      |
6      | CSS           |
7      | web design    |

article_tags

article_ID | tag_ID |
---------------------
1          | 1      |
1          | 2      |
1          | 3      |
3          | 4      |
3          | 3      |
3          | 5      |
4          | 6      |
4          | 5      |
4          | 7      |

and then in the PHP

$array1 = array();
$result = mysql_query("
SELECT tag_title_fld
FROM tags
JOIN articles_tags USING(tag_ID)
ORDER BY article.article_ID
");
foreach($result as $row) { 
   $array1[] = $row['tag_title_fld'];
   // With count. See below for the query it goes with
   // $array2 = array();
   // $array2['tag'] = $row['tag_title_fld'];
   // $array2['count'] = $row['tag_count'];
   // $array1[] = $array2;
}

Of course, if you just wanted a list of the tags without duplicates, you'd use this query instead.

SELECT tag_title_fld
FROM tags

and if you wanted them with a count of how often they're used:

SELECT tag_title_fld, COUNT(*) AS tag_count
FROM tags
JOIN articles_tags USING(tag_ID)
GROUP BY tag_title_fld

Upvotes: 7

Related Questions