Update row after mysql query

Karchion · August 1, 2011, 3:57pm

So I have this query that pulls specific information and places it in a table . I want to be able to have a check box that says “completed” when that check box has been checked it will submit saying it has been completed.

My Query code is



<html>
<body onLoad='initTable("table1");'>
<script src='/req/scripts/sortTable.js' type='text/javascript'></script>
<table ID="table1" border="2" cellspacing="1">
<thead class=fixedheader>
	  <td align=left valign=top>&nbsp;Location</td>
	  <td align=left valign=top>&nbsp;Date&nbsp;Of&nbsp;Exam</td>
	  <td align=left valign=top>&nbsp;Patient&nbsp;Name</td>
	  <td align=left valign=top>&nbsp;Date&nbsp;of&nbsp;Birth</td>
	  <td align=left valign=top>&nbsp;Exam&nbsp;Type</td>
	  <td align=left valign=top>&nbsp;#&nbsp;of&nbsp;Views</td>
	  <td align=left valign=top>&nbsp;History</td>
	  <td align=left valign=top>&nbsp;Notes</td>
	  <td align=left valign=top>&nbsp;Ordering&nbsp;Physician</td>
	  <td align=left valign=top>&nbsp;Contact&nbsp;Phone</td>
	  <td align=left valign=top>&nbsp;Stat&nbsp;Read</td>
	  <td align=left valign=top>&nbsp;Priors</td>
	  </thead> 
<?php

 mysql_connect("localhost","","") or die(mysql_error()); 
 mysql_select_db("RadReq") or die(mysql_error()); 
 
 
$sql = "Select Location, dateofexam, Pfirstname, Plastname, dob, examtype, views, history, notes, orderingphysician, contactphone, statread, priors From formdata WHERE rcompleted =''";
$result = MYSQL_QUERY($sql);

while ($row=mysql_fetch_array($result))

if ($row[statread] == 'yes'){echo "<tr><td bgcolor='red'>$row[Location]</td><td bgcolor='red'>$row[dateofexam]</td><td bgcolor='red'>$row[Pfirstname]&nbsp;$row[Plastname]</td><td bgcolor='red'>$row[dob]</td><td bgcolor='red'>$row[examtype]</td><td bgcolor='red'>$row[views]</td><td bgcolor='red'>$row[history]</td><td bgcolor='red'>$row[notes]</td><td bgcolor='red'>$row[orderingphysician]</td><td bgcolor='red'>$row[contactphone]</td><td bgcolor='red'>$row[statread]<td bgcolor='red'>$row[priors]</td></td></tr>";}
elseif($row[statread] == ''){echo "<tr><td>$row[Location]</td><td>$row[dateofexam]</td><td>$row[Pfirstname]&nbsp;$row[Plastname]</td><td>$row[dob]</td><td>$row[examtype]</td><td>$row[views]</td><td>$row[history]</td><td>$row[notes]</td><td>$row[orderingphysician]</td><td>$row[contactphone]</td><td>$row[statread]<td>$row[priors]</td></td></tr>";}


?>
</table>
</body>
</html>

cholmon · August 1, 2011, 6:34pm

You’ll want to:

wrap that whole <table> in a <form> that posts to another script that does the actual update, e.g., completed.php
make sure that your SELECT pulls each row’s primary key… assuming your “formdata” database table has a primary key, e.g., exam_id.
add a checkbox input into each table row that specifies each row’s primary key, like <input type="checkbox" name="exam_ids[]" value="$row['exam_id']" />
your form will end up submitting an array of IDs, $exam_ids, to be marked as “completed”. You’ll want to implode that array to a string of ints, then run a query similar to this:


// make sure all your IDs are integers...assuming you're using integers as your table's PK
$ids = implode(',', array_map('intval', $_POST['exam_ids']));

// update all the checked exams
$sql = "UPDATE formdata SET rcompleted='y' where exam_id IN ($ids)";

mysql_query($sql);

