Using php to modify a table

[merylvingien]

Hi Folks, i am a bit stuck on how to proceed with the following and could use some input.

 

I have a table with a whole bunch of zipcodes and places. A lot of the zipcodes are repeated and i would like to merge them e.g.

 

7 | 07677 | WOODCLIFF LAKE | New Jersey | 41.02 | -74

8 | 07677 | WESTWOOD | New Jersey | 41.02 | -74

9 | 07677 | WOODCLIFF LK | New Jersey | 41.02 | -74

 

What i would like to do is merge these into one line then delete the obsolete ones e.g.

 

7  | 07677 | WOODCLIFF LAKE | WESTWOOD, WOODCLIFF LK |  New Jersey | 41.02 | -74

 

Obviously not all the zipcodes are duplicated, and with 70 odd thousand lines its a bit daunting to go through them manually.

Any pointers would be useful.

 

[Mahngiel]

If i understand correctly and In my opinion, what you're asking to do is silly because you're wanting to dynamically add rows to a table, and that can and will get out of hand quickly. 

 

Now, understandably, there can be many variations of spelling and multiple cities sharing the same zip code.  My personal approach would be to change the row type for 'city' in your table to text type.  Then have the form submission script check for the existence of the zip code submitted, if it exists, you could return the value of the 'city' row and append the new submission to the cell.  You could avoid redundant city inputs by exploding the cell content and conditionally checking for the existence of said city before appending.

[merylvingien]

i need the zipcode to be unique as the rest of the site depends on this.

 

sorry i should have made this clearer.

I have started to try and code this, but it aint working yet 

[Mahngiel]

in my suggestion, the zip code does remain unique.  Let me try to flow-chart this for you:

 

User submits form

->Script checks if submitted zip exists in table

->-> If zip does not exists in table

->->-> Insert zip and city

->-> If zip exists in table

->->-> Append city name to existing 'city' (as it was inserted initially when the zip did not exist)

 

You now have a table with this format

id | zip | cities | state | row | row

1 | 07677 |woodcliff lake, westwood, woodcliff lk | new jersey | 41.02 | -74

 

[cpd]

You have dupe data because your database is not normalized. If you want to avoid repeating the same data over and over you should normalize your database else you may as well just have a flat file database instead of a relational database.

[redsmurph]

I assume you already have the data in a database table.

 

I'd keep it the way you have it: one row per city.

 

First thing I would do would be to index the ZIP row for increased SELECT performance.

 

Then I would run a SELECT on the ZIP code and get all the rows back for that ZIP code and merge the city names in real time, if now you want to show the city names at all.

 

If you really want to merge I'd advise you to read from one table (as per above), merge in PHP and then write to another table. Otherwise you are in for a disaster.

 

Cheers,

Anders

[Mahngiel]

@redsmurph

 

OP wants a single table row per zip code, not per city.  Therefore your "merge cities and zips in realtime" would no be effective, whereas it would be quite simple to maintain a text type cell for `cities` and create an explode-able array for the values to check against.

 

ie

id | zip | cities | state | row | row

1 | 07677 |woodcliff lake, westwood, woodcliff lk | new jersey | 41.02 | -74

 

// explode array to check for city exists
$cities = explode(', ', $row[cities]);
// if not, add value
( !in_array($_POST['city'], $cities) ? array_push($cities, $_POST['city']) : '' );
// implode before resending
$cities = implode(', ' $cities);

// update db

[redsmurph]

@Mahngiel: That was what my advice was about: read rows with the same ZIP code, merge ZIP lines to one (where all cities are put in one column as a JSON array or whatever), store merged row in a new table. Rinse and repeat.

[merylvingien]

The reason why i need to have just one zipcode is because folk will be purchasing a subscription to each zipcode. Each subscriber can have x amount of zipcodes so will not want to purchase the same ones twice. It also makes it much easier to manage thier accounts. Well for me it does anyway.

 

As far as the site goes, it makes little odds if the towns with duplicate zipcodes are placed into another column which can then be displayed as and when is needed.

 

The site at the moment runs a database with postcodes rather than zipcodes, the main town is listed and other less important areas are listed as secondary areas.

 

I hope this helps explain why i need just one zipcode per row and no duplicates.

 

 

[Mahngiel]

@ redsmurph: My lack of coffee this morning hid the true question.  Yes, I agree merylvingien will best be suited to run a select and newly formatted insert query.  The largest issue in this is how to identify the "major" city from the from table and place it appropriately in the to table.  There would need to be some sort of array to check values against to determine this.