mysql checkbox problem

[jbobfunky]

hello daniweb users,

I been currently developing a mass email html coupon system for my work and am stuck at the last part of the development, the redemption of the coupon. So it works like this:

On our site, users put in the month of their birthday and email and it gets added to our data base. I then go in and with script, query all the the records that match the current month. in this case month 10, October. I then have script pull users info into an html coupon with their unique ID number and then script sends of emails to all the recipients and places a 1 in the 'sent' field of the data base so that the next time I view the DB i can see who has been sent an email by use of check boxes, if the query returns a 1 for that id number the box is checked. I know an email has been sent. All this works great so far.

The next part is redeeming the coupon by the unique number once a customer comes in with coupon. So again I made a redemption page that queries the database for all records that have a 1 in the 'sent' field and through a repeated table region. it generates the records with the 'sent' checkboxes checked and a 'redeemed' checkbox empty, ready to be checked off. You check of the ones that you have in hand, hit the button and it should update the database with a 1 in the redeemed field for the ones that were checked off. My issue is properly updating the database from the multiple checkboxs generated from my query! Heres the code, it probably explains it better:

-------------------------------- php-------------------------------------
<?php
$query_vsp_all = "SELECT * FROM vsp_admin WHERE vsp_coupon_sent = 1";
$vsp_all = mysql_query($query_vsp_all, $ppielogin) or die(mysql_error());
$row_vsp_all = mysql_fetch_assoc($vsp_all);
$totalRows_vsp_all = mysql_num_rows($vsp_all);

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

if((isset($_POST[Submit_1])) && ($_POST[Submit_1] == "form1"))
{
	
	while($row_vsp = mysql_fetch_assoc($vsp_all))
	{
		$sql= sprintf("UPDATE vsp_admin SET vsp_coupon_redeemed = %s WHERE vsp_record_id = %s", GetSQLValueString(isset($_POST['redeemed_chkbx']) ? "true" : "", "defined","1","0"),  GetSQLValueString($_POST['vsp_record_id'], "int"));
	
	}	
	
	mysql_query($sql) or die(mysql_error());
	redirect_page('admin_vsp_view.php');
}
?>
--------------------------------------------------html-------------------------
<form  name="form1" method="POST" >
<table width="900" border="1">

  <tr  class="smallText" >
    <td colspan="13"><?php echo $totalRows_vsp_all ?> Records Total</td>
    </tr>
  <tr class="col_header">
   <td>Coupon Number</td>
   <td>Sent</td>
   <td>Redeem</td>
   
    <td>Birthday Month</td>
    <td>First Name </td>
    <td>Last Name </td>
    <td>City</td>
    <td>Email</td>
  </tr>
 
  <?php do { ?>
    <tr  class="smallText" >
	 <td>0<?php echo $row_vsp_all['vsp_record_id']; ?> </td>
    <td>  <input <?php if (!(strcmp($row_vsp_all['vsp_coupon_sent'],1))) {echo "checked=\"checked\"";} ?> name="sent_chkbx" type="checkbox" value="1" /></td>
      <td><input <?php if (!(strcmp($row_vsp_all['vsp_coupon_redeemed'],1))) {echo "checked=\"checked\"";} ?> name="redeemed_chkbx" type="checkbox" value="1"  />
	
	  </td>
	 
        <td><?php echo $row_vsp_all['vsp_birthday_month']; ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_firstname']); ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_lastname']); ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_city']); ?> </td>
      <td><?php echo strtolower($row_vsp_all['vsp_email']); ?> </td>
    </tr>
    <?php } while ($row_vsp_all = mysql_fetch_assoc($vsp_all)); ?>
	<tr>
	
	</tr>
	<input type="hidden" name="vsp_record_id" value="<?php echo $$row_vsp_all['vsp_record_id']; ?>">
</table><br />

<input type="hidden"  name="Submit_1" value="form1">
<input type="submit" value="Update VSP Database" />
</form>

I know its probably much simpler than I have set up but regardless I can't make it work!
I appreciate any and all suggestions, questions, help and thanks for your time and brains!

Johnny

[wilch]

Hi jbob, i didn't thoroughly analyze your code, but did a quick run through it, and i found 2 errors to start of with.
- line 83, the value being echoed into the hidden control's value attribute, has an extra $ sign. I don't think you need the $ sign.
- notice that on line 5 and line 36, you use the mysql_fetch_assoc() on the same result set, if not intended, this means that in your while loop you will loop for 1 less than the actual number of rows in the recordset.
The reason for this being, that you have already extracted a row at line 5, by the call to mysql_fetch_assoc. ie the result handle's pointer has moved 1 step down the recordset.