On a side note, instead of setting the bgcolor of each TD, just set the bgcolor of the TR, and just print all the fields once, e.g.:


while ($row=mysql_fetch_array($result)) {
    $bgcolor = $row['statread'] == 'yes' ? "red": "";

    echo "<tr bgcolor='$bgcolor'> ...(all your tds without their own bgcolors)... </tr>";
}

Karchion · August 1, 2011, 8:18pm

So I was able to get the query/form to have the checkbox, and I setup the form to submit to rviewsubmit.php but now I’m getting this error


Warning: array_map() [function.array-map]: Argument #2 should be an array in /var/www/helpdesk/req/rviewsubmit.php on line 9

Warning: implode() [function.implode]: Bad arguments. in /var/www/helpdesk/req/rviewsubmit.php on line 9

here is the form


<html>
<form id="rview" name="rview" method="post" action="rviewsubmit.php">
<body onLoad='initTable("table1");'>
<script src='/req/scripts/sortTable.js' type='text/javascript'></script>
<table ID="table1" border="2" cellspacing="1">
<thead class=fixedheader>
	  <td align=left valign=top>&nbsp;Form&nbsp;ID</td>
	  <td align=left valign=top>&nbsp;Location</td>
	  <td align=left valign=top>&nbsp;Date&nbsp;Of&nbsp;Exam</td>
	  <td align=left valign=top>&nbsp;Patient&nbsp;Name</td>
	  <td align=left valign=top>&nbsp;Date&nbsp;of&nbsp;Birth</td>
	  <td align=left valign=top>&nbsp;Exam&nbsp;Type</td>
	  <td align=left valign=top>&nbsp;#&nbsp;of&nbsp;Views</td>
	  <td align=left valign=top>&nbsp;History</td>
	  <td align=left valign=top>&nbsp;Notes</td>
	  <td align=left valign=top>&nbsp;Ordering&nbsp;Physician</td>
	  <td align=left valign=top>&nbsp;Contact&nbsp;Phone</td>
	  <td align=left valign=top>&nbsp;Stat&nbsp;Read</td>
	  <td align=left valign=top>&nbsp;Priors</td>
	  <td align=left valign=top>&nbsp;Completed</td>
	  </thead> 
<?php

 mysql_connect("localhost","root","@uia123;") or die(mysql_error()); 
 mysql_select_db("RadReq") or die(mysql_error()); 
 
 
$sql = "Select form_id,Location, dateofexam, Pfirstname, Plastname, dob, examtype, views, history, notes, orderingphysician, contactphone, statread, priors From formdata WHERE rcompleted =''";
$result = MYSQL_QUERY($sql);


while ($row=mysql_fetch_array($result))

if ($row[statread] == 'yes'){echo "<tr><td bgcolor='red'>$row[form_id]</td><td bgcolor='red'>$row[Location]</td><td bgcolor='red'>$row[dateofexam]</td><td bgcolor='red'>$row[Pfirstname]&nbsp;$row[Plastname]</td><td bgcolor='red'>$row[dob]</td><td bgcolor='red'>$row[examtype]</td><td bgcolor='red'>$row[views]</td><td bgcolor='red'>$row[history]</td><td bgcolor='red'>$row[notes]</td><td bgcolor='red'>$row[orderingphysician]</td><td bgcolor='red'>$row[contactphone]</td><td bgcolor='red'>$row[statread]<td bgcolor='red'>$row[priors]</td><td bgcolor='red'><center><input type='checkbox' name='form_id[]' value='$row[form_id]'></center></td></td></tr>";} 
elseif($row[statread] == ''){echo "<tr><td>$row[form_id]</td><td>$row[Location]</td><td>$row[dateofexam]</td><td>$row[Pfirstname]&nbsp;$row[Plastname]</td><td>$row[dob]</td><td>$row[examtype]</td><td>$row[views]</td><td>$row[history]</td><td>$row[notes]</td><td>$row[orderingphysician]</td><td>$row[contactphone]</td><td>$row[statread]<td>$row[priors]</td><td><center><input type='checkbox' name='form_id[]' value='$row[form_id]'></center></td></td></tr>";}


