Updating Records - Serious Help Needed!

kpowning

I have the following script. I'm trying to make an admin page the shows all records of submissions pending an "approval". I'm trying to make a form that will upon submit either change their "pending" status to "Approved" if the checkbox approved is checked or if I check the decline checkbox, all their information will be deleted.

NEED HELP AS SOON AS POSSIBLE...PLEASE...........
THANKS IN ADVANCE!!!

Here is my script:

$msg ='';
mysql_select_db($database_gspot, $gspot);
$query_Recordset1 = "SELECT * FROM membersrigs WHERE Status = 'Pending'";
$Recordset1 = mysql_query($query_Recordset1, $gspot) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$username = $row_Recordset1['Username'];
if (isset($HTTP_POST_VARS['submit'])){
if ($approve == 'Approved'){
$shortlist = 'Approved'; 
}
else if ( 'Decline' == $decline) {
"DELETE FROM membersrigs WHERE Username='$username'";
$msg = "Successfully Deleted";
} else {
$query2 = "UPDATE membersrigs SET Status='$shortlist' WHERE Username='$username'"; 
$msg = "Successfully Approved";
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error()); 
}
}

And the html:

<form name="form1" method="post" action="">
<input name="approve" type="checkbox" id="approve" value="Approved">
Approve
<input name="decline" type="checkbox" id="decline" value="Decline">
Decline
<input name="submit" type="submit" id="submit" value="Update">
</form>

tsinka

Hi,

here's an example. The code allows to approve all, to decline all or to approve and decline members as selected. The code is rather long and maybe not the shortest one but it should work.

First of all I'd make sure that you add userids to the members if you didn't already because that makes things a lot easier.

<?PHP
	//mysql_select_db($database_gspot, $gspot);

if (isset($_POST["submit"])) {
	// update table depending on selected check boxes
	if (isset($_POST["approve"]) && is_array($_POST["approve"])) {
		// at least one of the check boxes has been selected
		$arrApp = "";
		$arrDec = "";
		foreach($_POST["approve"] as $key => $value) {
			// read out the key and value of the check box
			// the key is also the key of the userid array
			$userid = $_POST["userid"][$key];
			if ($value == "Approved") {
				$arrApp[] = $userid;
			}
			else if ($value == "Decline") {
				$arrDec[] = $userid;
			}
		}

		// create queries
		if (is_array($arrApp)) {
			$userids = implode(",",$arrApp);
			$sqlApp  = "UPDATE membersrigs WHERE Userid IN ($userids)";
		}

		if (is_array($arrDec)) {
			$userids = implode(",",$arrDec);
			$sqlDec  = "DELETE FROM membersrigs WHERE Userid IN ($userids)";
		}

		if (isset($sqlApp))
            mysql_query($sqlApp) or die("Error in query: $query2. " . mysql_error());
		if (isset($sqlDec))
		    mysql_query($sqlDec) or die("Error in query: $query2. " . mysql_error());

	}
}
else if (isset($_POST['approveall']) || isset($_POST['declineall'])) {
	// approve or decline all
	$userids = implode(",",$_POST["userid"]);

	if (isset($_POST['approveall']))
		$sql = "UPDATE membersrigs SET Status='Approved' WHERE Userid IN ($userids)";
	else
	    $sql = "DELETE FROM membersrigs WHERE Userid IN ($userids)";

	mysql_query($sql) or die("Error in query: $query2. " . mysql_error());
}

// read out all members with a status of 'Pending'
$sql    = "SELECT * FROM membersrigs WHERE Status = 'Pending'";
$result = mysql_query($sql, $gspot) or die(mysql_error());
$cnt = 0;
while ($rows = mysql_fetch_array($result)) {
	$arrMembers[$cnt]["username"] = $rows["username"];
	$arrMembers[$cnt]["userid"]   = $rows["Userid"];
	$cnt++;
}
?>
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>
       <title>Admin</title>
</head>
<body>
<form name="form1" method="post" action="<?=$_SERVER["PHP_SELF"]; ?>">
<table>
    <tr>
		<td>Member</td><td>Approve</td><td>Decline</td>
	</tr>
	<?PHP for ($cnt=0;$cnt<count($arrMembers);$cnt++) { ?>
	<tr>
	    <td><?=$arrMembers[$cnt]["username"]; ?></td>
		<td>
		    <input type="hidden" name="userid[<?=$cnt; ?>]" value="<?=$arrMembers[$cnt]["Userid"]; ?>">
			<input name="approve[<?=$cnt; ?>]" type="checkbox" value="Approved">
		</td>
		<td><input name="approve[<?=$cnt; ?>]" type="checkbox" value="Decline"></td>
	</tr>
	<?PHP } ?>
	<tr>