So you could try to fix this first, then retry and see what comes up next.

[jbobfunky]

Sweet! I did notice the double $ and fixed it and I am checking out the assoc issue to. I really appreciate your help. I noticed also in my thread that the "hidden" input value of the vsp_record_id is not in the while loop, therefor the php above can't find it. I have fixed those. At the moment for test purposes I have the query return only 2 test rows. It seems to work but its only updating one of the values, probably the assoc issue here's the code again

<?php
mysql_select_db($database_ppielogin, $ppielogin);
$query_vsp_all = "SELECT * FROM vsp_admin WHERE vsp_coupon_sent = 1";
$vsp_all = mysql_query($query_vsp_all, $ppielogin) or die(mysql_error());
$row_vsp_all = mysql_fetch_assoc($vsp_all);
$totalRows_vsp_all = mysql_num_rows($vsp_all);
?>
<?php 
function redirect_page($url)
{
	header ('Location:' .$url);
	exit;
}

if ($totalRows_vsp_all = 0)
{	
	redirect_page('admin_vsp_view.php');
}
?>
<?php
// when the "update vsp database" button is clicked, insert a 1 to the vsp_coupon redeemed column in the vsp_admin table 
//UPDATE vsp_admin SET vsp_coupon_redeemed = 1 WHERE vsp_coupon_sent = 1 ||||| && $_POST['redeemed_chkbx'] == '1'
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}




if((isset($_POST[Submit_1])) && ($_POST[Submit_1] == "form1"))
{
	
	while($row_vsp = mysql_fetch_assoc($vsp_all))
	{
		$sql= sprintf("UPDATE vsp_admin SET vsp_coupon_redeemed = %s WHERE vsp_record_id = %s", GetSQLValueString(isset($_POST['redeemed_chkbx']) ? "true" : "", "defined","1","0"),  GetSQLValueString($_POST['vsp_record_id'], "int"));
	
	}	
	
	mysql_query($sql) or die(mysql_error());
	redirect_page('admin_vsp_view.php');
}
?>
-------------------------------------------------------------------------
<form  name="form1" method="POST" >
<table width="900" border="1">

  <tr  class="smallText" >
    <td colspan="13"><?php echo $totalRows_vsp_all ?> Records Total</td>
    </tr>
  <tr class="col_header">
   <td>Coupon Number</td>
   <td>Sent</td>
   <td>Redeem</td>
   
    <td>Birthday Month</td>
    <td>First Name </td>
    <td>Last Name </td>
    <td>City</td>
    <td>Email</td>
  </tr>
 
  <?php do { ?>
    <tr  class="smallText" >
	 <td>0<?php echo $row_vsp_all['vsp_record_id']; ?> </td>
    <td>  <input <?php if (!(strcmp($row_vsp_all['vsp_coupon_sent'],1))) {echo "checked=\"checked\"";} ?> name="sent_chkbx" type="checkbox" value="1" /></td>
      <td><input <?php if (!(strcmp($row_vsp_all['vsp_coupon_redeemed'],1))) {echo "checked=\"checked\"";} ?> name="redeemed_chkbx" type="checkbox" value="1"  />
	
	  </td>
	 
        <td><?php echo $row_vsp_all['vsp_birthday_month']; ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_firstname']); ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_lastname']); ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_city']); ?> </td>
      <td><?php echo strtolower($row_vsp_all['vsp_email']); ?> </td>
    </tr>
	<input type="hidden" name="vsp_record_id" value="<?php echo $row_vsp_all['vsp_record_id']; ?>">
    <?php } while ($row_vsp_all = mysql_fetch_assoc($vsp_all)); ?>
	<tr>
	
	</tr>
	
</table><br />

<input type="hidden"  name="Submit_1" value="form1">
<input type="submit" value="Update VSP Database" />
</form>

you rock. I'll keep posting!

Johnny

[jbobfunky]

Not sure what I should do about the mysql_fetch_assoc thing. Should I use another record set?

Johnny

[wilch]

Yes, you could try that, at least that way 2 result sets will be independent of one another, should you use the mysql_fetch_assoc.

[jbobfunky]

Hello Daniweb community,