?>
</table>
</body>
<input type="submit" id="wsbfrmbtn1" name="Submit" value="Submit" />
<input type="reset" id="wsbfrmbtn2" name="Cancel" value="Reset" />
</form>
</html>

and here is the script to insert the completed into the database


</html>

<?php

 mysql_connect("localhost","root","@uia123;") or die(mysql_error()); 
 mysql_select_db("RadReq") or die(mysql_error()); 
 
// make sure all your IDs are integers...assuming you're using integers as your table's PK
$ids = implode(',', array_map('intval', $_POST['form_id[]']));

// update all the checked exams
$sql = "UPDATE formdata SET rcompleted='y' where form_id IN ($ids)";

mysql_query($sql);

?>

<head>
<meta http-equiv="refresh" content="5; URL=rview.php">
</head>
</html>

Karchion · August 2, 2011, 4:41pm

I ran a vardump after checking the box of form_id 1

 array(2) { ["form_id"]=> array(1) { [0]=> string(1) "1" } ["Submit"]=> string(6) "Submit" }

Here is the vardump with the box of form_id 2 checked

 array(2) { ["form_id"]=> array(1) { [0]=> string(1) "2" } ["Submit"]=> string(6) "Submit" }

Here is the vardump with both boxes checked

 array(2) { ["form_id"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["Submit"]=> string(6) "Submit" }

cholmon · August 2, 2011, 5:43pm

The implode error from two posts up is b/c you’ve got

$_POST['form_id[]']

instead of

$_POST['form_id']

Karchion · August 2, 2011, 5:49pm

Thank You Cholmon, it is working

Karchion · August 2, 2011, 6:20pm

So I need one last thing, I need to add two more checkboxes one that updates missingfilm and one that updates statranscribe fields on my database.

so I added


// make sure all your IDs are integers...assuming you're using integers as your table's PK
$com = implode(',', array_map('intval', $_POST['completed']));
$mis = implode(',', array_map('intval', $_POST['missing']));
$str = implode(',', array_map('intval', $_POST['strans']));

// update all the checked exams
$sqlc = "UPDATE formdata SET rcompleted='y' where form_id IN ($com)";
$sqlm = "UPDATE formdata SET missingfilm='y' where form_id IN ($mis)";
$sqls = "UPDATE formdata SET stattranscribe='y' where form_id IN ($str)";

mysql_query($sqlc);
mysql_query($sqlm);
mysql_query($sqls);

?>

However I am getting a similar error now



Warning: array_map() [function.array-map]: Argument #2 should be an array in /var/www/helpdesk/req/rviewsubmit.php on line 9

Warning: implode() [function.implode]: Bad arguments. in /var/www/helpdesk/req/rviewsubmit.php on line 9

Warning: array_map() [function.array-map]: Argument #2 should be an array in /var/www/helpdesk/req/rviewsubmit.php on line 10

Warning: implode() [function.implode]: Bad arguments. in /var/www/helpdesk/req/rviewsubmit.php on line 10

Warning: array_map() [function.array-map]: Argument #2 should be an array in /var/www/helpdesk/req/rviewsubmit.php on line 11

Warning: implode() [function.implode]: Bad arguments. in /var/www/helpdesk/req/rviewsubmit.php on line 11

cholmon · August 2, 2011, 6:49pm

This code…


$com = implode(',', array_map('intval', $_POST['completed']));
$mis = implode(',', array_map('intval', $_POST['missing']));
$str = implode(',', array_map('intval', $_POST['strans']));

…expects the corresponding checkboxes to be named properly when all the records are printed in the big HTML table, i.e…


// echoed on separate lines here for clarity
echo "<input type='checkbox' name='completed[]' value='$row[form_id]' />";
echo "<input type='checkbox' name='missing[]'   value='$row[form_id]' />";
echo "<input type='checkbox' name='strans[]'    value='$row[form_id]' />";