<td><input name="submit" type="submit" id="submit" value="Update"></td>
<td><input name="approveall" type="submit" value="Approve All"><br></td>
<td><input name="declineall" type="submit" value="Decline All"><br></td>
</tr>
</table>
</form>
</form>
</body>
</html>

I tested all but the mysql functions (and hope that I didn't mess up stuff when removing the debug output).

Thomas

kpowning

Thanks for the help, but I get the following error..

Error in query: . You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Userid =() at line 1

kpowning

How can I make a select all records that are "Pending" and update all to "Approved" when a button named "Approve all" is submitted? Is there actually a way to integrate your checkbox idea on all repeated regions of these records that will be specifically for each user?

Sorry for all the questions, but I'm trying to understand how i can actually make an admin section that will allow me to use repeated regions and make updating / deleting records as easy as possible..

THANKS FOR ALL THE HELP!

Here is my script:

//connect to database//

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($HTTP_GET_VARS['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $HTTP_GET_VARS['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_gspot, $gspot);
$query_Recordset1 = "SELECT * FROM membersrigs WHERE Status = 'Pending'";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $gspot) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($HTTP_GET_VARS['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $HTTP_GET_VARS['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
//approve user
$username = $row_Recordset1['Username'];
if ($_POST['approve']){
$shortlist = 'Approved'; 
$query2 = "UPDATE membersrigs SET Status='$shortlist' WHERE Username='$username'"; 
$result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error()); 
$msg = "Successfully Approved $username(s) record!";
header ("Location: /members/admin.php?msg=$msg");
}
//end approval
//start decline user
if ($_POST['decline']){
$result4 = mysql_query("SELECT vimage FROM membersrigs WHERE username = '$username'"); 
$vimage = mysql_fetch_assoc($result4); 
$deletevimage = $vimage['vimage'];
$locvimage = "/webapp/vhosts/gspot@gspotracing.com/members/images/$deletevimage"; 
unlink($locvimage);
$query3 = "DELETE FROM membersrigs WHERE Username='$username'";
$result3 = mysql_query($query3) or die ("Error in query: $query3. " . mysql_error());
$msg = "You have successfully deleted $username(s) declined record";
header ("Location: /members/admin.php?msg=$msg");
}
//end decline

and the html:

<form name="form1" method="post" action="">
<input name="approve" type="submit" id="approve" value="Approve">
<input name="decline" type="submit" id="decline" value="Decline">
<span class="blue"><?php echo $row_Recordset1['Username']; ?>(s)</span> record?
</form>
<p class="greybold"><strong><font color="#FF0000">
<?php
if ($msg != "")
echo "<p>" . $msg;
?>
</font></strong></p>
<?php do { ?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="20%" class="blue"><div align="left">Username:</div></td>
<td width="80%"><div align="left"><?php echo $row_Recordset1['Username']; ?></div></td>
</tr>
<tr>
<td class="blue"><div align="left">Vehicle Make:</div></td>
<td><div align="left"><?php echo $row_Recordset1['Category']; ?></div></td>
</tr>
<tr>
<td class="blue"><div align="left">Vehicle Model:</div></td>
<td><?php echo $row_Recordset1['Vehicle Model']; ?> <div align="left"></div></td>
</tr>
<tr>
<td class="blue"><div align="left">Vehicle Year:</div></td>
<td><?php echo $row_Recordset1['Vehicle Year']; ?> <div align="left"></div></td>
</tr>
<tr>
<td class="blue"><div align="left">Modifications:</div></td>
<td><?php echo $row_Recordset1['Vehicle Modifications']; ?>
<div align="left"></div></td>
</tr>
<tr>
<td colspan="2"> <p align="left" class="blue">Vehicle Image:</p></td>
</tr>
<tr>
<td colspan="2"> <?php echo "<img src='" . "/members/images/" . $row_Recordset1['vimage'] . "'"; ?>
<div align="left"></div></td>
</tr>
<tr>
<td class="blue"><div align="left">Comments:</div></td>
<td><div align="left"><?php echo $row_Recordset1['Comments']; ?></div></td>
</tr>
<tr>
<td class="blue"><div align="left">Status:</div></td>
<td><div align="left"><?php echo $row_Recordset1['Status']; ?></div></td>
</tr>
</table>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>