update several records in a queried list.

code-guy

I am working on a Syslog query page that will allow me to view all the events from today and then Ack items in the list so they no longer show up in the query.

The basic query works and the items are displayed fine, however i am unable to process the Acks all at once.

I have a Query that:
SELECT * FROM systemevents WHERE ReceivedAt>((now()+1)-1) and ack=0

the page has a checkbox to the left of all the returned items. I want to update the database field "ack" to "1" of every item with a check in the checkbox.

I am having difficulty processing all the checkboxes at once.

Below is the code

Thanks!!!

<?PHP
$username="root";
$password="";
$database="MyWinSyslog";
$server="192.168.1.105";

mysql_connect($server,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM systemevents WHERE ReceivedAt>((now()+1)-1) and ack=0 ORDER BY ReceivedAt DESC LIMIT 0,50";
mysql_query($query);
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();

if ($B1) {

mysql_connect($server,$username,$password);

@mysql_select_db($database) or die( "Unable to");

$sql = "UPDATE systemevents SET ack=1 WHERE ID='$ID'";

$result = mysql_query($sql);

mysql_close();  

echo "Thank you! Information entered.\n";

} else{}

<form>
<table align="center" bgcolor="FF9966" cellspacing="2" cellpadding="2">
<tr>
<th><input type="submit" value="ack" name="B1">
<th>Facility</th>
<th>Priority</th>
<th>Host</th>
<th>Message</th>
<th>Timestamp</th>
</tr>
<?PHP
$i=0;
while ($i < $num) {
/ $bgcolor = ($i % 2) ? "White" : "#DCDCDC"; /
$bgcolor = "#FFFFCC";
$id_num=mysql_result($result,$i,"ID");
$facility=mysql_result($result,$i,"Facility");
$priority=mysql_result($result,$i,"Priority");
$host=mysql_result($result,$i,"FromHost");
$message=mysql_result($result,$i,"Message");
$timestamp=mysql_result($result,$i,"ReceivedAt");
$cellcolor=$bgcolor;

/ change the priority to a useful name priority and set color /

if ($priority == "0") { $priority="Emergency"; $cellcolor="#dd0000";}
if ($priority == "1") { $priority="Alert"; $cellcolor="#dd00dd";}
if ($priority == "2") { $priority="Critical"; $cellcolor="#dd9900";}
if ($priority == "3") { $priority="Error"; $cellcolor="ff4444";}
if ($priority == "4") { $priority="Warning"; $cellcolor="redyellow";}
if ($priority == "5") { $priority="Notice"; $cellcolor="#00dd00";}
if ($priority == "6") { $priority="Info"; $cellcolor="#dd88ee";}
if ($priority == "7") { $priority="Debug"; $cellcolor="#3333ff";}

else {}

?>
<input type="hidden" name="ID" value="<?php echo $id_num; ?>">
<tr bgcolor="<?php echo $bgcolor; ?>">
<td align="center"><input type="checkbox" name="C1" value="ON">
<td align="center" ><a href="detail.php?ID=<? echo "$id_num"; ?>"><? echo "$facility"; ?></a></td>
<td align="center" bgcolor="<?php echo $cellcolor; ?>"><? echo "$priority"; ?></td>
<td align="center" ><? echo "$host"; ?></td>
<td ><? echo "$message"; ?></td>
<td width="150" align="center" ><? echo "$timestamp"; ?></td>
</tr>

</form>
<?
++$i;

}

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

Pig

query the db to get all the records, and spit out a checkbox for each one, with the name "ack[]" or whatever you want to do. Make the id of the current row the value of the checkbox.

while( $row = mysql_fetch_array($result) )
echo("<input type=checkbox name=ack[] value=\"$row[id]\">");

On the process page, run $ack through a loop and generate your queries.

foreach( $_POST['ack'] AS $value )
mysql_query("UPDATE table SET ack=1 WHERE id=$value");