Jump to content

Using php to modify a table


merylvingien

Recommended Posts

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

@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

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

@ 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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.