updating multiple sql records using checkboxes

jacko_162 · December 21, 2010

I know how to pull data from sql table and limit down the results,

what i wanted to do is pull data from a sql table 'finished' and list data that has paid='0'

this is then limited to show the amount of rows set by variable 'maxPay'

here is my list what im pulling atm;

    <h2>Payment </h2>
    <?php 
if($userType[user_type]==28){	
$queryPay = mysql_query("SELECT * FROM finished WHERE paid='0'&&method!='FALSE'&&method!='credit';") or die(mysql_error());

$queryAdmin = mysql_query("SELECT maxPay FROM `admin`;") or die(mysql_error());

$row = mysql_num_rows($queryPay);
//echo $row."row<br />";
$rowsArray = mysql_fetch_assoc($queryAdmin);
$rows = $rowsArray[maxPay];
//echo $rows."rows<br />";

if($rows>$row){
	$r=$row;
}
else{
	$r=$rows;
}
$s=0;
while($s<$r){
	if($_POST[complete]==TRUE){
		mysql_query("UPDATE `finished` SET `paid`='$_POST[paid]' WHERE `auctionID`='$_POST[aid]';");
		$_POST[complete] = FALSE;

	}
	$s++;
}

$t=0;
while($t<$r){
	$queryuser = mysql_query("SELECT * FROM finished WHERE paid='0'&&method!='FALSE'&&method!='credit';") or die(mysql_error());

	$user = mysql_result($queryuser, $t, "username");
	//echo $user;
	$item = mysql_result($queryuser, $t, "itemName");
	//echo $item;
	$value = mysql_result($queryuser, $t, "marketPrice");
	//echo $value;
	$method = mysql_result($queryuser, $t, "method");
	//echo $method;
	$id = mysql_result($queryuser, $t, "auctionID");
	//echo $id;



	//echo $method;
	if($method=='isk'){
		?>
		<FORM method="post" action="pay.php">
		<input type="hidden" name=complete value="TRUE" />
		<input type="hidden" name=paid value="YES" />
		<input type="hidden" name=username value="<? echo $user; ?>" />
		<input type="hidden" name=aid value="<? echo $id; ?>" />
		<input type="submit" value="Paid" /><?
		echo $user." won a ".$item." he selected to receive ".$value."ISK.";

		echo "<br />";
		?></FORM><?
	}
	elseif($method=='ship'){
		?>
		<FORM method="post" action="pay.php">
		<input type="hidden" name=complete value="TRUE" />
		<input type="hidden" name=paid value="YES" />
		<input type="hidden" name=username value="<? echo $user; ?>" />
		<input type="hidden" name=aid value="<? echo $id; ?>" />
		<input type="submit" value="Paid" /><?
		echo $user." won a ".$item." he selected to receive the ship.";

		echo "<br />";
		?></FORM><?
	}
	else{

	}
	$t++;
}

}
else{
echo "You are not an Admin.";	
}
?>

and pay.php:

<?php 

header('Location: /admin.php');

include "connect.php";
echo $_POST[aid];
if($_POST[complete]==TRUE){
	$tempID = (($_POST[aid])+1);
	mysql_query("UPDATE `finished` SET `paid`='$_POST[paid]' WHERE `auctionID`='$tempID';") or die(mysql_error());
	$_POST[complete] = FALSE;
}
?>

at the moment i am listing all my data, and processing it using pay.php, each dataset has a "paid" button, i want to change this to using a checkbox at end of each data row, and a submit button at the bottom. this way i can select which rows to update and tick the checkbox, then click submit and those rows will then be updated the value '0' in db to a value of 'YES'.

hope i explained myself clearly enough for some help.

im a relative php noob still in training.

jacko_162 · December 21, 2010

managed to find some code snippets which should do the trick

<?php

include "connect.php";

if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];
if(isset($_POST['activate'])?$activate = $_POST["activate"]:$deactivate = $_POST["deactivate"])
$id = "('" . implode( "','", $checkbox ) . "');" ;
$sql="UPDATE finished SET paid = '".(isset($activate)?'YES':'0')."' WHERE auctionID='$auctionID'" ;
$result = mysql_query($sql) or die(mysql_error());
}

$sql="SELECT * FROM finished WHERE paid ='0' AND method='ship'";
$result=mysql_query($sql);

$count=mysql_num_rows($result);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta http-equiv="Content-Type" content="text/html; charset="utf-8"" />
<title>Update multiple rows in mysql with checkbox</title>

<script type="text/javascript">
<!--
function un_check(){
for (var i = 0; i < document.frmactive.elements.length; i++) {
var e = document.frmactive.elements[i];
if ((e.name != 'allbox') && (e.type == 'checkbox')) {
e.checked = document.frmactive.allbox.checked;
}}}
//-->
</script>

</head>
<body>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="frmactive" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1">
<tr>
<td colspan="7"><input name="activate" type="submit" id="activate" value="Activate" />
<input name="deactivate" type="submit" id="deactivate" value="Deactivate" /><br />
<?php
    $queryAdmin = mysql_query("SELECT * FROM admin;") or die(mysql_error());
$maxPay = mysql_result($queryAdmin, 0, "maxPay");
    //echo $maxPay;
?>
<br /></td>
</tr>
<tr>
<td> </td>
<td colspan="6"><strong>Outstanding ship orders</strong> </td>
</tr><tr>
<td align="center"><input type="checkbox" name="allbox" title="Select or Deselct ALL" style="background-color:#ccc;"/></td>
<td align="center"><strong>auctionID</strong></td>
<td align="center"><strong>Character</strong></td>
<td align="center"><strong>Item Name</strong></td>
<td align="center"><strong>Value</strong></td>
<td align="center"><strong>Method</strong></td>
<td align="center"><strong>paid</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td><? echo $rows['auctionID']; ?></td>
<td><? echo $rows['username']; ?></td>
<td><? echo $rows['itemName']; ?></td>
<td><? echo $rows['marketPrice']; ?></td>
<td><? echo $rows['method']; ?></td>
<td><? echo $rows['paid']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7" align="center"> </td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>

problem now is its not updating the database correctly.?

here is my connect.php script, anything wrong here?

<?php

$dbServer = "db server";
$dbUsername = "username";
$dbPassword = "password";

$db = mysql_connect($dbServer, $dbUsername, $dbPassword) or die("Could not connect.");

if(!$db) 

die("no db");

if(!mysql_select_db("databasename",$db))

	die("No database selected.");

//echo "connected";

?>

jacko_162 · December 21, 2010

got it updating now.. damn typo's.

anyone know why the main tickbox doesnt tick all the tick boxes?

suma237 · December 21, 2010

function checkedAll(frm,val)
{
var chkValue = val.checked;
var len = frm.elements.length;
for(i=0;i<len;i++)
{
  frm.elements[i].checked=chkValue;
}
}
[code]
<input type="checkbox" name="checkbox" id="checkbox"  class="checkbox" onclick="javascript:checkedAll(this.form,this);"/>

jacko_162 · December 21, 2010

works a treat

only thing i need now is if row count = 0 echo"Nothing to update"

can anyone help me with this?

suma237 · December 21, 2010

check the array is empty or not

jacko_162 · December 21, 2010

cheers again suma,

i use the if(empty($result)){ to check if its empty and works.

Sign In

updating multiple sql records using checkboxes

Recommended Posts

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

suma237

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

suma237

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information