How could I run thru an SQL db and edit this...

[krazyjosh5]

Im trying to go through an SQL db of stores and, in the address field, remove everything but the first line BEFORE the <br /> so its only the street number and street name left in the address field...

So, for example, the address field for record 1 after editing would be:
77 Whatever Rd

BUT

I want to take the zip code, which is the last 5 characters in the address field text string, and move that to a new column called 'zip' in the same table.

Suggestions? Flex those SQL muscles developers...


EDIT: I got the columns duplicated. I now have the address column and a new column called 'zip' with the same contents.

If anyone can generate the regex expression to select the first line of an address text and another expression to select the last 5 characters of an address text, thatd be a big part of the equation.

77 Whatever Rd<br />
[26 spaces are right here]Balls<br />
[26 spaces are right here]Alabama<br />
[26 spaces are right here]87891

(so expression 1 would select 77 Whatever Rd)
(and expression 2 would select 87891)

I think expression 2 would be:
[(26 spaces right here, no parentheses)][1-9][0-9]{4}

 

[jened]

it's actually sql muscles

 

[krazyjosh5]

thanks. im a bit rusty on my programming anatomy...

 

[drugoon]

I think that you will have to do a script that reads your table, extracts information from the desired field and then writes back to a new table. I don't think you can do this with an sql command

 

[krazyjosh5]

yeah im thinking it will be a short query/while-conditional script.

I got the columns duplicated, now I just need the appropriate regex expressions to select the data and I can write the script to run through and change all that data.

 

[krazyjosh5]

bump for help with this

 

[emp]

Err.. is the <br> actually part of the content?

::emp::

 

[maxor]

Krazy... What exactly are you trying to do?

If your address field is in the following format:

77 Whatever Rd<br />
                          Balls<br />
                          Alabama<br />
                          87891

You could loop through all of the records, use PHP's explode function to split that string apart at the <br /> tags. Then you could extract whichever part of the resulting array you needed and update the database.

I guess I'm not really understanding what exactly you're trying to do.

 

[Xrproto]

you should have set your table up like these seperate columns for the address

address 1
address 2
City
State
Zip

If I were you I would add those columns to my DB (depending on a few this) and parse the existing data and re-dump it into there new columns. I don't have time to write a script for you since I'm out the door in a few minutes.

 

[Xrproto]

Krazy... What exactly are you trying to do?

If your address field is in the following format:

77 Whatever Rd<br />
                          Balls<br />
                          Alabama<br />
                          87891

You could loop through all of the records, use PHP's explode function to split that string apart at the <br /> tags. Then you could extract whichever part of the resulting array you needed and update the database.

I guess I'm not really understanding what exactly you're trying to do.

I think that's what he wants, I would write a script but I gotta go.

 

[maxor]

you should have set your table up like these seperate columns for the address

address 1
address 2
City
State
Zip

If I were you I would add those columns to my DB (depending on a few this) and parse the existing data and re-dump it into there new columns. I don't have time to write a script for you since I'm out the door in a few minutes.

This is a good idea, however I tend to use address and address_more rather than address_1 and address_2.

 

[krazyjosh5]

Krazy... What exactly are you trying to do?

If your address field is in the following format:

77 Whatever Rd<br />
                          Balls<br />
                          Alabama<br />
                          87891

You could loop through all of the records, use PHP's explode function to split that string apart at the <br /> tags. Then you could extract whichever part of the resulting array you needed and update the database.

I guess I'm not really understanding what exactly you're trying to do.

Ok, Ill try to do that.

Yes, that is what Im trying to do.
I want all the first lines of the addresses (77 Whatever Rd's) to be in one column and the zipcodes to be in another.

The format which you have posted is correct.

Wouldnt the 26 spaces throw off the explode command though?

 

[krazyjosh5]

This is a good idea, however I tend to use address and address_more rather than address_1 and address_2.

Well my DB is setup so that each entry has an 'address' field which is text and lists the address but its formatted real retarded looking in HTML. Thats not a problem since each entry has a 'city id' which matches up in another table to a city name and then in that table that matches to a state name in another table. So the only piece missing is the zip code. Id basically like to trim down the address field and extract the zip code from it to a new column. Then id have address line 1, zip in one table. city in another. state in another.

 

[krazyjosh5]

Got it. Here was the code for whos interested:

<?php
include 'include.php';

$query = mysql_query("SELECT address FROM shops");

while($array=mysql_fetch_array($query)){
$boom = explode("<br />", $array["address"]);

$trimzero = trim($boom[0]);
$trimtree = trim($boom[3]);

$fixone = mysql_query("UPDATE shops SET address='$trimzero' WHERE address='$array[address]'");
$fixtwo = mysql_query("UPDATE shops SET zip='$trimtree' WHERE address='$trimzero'");
}
?>