Databases
StackOverflow2013
Postsdbo
POmultiple update array query gone wrong :(
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmultiple update array query gone wrong :(
    text
    copied!<p>hey guys im having trouble with an array. all i need is for the query to update 2 columns on the table based on the records id </p> <p>The table has a column called OffID and it looks for any record that has not yet been authorised. to mark it authorised the user will select a check box for either authorise or deny and then press the submit button. however at the moment i have 9 records which show in the table each with there own unique id but when submit is selected it will only update one. any help in showing where ive gone wrong would be grateful and cheers in advance :)</p> <p>ok i edited the code but it is only now setting the first record. so if there are 8 records and i choose accept for the first record and deny for the second record both the accept and deny are set to the first record in the database my new code is below</p> <pre><code> &lt;?php $path = $_SERVER['DOCUMENT_ROOT']; $path .= "/Apollo/dbc.php"; include_once($path); $rs_results = mysql_query("SELECT * FROM off WHERE IsItAuthorised='0' and isitsick='0' ORDER BY DayOff"); ?&gt; &lt;html&gt; &lt;head&gt; &lt;title&gt;Administration Main Page&lt;/title&gt; &lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt; &lt;?php if (isset($_POST['submit'])) { //Assign each array to a variable $id = $_POST['id']; $approve = $_POST['approve']; $deny = $_POST['deny']; $limit = count($rs_results); $values = array(); // initialize an empty array to hold the values for($k=0;$k&lt;$limit;$k++){ $msg[] = "$limit New KPI's Added"; $query = "UPDATE off SET Authorised = '$approve[$k]', Deny = '$deny[$k]' WHERE OffID = '$id[$k]'"; } $Event = "INSERT INTO events (UserName, Event ) VALUES ('$_SESSION[user_name]', 'Entered New KPI' )"; echo $query; if (!mysql_query($query,$link)){ die('Error: ' . mysql_error()); } else { mysql_query($Event); echo "&lt;div class=\"msg\"&gt;" . $msg[0] . "&lt;/div&gt;"; } } ?&gt; &lt;/head&gt; &lt;body&gt; &lt;table width="100%" border="0" cellspacing="0" cellpadding="0"&gt; &lt;tr&gt; &lt;td width="14%" valign="top"&gt;&lt;?php ?&gt; &lt;/td&gt; &lt;td width="74%" valign="top" style="padding: 10px;"&gt; &lt;p&gt;&lt;?php if(!empty($msg)) { echo $msg[0]; } ?&gt;&lt;/p&gt; &lt;p&gt; &lt;?php $cond = ''; $sql = "select * from off "; $rs_total = mysql_query($sql) or die(mysql_error()); $total = mysql_num_rows($rs_total); ?&gt; &lt;p&gt; &lt;form name "searchform" action="/Apollo/Admin/HolidayRequests.php" method="post"&gt; &lt;table width="100%" border="0" align="center" cellpadding="2" cellspacing="0"&gt; &lt;tr class="mytables"&gt; &lt;td width="4%"&gt;&lt;font color="white"&gt;&lt;strong&gt;ID&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt; &lt;td width="4%"&gt; &lt;font color="white"&gt;&lt;strong&gt;Staff Member&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt; &lt;td width="10%"&gt;&lt;font color="white"&gt;&lt;strong&gt;Day Off&lt;/font&gt;&lt;/strong&gt;&lt;/div&gt;&lt;/td&gt; &lt;td width="10%"&gt;&lt;font color="white"&gt;&lt;strong&gt;Is It Authorized&lt;/font&gt;&lt;/strong&gt;&lt;/div&gt;&lt;/td&gt; &lt;td width="15%"&gt;&amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&amp;nbsp;&lt;/td&gt; &lt;td width="10%"&gt;&amp;nbsp;&lt;/td&gt; &lt;td width="17%"&gt;&lt;div align="center"&gt;&lt;/div&gt;&lt;/td&gt; &lt;td&gt;&amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;?php while ($rrows = mysql_fetch_array($rs_results)) {?&gt; &lt;tr&gt; &lt;td&gt;&lt;input name="id[]" id="id[]" size="4" value="&lt;?php echo $rrows['OffID'];?&gt;" /&gt;&lt;/td&gt; &lt;td&gt;&lt;?php echo $rrows['StaffMember']; ?&gt;&lt;/td&gt; &lt;td&gt;&lt;?php echo date('d/m/Y', strtotime($rrows['DayOff']));?&gt;&lt;/div&gt;&lt;/td&gt; &lt;td&gt; &lt;span id="approve&lt;?php echo $rrows['id']; ?&gt;"&gt; &lt;?php if(!$rrows['IsItAuthorised']) { echo "Pending"; } else {echo "Authorized"; }?&gt; &lt;/span&gt; &lt;/td&gt; &lt;td&gt; &lt;input type="checkbox" name="approve[]" id="approve[]" value="1"&gt; Approve &lt;input type="checkbox" name="deny[]" id="deny[]" value="1"&gt; Deny &lt;/td&gt; &lt;/tr&gt; &lt;?php } ?&gt; &lt;/table&gt; &lt;input name="submit" type="submit" id="submit" value="Submit"&gt; &lt;/form&gt; &amp;nbsp;&lt;/p&gt; &lt;?php ?&gt; &lt;p&gt;&amp;nbsp;&lt;/p&gt; &lt;p&gt;&amp;nbsp;&lt;/p&gt; &lt;p&gt;&amp;nbsp;&lt;/p&gt; &lt;p&gt;&amp;nbsp;&lt;/p&gt;&lt;/td&gt; &lt;td width="12%"&gt;&amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/body&gt; &lt;/html&gt; </code></pre>
 

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