For anyone looking at this thread, thank you. I am still trying to work this peace of code out. here is the current version:
-------------------------php-----------------------

<?php
mysql_select_db($database_ppielogin, $ppielogin);
$query_vsp_all = "SELECT * FROM vsp_admin WHERE vsp_coupon_sent = 1";
$vsp_all = mysql_query($query_vsp_all, $ppielogin) or die(mysql_error());
$row_vsp_all = mysql_fetch_assoc($vsp_all);
$totalRows_vsp_all = mysql_num_rows($vsp_all);

mysql_select_db($database_ppielogin, $ppielogin);
$query_Recordset1 = "SELECT vsp_record_id FROM vsp_admin WHERE vsp_coupon_sent = 1";
$Recordset1 = mysql_query($query_Recordset1, $ppielogin) or die(mysql_error());

$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<?php 
function redirect_page($url)
{
	header ('Location:' .$url);
	exit;
}

if ($totalRows_vsp_all = 0)
{	
	redirect_page('admin_vsp_view.php');
}
?>
<?php
// when the "update vsp database" button is clicked, insert a 1 to the vsp_coupon redeemed column in the vsp_admin table 
//UPDATE vsp_admin SET vsp_coupon_redeemed = 1 WHERE vsp_coupon_sent = 1 ||||| && $_POST['redeemed_chkbx'] == '1'
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}



if((isset($_POST[Submit_1])) && ($_POST[Submit_1] == "form1"))
{
	
	while($row_Recordset1 = mysql_fetch_array($Recordset1))
	{
		$sql= sprintf("UPDATE vsp_admin SET vsp_coupon_redeemed = %s WHERE vsp_record_id = %s", 
		GetSQLValueString(isset($_POST['redeemed_chkbx']) ? "true" : "", "defined","1","0"),
		 GetSQLValueString($_POST['vsp_record_id'], "int"));
	
	}	
	
	mysql_query($sql) or die(mysql_error());
	redirect_page('admin_vsp_view.php');
}
?>
---------------------html----------------------

<h2>Redeem VSP  Coupons</h2>

<table width="900" border="1">

  <tr  class="smallText" >
    <td colspan="13"><?php echo $totalRows_Recordset1 ?> Records Total</td>
    </tr>
  <tr class="col_header">
   <td>Coupon Number</td>
   <td>Sent</td>
   <td>Redeem</td>
   
    <td>Birthday Month</td>
    <td>First Name </td>
    <td>Last Name </td>
    <td>City</td>
    <td>Email</td>
  </tr>
 <form name="form1" method="POST" >
  <?php do { ?>
    <tr  class="smallText" >
	 <td>0<?php echo $row_vsp_all['vsp_record_id']; ?><input type="hidden" name="vsp_record_id" value="<?php echo $row_vsp_all['vsp_record_id']; ?>"> </td>
    <td>  <input <?php if (!(strcmp($row_vsp_all['vsp_coupon_sent'],1))) {echo "checked=\"checked\"";} ?> name="sent_chkbx" type="checkbox" value="1" /></td>
      <td><input <?php if (!(strcmp($row_vsp_all['vsp_coupon_redeemed'],1))) {echo "checked=\"checked\"";} ?> name="redeemed_chkbx" type="checkbox" value="1"  />
	
	  </td>
	 
        <td><?php echo $row_vsp_all['vsp_birthday_month']; ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_firstname']); ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_lastname']); ?> </td>
      <td><?php echo ucfirst($row_vsp_all['vsp_city']); ?> </td>
      <td><?php echo strtolower($row_vsp_all['vsp_email']); ?> </td>
    </tr>
    <?php } while ($row_vsp_all = mysql_fetch_assoc($vsp_all)); ?>
	<input type="hidden"  name="Submit_1" value="form1">
<input type="submit" value="Update VSP Database" />
</form>
	<tr>
	</tr>
	</table><br />

the way it should work is like this, this code pulls from a database the records that have a "1" in the "sent" field of the database, displaying all records that have been sent an email. Next to the sent check box is a redeem check box. lets say records 1, 2, 3, 4 gets called, and I want to check off records 2, 4 as redeemed coupons. Once I click the update button it should update the database with a "1" in the redeedmed field for only records 2 and 4. At the moment if I check off record 2 to be redeem and click the update button it updates record number 4 only, the last record regardless of which record I check off. Ahhh! It seems straight forward but something doesn't add up. I appreciate any help on this one. I'll keep working on it. Thanks

Johnny