alturic

Whoa, you just blew my mind....... :-/

I have an array like so: array ( 0 => array ( 'zipcode' => '18640', 'latitude' => '41.31390', 'longitude' => '-75.77767', 'state' => 'Pennsylvania', 'county' => 'Luzerne', 'city' => 'Pittston', ), 1 => array ( 'zipcode' => '18515', 'latitude' => '41.44901', 'longitude' => '-75.66408', 'state' => 'Pennsylvania', 'county' => 'Lackawanna', 'city' => 'Scranton', ), 2 => array ( 'zipcode' => '18704', 'latitude' => '41.27534', 'longitude' => '-75.89115', 'state' => 'Pennsylvania', 'county' => 'Luzerne', 'city' => 'Kingston', ), 3 => array ( 'zipcode' => '90210', 'latitude' => '34.08476', 'longitude' => '-118.40629', 'state' => 'California', 'county' => 'Los Angeles', 'city' => 'Beverly Hills', ), 4 => array ( 'zipcode' => '95456', 'latitude' => '39.26767', 'longitude' => '-123.75502', 'state' => 'California', 'county' => 'Mendocino', 'city' => 'Little River', ), 5 => array ( 'zipcode' => '98499', 'latitude' => '47.16618', 'longitude' => '-122.50846', 'state' => 'Washington', 'county' => 'Pierce', 'city' => 'Lakewood', ), ) and I'm looking to use it to populate a drop-down like so: Pennsylvania (state) -Luzerne (county) --18640 --18702 -Lackawanna (county) --18515 California -Mendocino --95456 -Pierce --98499 So, I thought if this was accomplished with nested foreach's I'd do something like: echo '<select>'; foreach ($zipcode as $singlestate) { echo '<option id='.$singlestate.'>'; foreach ($singlestate as $singlecountinsinglestate) { echo '<option id='.$singlecountyinsinglestate.'>'; foreach ($singlecountyinsinglestate as $individualzipcodes) { echo '<option id='.$individualzipcodes.'>'; } } } echo '</select'; Which would end up looking like so: <select> <option id="California">California</option> <option id="-Mendocino">-Mendocino</option> <option id="--95456">--95456</option> <option id="Pennsylvania">Pennsylvania</option> <option id="-Luzerne">-Luzerne</option> <option id="--18640">--18640</option> <option id="--18702">--18702</option> <option id="-Lackawanna">-Lackawanna</option> <option id="--18515">--18515</option> </select> Of course, that entire idea wouldn't work if, in order to group the original array by State=>County=>ZIP, wasn't done using nested foreach's though... and frankly I can group by state, I can group by county but I get lost when I need to group by State=>County=>ZIP

I've progressed this to a working state with your logic (thanks, seriously!), but there will be duplicate keys, every day? Every day the forecast updates, it's updating the duplicate keys, and it's inserting if the key (zipcode) doesn't exist.

If I wanted to do an INSERT ... ON DUPLICATE KEY UPDATE, It'd be easier (for sure) to have the key be the zipcode, and then put each "10 rows" as a single row, correct? I've never dealt with rows that were 50+ columns in length, since each day is going to have ~10 columns of data for it... is that a bad idea to have a row so long?

The zipcodes that would be forecasted for would be supplied by a user selecting them on a leaflet map. I think Barand's solution will actually work nicely. I just need to think of the implementation now (unless you have another suggestion) in terms of not having "stale" forecasts sitting in a table never being used again, possibly putting a timestamp on them and running a cron query everyday to delete any older than 1 day, because again there's no real way to do "regions" or "zones" and that, ultimately, is one of the problems I'm facing. Today it was gorgeous out in most areas (Northeast US), so today is a perfect example of how all zipcodes in ~10-20 mile radius (a lot of zipcodes heh) would all be the same forecast. On days where there's "active" weather, only zipcodes in a ~5 mile radius might have the same forecast. In the winter time, a zipcode on the bottom of a mountain would be totally different than a zipcode 1 mile up the mountain, but again on a clear summer day they would be the same. Does that make any more sense?

