StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POChange Static SQL update to dynamic based on changing column names
text
Body
copied!<p>Ok, I asked a question last night and received a number of really great responses. Since that was my first time using StackOverflow I was really pleased.</p> <p>I'm hoping you guys can help with a new one. Hopefully, down the road, I'll be able to repay the favor to some NEW newbies.</p> <p>I have the following code in a php file:</p> <pre><code> $sql = ""; $now=date("Y-m-d h:i:s"); $updatedRecords = $json1->{'updatedRecords'}; foreach ($updatedRecords as $value){ $sql = "update `acea` set ". "`ACEA_A1`='".$value->ACEA_A1 . "', ". "`ACEA_A2`='".$value->ACEA_A2 . "', ". "`ACEA_A3`='".$value->ACEA_A3 . "', ". "`ACEA_A4`='".$value->ACEA_A4 . "', ". "`ACEA_A5`='".$value->ACEA_A5 . "', ". "`ACEA_B1`='".$value->ACEA_B1 . "', ". "`ACEA_B2`='".$value->ACEA_B2 . "', ". "`ACEA_B3`='".$value->ACEA_B3 . "', ". "`ACEA_B4`='".$value->ACEA_B4 . "', ". "`ACEA_B5`='".$value->ACEA_B5 . "', ". "`ACEA_E1`='".$value->ACEA_E1 . "', ". "`ACEA_E2`='".$value->ACEA_E2 . "', ". "`ACEA_E3`='".$value->ACEA_E3 . "', ". "`ACEA_E4`='".$value->ACEA_E4 . "', ". "`ACEA_E5`='".$value->ACEA_E5 . "', ". "`ACEA_E7`='".$value->ACEA_E7 . "' ". "where `acea_id`=".$value->acea_id; if(mysql_query($sql)==FALSE){ $errors .= mysql_error(); } } </code></pre> <p>The "ACEA_XX" portions relate to columns in the "acea" database table (obviously), but the programmer set them statically. Unfortunately, these columns need to be added to periodically, with new columns being created related to the new ACEA specs that are introduced.</p> <p>As a result, this code becomes outdated.</p> <p>Without having to go in and update this code each time I add a new column, how can I redesign this code so that it updates itself dynamically to include the new columns? I've been trying all morning to make it work, and I can set it so that I can dynamically insert the actual column names into the update statement, but, I can't seem to dynamically grab the associated values dynamically (and I think my method for grabbing and inserting the column names is a little convoluted).</p> <p>My actual database table columns are currently:</p> <p>acea_id ACEA_A1 ACEA_A2 ACEA_A3 ACEA_A4 ACEA_A5 ACEA_B1 ACEA_B2 ACEA_B3 ACEA_B4 ACEA_B5 ACEA_E1 ACEA_E2 ACEA_E3 ACEA_E4 ACEA_E5 ACEA_E6 ACEA_E7 ACEA_E9 oil_data_id</p> <p>The first and last columns will never change and will continue to be the first and last columns. Any new columns will be added somewhere in between, but not necessarily immediately preceding the "oil_data_id" column.</p> <p>I've tried revising the code numerous ways in order to properly grab the values, but just can't make it work.</p> <p>Anyone have a concise modification to the code to do what I want? It would be greatly appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload