SQL/PHP: Using a 10 row form to update 10 rows WHERE a column =...

[alturic]

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

[Jacques1]

[OP has cross-posted the thread on CodingForums]

 

So the data is always the same for those ZIP codes? Then why do you even store it for each individual ZIP code? If you can only cover bigger areas, you should associate the data with those bigger areas.

[alturic]

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

[alturic]

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.

[Barand]

Create a table of areas and another for zipcodes. In the zipcode table, include the area_id to group the zips into the areas.

 

Create a forecast for the area. Select which zipcodes that forecast applies to and save. If it isn't all zips in the area then create another forecast and select zips. Repeat as necessary.

+-------------+                                           +-------------+
|   area      |                                           |   zipcode   |
+-------------+                                           +-------------+
|  area_id    |------------------------+                  |   zip       |
|  name       |                        +-----------------<|   area_id   |
+-------------+                                           +-------------+
       |                                                         |
       |                                                         |
       |                                                         |
       |    +-------------+           +---------------+          |
       |    |  forecast   |           |  applies_to   |          |
       |    +-------------+           +---------------+          |
       |    | forecast_id |----------<|  forecast_id  |          |
       +---<| area_id     |           |  zip          |>---------+
            | date        |           +---------------+
            | text        |
            +-------------+

Edited August 20, 2016 by Barand

[alturic]

 

Create a table of areas and another for zipcodes. In the zipcode table, include the area_id to group the zips into the areas.

 

Create a forecast for the area. Select which zipcodes that forecast applies to and save. If it isn't all zips in the area then create another forecast and select zips. Repeat as necessary.

 

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? 

[Barand]

I'd have something like this

[alturic]

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"?

Edited August 20, 2016 by alturic

[Jacques1]

Your project is not “massively complex”. It's just very vague. You've talked a lot about how you think the technical implementation should look like (most of which doesn't really make sense to me), but the workflow doesn't even seem to be clear at this point.

 

If you don't want to have areas which cover multiple ZIP codes, where do the ZIP codes come from? Is the user supposed to enter a list of (arbitrary?) codes whenever the data is the same? That's doable, sure.

[alturic]

Your project is not “massively complex”. It's just very vague. You've talked a lot about how you think the technical implementation should look like (most of which doesn't really make sense to me), but the workflow doesn't even seem to be clear at this point.

 

If you don't want to have areas which cover multiple ZIP codes, where do the ZIP codes come from? Is the user supposed to enter a list of (arbitrary?) codes whenever the data is the same? That's doable, sure.

 

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?

[Jacques1]

Does that make any more sense?

 

Yes. If the user can easily select many ZIP codes, and if the code groups constantly change, then I would go back to your initial approach and simply repeat the data.

 

Sure, an area would save a few rows, but it's also more complicated and less flexible. Let's say many ZIP codes have almost the same data. The easiest and most intuitive approach would be to first duplicate a set of default values and then adjust individual ZIP codes. This is somewhat difficult to implement with areas.

 

When you have a set of ZIP codes and the corresponding data, you can insert everything with a nested loop (pseuco-code):

insert_stmt = db.prepare('INSERT INTO forecasts (zip_code, ...) VALUES (?, ...)')

each zip_code in zip_codes:
  each weather_of_interval in weather:
    insert_stmt.execute([zip_code, ...])

zip_codes are the codes which are eather selected on the same page or have been submitted on a previous page. weather contains the 10 data rows of your initial GUI.

Edited August 21, 2016 by Jacques1

[alturic]

When you have a set of ZIP codes and the corresponding data, you can insert everything with a nested loop (pseuco-code):

insert_stmt = db.prepare('INSERT INTO forecasts (zip_code, ...) VALUES (?, ...)')

each zip_code in zip_codes:
  each weather_of_interval in weather:
    insert_stmt.execute([zip_code, ...])

zip_codes are the codes which are eather selected on the same page or have been submitted on a previous page. weather contains the 10 data rows of your initial GUI.

 

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?

[Jacques1]

The ZIP code and the time interval constitute the primary key. Each time interval has its own row (hence the nested loop).

 

Then you also don't need an ON DUPLICATE KEY UPDATE clause, because there won't be duplicate keys.

[alturic]

The ZIP code and the time interval constitute the primary key. Each time interval has its own row (hence the nested loop).

 

Then you also don't need an ON DUPLICATE KEY UPDATE clause, because there won't be duplicate keys.

 

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.

[Jacques1]

Don't overwrite the data, just add new data per date. Keeping a few MB of extra information doesn't cost you anything and can later turn out to be useful. Many sites show the weather of previous dates and provide statistics.

 

So a small correction: The records are identified by the ZIP code, the date and the time interval. Split this into separate tables and use numeric IDs.