Actually, just spoke to the meteorologist and even that wouldn't work. Having to keep track of what forecast id you use "Sunny, 96 degrees, AM Clouds followed by a sunny afternoon, 76 dewpoint" for and what forecast id you use "Major Snowstorm", etc would be difficult, again this is on a conus level so forecast areas would add a UX nightmare to it. It almost sounds like (from everyone I've asked) this is getting into the realm of a massively complex project and it'd be better off just writing an actual program to do this instead of using php/mysql/js for this type of project. I like that mock-up, but in that case, what exactly is the forecast area for? Is it (seemingly at least) logical to have 1 column that says "AM Sun with Increasing Clouds" that the affected zipcodes pull from instead of 8 zipcodes all containing a column that says "AM Sun with Increasing Clouds"?

I like this idea somewhat, but I'm confused on the implementation of it. So I'd have a table "forecast_areas" which would contain the actual forecast columns and I'd use a form of course to add that info to the database. I'd also have a table and use a form, that just had 2 columns, zipcode and area_id. I'd have 1 form for updating the forecast_areas and then I'd have another page (with a form) that would list the zipcodes and what area_id I'd want to put them in for that day?

Sorry for replying again, seems you can't edit posts on here. What I mean by that above is that, on a clear day, zipcodes in a ~10-20 mile radius could all receive the same weather. On the other hand, especially when it comes to winter weather or severe weather, there could be a drastic difference in weather from a zip code at the bottom of a mountain to one further up the mountain.

Because all the zip codes won't always be the same. Most of the time, yes. Not always.

I have a 10 row form (attached, which each column is respective to the column names and values in a MySQL table) and I'm wondering if it's bad practice, in general, or if it's even possible to take those "synopsis" (and others, like temp_f when the time comes) fields from the form and use them to update other zipcodes to be the same values. So let's say we had 10 zip codes, 18640, 18641, 18642 ... 18649. I'm looking to make all 10 of those zipcodes (which would also have these 10 rows in the database, the only things that would be different is the auto-inc id column and zipcode column) match the same synopsis and temp_f as this form. So I was going to try going the route of submitting this form to a page, which would then unserialize the _POSTed array and iterate over it making the INSERT ... ON DUPLICATE KEY UPDATE. I've talked to a few people in various irc channels and the responses range from 'updating that much data is one go is the issue' to 'it sounds like what you're trying to do is a UX nightmare' to 'you should try making a temp table or non-mysql message queue'. While I do think updating this much data in one go would give me problems, I'm unsure how it's a UX nightmare. A UX nightmare would be having 10 rows, times 10 zip codes an having to update each one individually, when at the end of the day synopsis, temp_f and other fields are going to be identical to the fields in this screenshot. Where I stand currently is... not too far into this, I'm pulling all the zipcodes I need to update into an array and array_slice'ing that down to these 10 rows (again, each zipcode contains 10 rows in the database) the only two things I believe I need to figure out how to do it the way I'm looking for is a.) taking the forms "submitted" fields, and pusing the "synopsis" and "temp_f" data from the 10 submitted rows to the other zipcodes in the array and then b.) figuring out how I would iterate over THAT _POSTed array and doing the insert statement. I actually don't think that's possible witout 3 pageloads. The original form page, the _POSTed serialized array which would then need the affected zipcodes to know what to push into them and then another serialized _POSTed array to do the actual foreach looping of the insert ... on dup key update, no? So ultimately, is what I'm looking to do (hopefully my second paragraph got the idea across correctly) "bad practice" or "a nightmare" from a coding perspective? I can get behind the latter definitely, but the former I'm unsure of. Keep in mind that this could potentially be updating 10 rows X 50-100 zipcodes at one time... so I'm really looking for advice/possible solutions. http://imgur.com/a/R5g1Z