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

timdev · Accepted Answer · 2009-10-05 05:01:56Z

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.

answered Oct 5, 2009 at 5:01

timdev

63.1k6 gold badges86 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ossi Over a year ago

Sweet ;) Would you have code handy for me to insert these as well? I have column 'id' as key. If not its ok, I figure out. Thanks again.

timdev Over a year ago

That's a good exercise for you to do, plenty of tutorials about writing to databases all over the place :-)

Ossi Over a year ago

It works great. How do I suppress this worning Notice: Undefined offset: 1 its on this line $state = $location_parts[1]; it only happens, when there is no state. like I said, it works awesome otherwise ;)

timdev Over a year ago

Just test with empty() before using the value. if (! empty($parts[0])) $newtitle = $parts[0]; -style

Ossi Over a year ago

Tried to suppress the Notice with this: if (! empty($location_parts[1])) $state = $location_parts[1]; Also tried this: if (isset($location_parts[1])) { $state = $location_parts[1]; } And cant fix it. Notice disappears, but now next record will get previous records state??? I'm going to bed.

Meta · Accepted Answer · 2009-10-05 05:33:23Z

0

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);

answered Oct 5, 2009 at 5:33

Collectives™ on Stack Overflow

Split data to multiple columns

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related