• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 859
  • Last Modified:

Parse comma-delimited string in MySQL / PHP

I have a table, called NEWS.  In it are two fields - UID and KEYWORDS.  UID is an integer (11) and KEYWORDS text.  KEYWORDS contains a list of values separated by commas.  I'm moving to a new system where tags are handled differently, and need to go from this format:

UID | KEYWORDS
123 | red, brown, blue
456 | black, yellow, white, purple

to this:

UID | TAG
123 | red
123 | brown
123 | blue
456 | black
456 | yellow
456 | white
456 | purple
0
michaelgiaimo
Asked:
michaelgiaimo
1 Solution
 
Greg AlexanderLead DeveloperCommented:
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

0
 
Greg AlexanderLead DeveloperCommented:
Sorry, use this one
<?
//include your db connection here

$query = "SELECT * FROM table_name";
$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 the_other_table SET UID = '".$row['UID'].", TAG = '".$data."'"; 
		mysql_query($query);
	}
}

?>

Open in new window

0
 
PranjalShahCommented:
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

0
 
Ray PaseurCommented:
http://www.laprbass.com/RAY_temp_michaelgiaimo.php

Outputs:
UID | TAG
123 | red
123 | brown
123 | blue
456 | black
456 | yellow
456 | white
456 | purple
<?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

0
 
michaelgiaimoAuthor Commented:
@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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now