Split data to multiple columns

Question

This is the data currently located in one column:

"Title Info - Company Here - City Here, ST"

ST represents state ie GA

Sometimes my data looks like this:

"Title Info - Company Here - United States"

Data is on column named "title" and is without quotes.

How do I split this using php, for 4 new columns named:

"New_Title", "Company", "City", "State"

On second example where there is no city and state just country, it would be ok to go to "city" column.

Table schema:

CREATE TABLE table ( 
id int(8) NOT NULL DEFAULT '0', 
Title char(100) DEFAULT NULL, 
New_Title char(100) DEFAULT NULL, 
Company char(100) DEFAULT NULL, 
City char(100) DEFAULT NULL, 
State char(100) DEFAULT NULL, 
PRIMARY KEY (id) 
) 
ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thanks for your time.

Ossi

Answer 1

First split on "-", then split the third element returned on ","

<?PHP
$title = $row['title']; // or however you get the title.
$parts = explode("-",$title);
if ( count($parts) != 3) die("Invalid title: $title");
$newtitle = $parts[0];
$company = $parts[1];
$location_parts = explode(',',$parts[2]);
$city = $location_parts[0];
$state = $location_parts[1];

//now you've got $newtitle, $company, $city, and $state to do something productive with.

Answer 2

I see 2 ways:

First: use the regexp like

(^[^-]*) - ([^-]*) - ([^,]*),?(.*)$

with preg_match

Second: do not use php code at all. SQL Update will be enough

UPDATE mytable t SET t.title = SUBSTRING_INDEX(t.value, ' - ', 1), t.company = SUBSTRING_INDEX(SUBSTRING_INDEX(t.value, ' - ', 2), ' - ', -1), t.city = SUBSTRING_INDEX(t.address, ' - ', -1);

and then

UPDATE mytable t SET
t.city = SUBSTRING_INDEX(t.city ',', 1),
t.state = SUBSTRING(t.city, LENGTH(SUBSTRING_INDEX(t.city, ',', 1))+3);