asked on

Parse comma-delimited string in MySQL / PHP

Greg Alexander

This should work

<?
//include your db connection here

$query = "SELECT * FROM table_name";
$result = mysql_result($query);

while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
	$data_array = explode(",",$row['KEYWORDS']);
	
	foreach($data_array as $data){
		$query = "INSERT INTO the_other_table SET UID = '".$row['UID'].", TAG = '".$data."'"; 
		mysql_query($query);
	}
}

?>

Open in new window

ASKER CERTIFIED SOLUTION

Greg Alexander

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PranjalShah

Using php explode, first get the keywords from the database, once you have the keywords, use explode. You will be setting the limit for that. http://php.net/manual/en/function.explode.php

Ray Paseur

<?php // RAY_temp_michaelgiaimo.php
error_reporting(E_ALL);
echo "<pre>";

// TEST DATA STRING FROM THE POST AT EE
$str = <<<ENDOFDATA
UID | KEYWORDS
123 | red, brown, blue
456 | black, yellow, white, purple
ENDOFDATA;

// TAKE THE DATA STRING AND MAKE AN ARRAY WITH ONE LINE IN EACH ELEMENT
$arr = explode(PHP_EOL, $str);

// DISCARD THE FIRST ELEMENT OF THE ARRAY (NOT MEANINGFUL DATA)
unset($arr[0]);

// USE A NEST OF ITERATORS TO CREATE THE NEW DATA FORMAT
$new = array();
foreach ($arr as $old)
{
    // BREAK EACH STRING INTO AN ARRAY ON THE PIPE SYMBOL
    $sub = explode('|', $old);
    $sub[0] = trim($sub[0]);

    // BREAK THE KEYWORDS INTO AN ARRAY ON THE COMMA
    $kws = explode(',', $sub[1]);

    foreach ($kws as $key)
    {
        $key = trim($key);
        $new[] = array( $sub[0], $key );
    }
}

// SHOW THE WORK PRODUCT -- var_dump($new);
echo "UID | TAG";
foreach ($new as $tag)
{
    echo PHP_EOL . $tag[0] . ' | ' , $tag[1];
}

Open in new window

michaelgiaimo

ASKER

@galexander:

Small issue with the INSERT syntax but I was able to get it working - thanks!

<?

mysql_connect("localhost", "xxxxxxxxx", "xxxxxxx") or
    die("Could not connect: " . mysql_error());
mysql_select_db("xxxxxxxx");

$query = "SELECT * FROM _mbg_tt_news";
$result = mysql_query($query);

while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
	$data_array = explode(",",$row['keywords']);
	
	foreach($data_array as $data){
		$query = "INSERT INTO _mbg_tags VALUES(".$row['uid'].",'".$data."')"; 
		mysql_query($query);
	}
}

?>

Open in new window