Page 1 of 1

SQL/PHP: Using a 10 row form to update 10 rows WHERE a colum

Posted: Sat Aug 20, 2016 7:44 am
by 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.

Image

Re: SQL/PHP: Using a 10 row form to update 10 rows WHERE a c

Posted: Sun Aug 21, 2016 3:09 pm
by Christopher
I read this post several times, but I'm still not sure exactly what you are trying to do. Let me see if I can summarize:

1. You have a database with, for example, 100 rows. There are 10 zip codes that each has 10 rows.

2. You want to read in the 10 rows for one zip code and show it in a form where the user can set the value of the synopsis and temp_f fields. That sounds like:

Code: Select all

SELECT * FROM mytable ORDER BY zipcode LIMIT 10;
3. When the form is submitted, you want to update the all the records for all zip codes with the values for each day. That sounds like 10 update statements:

Code: Select all

UPDATE mytable SET synopsis='{$form[1]['synopsis']}', temp_f='{$form[1]['temp_f']}' WHERE day_number='1';
UPDATE mytable SET synopsis='{$form[2]['synopsis']}', temp_f='{$form[2]['temp_f']}' WHERE day_number='2';
...