Splitting out existing records into separate fields

4 posts by 3 authors in: Forums > CMS Builder
Last Post: November 29, 2013   (RSS)

By zaba - November 27, 2013

Hi, I wonder if anyone can help me with this problem, its not technically a cmsb issue, but hoping some good hearted soul can help me with this.

I have an address field which is a text box containing the full address, i.e the filed contents will be something like "address 1 <br> address 2 <br> city  <br> postcode <br> country"

The client now wants this data in separate fields. I have no problem with changing this for all new records as the data is collected separately from a form and merged before it goes in to the database, the problem I have is all the thousands of existing records that need to be split and input into their relevant fields. 

Is there a way to do this as a php script I can run once or some snazzy sql that I can run in phpmyadmin on the database to split out the existing records into the new separate fields I have created?

please let me know if you need any more info.

By gkornbluth - November 27, 2013

Hi zaba,

Take a look at my recent post at,

http://www.interactivetools.com/forum/forum-posts.php?extracting-an-email-address-from-a-text-field-79134

Dave's input might be of some help.

In that approach, I think the trick is going to be finding a common pattern (like the <br>) to get the individual segments out of the array.

Wish I had a good solution for you but hopefully someone will.

Best,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Daryl - November 28, 2013

Hi zaba,

We're in luck if the full addresses are separated by <br> tags as we can use explode with the <br> tag as our delimiter. For example:

$fullAddress = "address 1 <br> address 2 <br> city  <br> postcode <br> country";
$addressPieces = explode('<br>', $fullAddress);

The code above will give us an array values for $addressPieces that we can use to save the "separated" address to their own field:

/* array values of $addressPieces
Array (
[0] => address 1
[1] => address 2
[2] => city
[3] => postcode
[4] => country
)
*/
$columnToValues = array(
'address_line1' => $addressPieces[0],
'address_line2' => $addressPieces[1],
'city ' => $addressPieces[2],
'postcode ' => $addressPieces[3],
'country' => $addressPieces[4],
)
mysql_update($tableName, $recordNum, null, $columnToValues);

And using the sample code above, what I would do is I will write a one-time script  that I will run once to loop through all the old records that needs the full address field to be separated into several fields. Explode their full address, and put them in the right field.

For example:

foreach($accoutnRecords as $record){
    if ($record['full_address']){
        $addressPieces = explode('<br>', $record['full_address']);
        
        $columnToValues = array(
            'address_line1' => $addressPieces[0],
            'address_line2' => $addressPieces[1],
            'city'          => $addressPieces[2],
            'postcode'      => $addressPieces[3],
            'country'       => $addressPieces[4],
        )
        mysql_update($tableName, $record['num'], null, $columnToValues);
    }
}

Hope this help!

Cheers,

Daryl Maximo
PHP Programmer - interactivetools.com