Databases
StackOverflow2013
Postsdbo
POPHP/MySQL only update input fields that have been changed
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP/MySQL only update input fields that have been changed
    text
    copied!<p>Creating a little admin row updater that I can't figure out in PHP server-side. I want the sql query to just update $_POST fields which have been altered on the page, that way it won't cycle all 100 or so records. </p> <p>Here is my approach:</p> <p>1) jQuery listen to changed input fields, then assign name attribute user-field-updated </p> <p>2) PHP detects user-field-updated is set. </p> <p>3) On submit reload, PHP detects this input, updates to SQL by row ID. How do I make sure PHP knows which input field goes to which row ID?</p> <p>jQuery client-side update name attribute:</p> <pre><code> $(".listen").keyup(function() { $(this).attr("name", "user-field-updated"); }) </code></pre> <p>Generate HTML admin table that is like a simple version of phpmyadmin: </p> <pre><code>&lt;form action="post"&gt; &lt;?php while($rows=mysql_fetch_array($result)) : ?&gt; &lt;tr&gt; &lt;td&gt;The editable field: &lt;input class="listen" type="text" name="user-field" size="4" value="&lt;?php echo $rows['Users'] ?&gt;" /&gt; &lt;input type="hidden" name="ID" value="&lt;?php echo $rows['ID']; ?&gt;" /&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php endwhile; ?&gt; &lt;input type="submit" name="submit-button" value="Click to update only fields I changed" /&gt; &lt;/form&gt; </code></pre> <p>At the bottom of my page is php script which will trigger on reload:</p> <pre><code>if(isset($_POST['submit-button'])) { foreach($_POST as $name =&gt; $value) { if(isset($_POST['user-count-updated'])){ $ID = $_POST['ID']; $sql = "UPDATE $tbl_name SET $name = $value WHERE id = '$ID'"; $q-&gt;execute($db-&gt;prepare($sql)); } } }; </code></pre> <p>A question that could be asked is: In PHP, how do I "group" related values in a form submit that will update different rows in a database? How can I make PHP know which row I want to update with the field input.</p> <hr> <h2>Solution created based on jacouh</h2> <p>jQuery changes input name attribute of text field AND inserts a new hidden input to trigger the isset variable below..</p> <pre><code> $(".trigger-changed").keyup(function() { $(this).addClass("changed"); $(this).attr("name", $(this).data("name")).off("keyup"); $("#mainform").append('&lt;input type="hidden" name="user-updated"&gt;&lt;/input&gt;') }) </code></pre> <p>html</p> <pre><code>&lt;td&gt;&lt;input type="text" size="4" name="nochange" data-name="userfield@&lt;?php echo $rows['ID'] ?&gt;" value="&lt;?php echo $rows['Users'] ?&gt;" class="trigger-changed"&gt;&lt;/input&gt;&lt;/td&gt; </code></pre> <p>php detect an input has changed (just a general global trigger, not specific to the field itself). Then go ahead and parse out the name value. If successful reload. </p> <pre><code>if(isset($_POST['user-updated'])) { foreach($_POST as $key =&gt; $value) { if($name != 'nochange'){ $name = strtok($key, '@'); $ID = strtok("\n"); $sql = "UPDATE $tbl_name SET Users = $value WHERE ID = '$ID'"; $result = mysql_query($sql); if($result){ echo "&lt;meta http-equiv=\"refresh\" content=\"0;URL=index.php\"&gt;"; } } } } </code></pre> <p>I also consulted a friend who suggested a myriad of ways from constructing a new array to printing out hidden input tags into the html itself. I think this solution is most compact for a simple change.</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