944,083 Members | Top Members by Rank

Web Development > PHP > mysql checkbox problem
Ad:
  • PHP Discussion Thread
  • Unsolved
  • Views: 624
  • PHP RSS
Permalink
Oct 2nd, 2009
-1

mysql checkbox problem

Expand Post »
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 Syntax (Toggle Plain Text)
  1. -------------------------------- php-------------------------------------
  2. <?php
  3. $query_vsp_all = "SELECT * FROM vsp_admin WHERE vsp_coupon_sent = 1";
  4. $vsp_all = mysql_query($query_vsp_all, $ppielogin) or die(mysql_error());
  5. $row_vsp_all = mysql_fetch_assoc($vsp_all);
  6. $totalRows_vsp_all = mysql_num_rows($vsp_all);
  7.  
  8. function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
  9. {
  10.   $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
  11.  
  12.   switch ($theType) {
  13.     case "text":
  14.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  15.       break;    
  16.     case "long":
  17.     case "int":
  18.       $theValue = ($theValue != "") ? intval($theValue) : "NULL";
  19.       break;
  20.     case "double":
  21.       $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
  22.       break;
  23.     case "date":
  24.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  25.       break;
  26.     case "defined":
  27.       $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  28.       break;
  29.   }
  30.   return $theValue;
  31. }
  32.  
  33. if((isset($_POST[Submit_1])) && ($_POST[Submit_1] == "form1"))
  34. {
  35.  
  36. 	while($row_vsp = mysql_fetch_assoc($vsp_all))
  37. 	{
  38. 		$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"));
  39.  
  40. 	}	
  41.  
  42. 	mysql_query($sql) or die(mysql_error());
  43. 	redirect_page('admin_vsp_view.php');
  44. }
  45. ?>
  46. --------------------------------------------------html-------------------------
  47. <form  name="form1" method="POST" >
  48. <table width="900" border="1">
  49.  
  50.   <tr  class="smallText" >
  51.     <td colspan="13"><?php echo $totalRows_vsp_all ?> Records Total</td>
  52.     </tr>
  53.   <tr class="col_header">
  54.    <td>Coupon Number</td>
  55.    <td>Sent</td>
  56.    <td>Redeem</td>
  57.  
  58.     <td>Birthday Month</td>
  59.     <td>First Name </td>
  60.     <td>Last Name </td>
  61.     <td>City</td>
  62.     <td>Email</td>
  63.   </tr>
  64.  
  65.   <?php do { ?>
  66.     <tr  class="smallText" >
  67. 	 <td>0<?php echo $row_vsp_all['vsp_record_id']; ?> </td>
  68.     <td>  <input <?php if (!(strcmp($row_vsp_all['vsp_coupon_sent'],1))) {echo "checked=\"checked\"";} ?> name="sent_chkbx" type="checkbox" value="1" /></td>
  69.       <td><input <?php if (!(strcmp($row_vsp_all['vsp_coupon_redeemed'],1))) {echo "checked=\"checked\"";} ?> name="redeemed_chkbx" type="checkbox" value="1"  />
  70.  
  71. 	  </td>
  72.  
  73.         <td><?php echo $row_vsp_all['vsp_birthday_month']; ?> </td>
  74.       <td><?php echo ucfirst($row_vsp_all['vsp_firstname']); ?> </td>
  75.       <td><?php echo ucfirst($row_vsp_all['vsp_lastname']); ?> </td>
  76.       <td><?php echo ucfirst($row_vsp_all['vsp_city']); ?> </td>
  77.       <td><?php echo strtolower($row_vsp_all['vsp_email']); ?> </td>
  78.     </tr>
  79.     <?php } while ($row_vsp_all = mysql_fetch_assoc($vsp_all)); ?>
  80. 	<tr>
  81.  
  82. 	</tr>
  83. 	<input type="hidden" name="vsp_record_id" value="<?php echo $$row_vsp_all['vsp_record_id']; ?>">
  84. </table><br />
  85.  
  86. <input type="hidden"  name="Submit_1" value="form1">
  87. <input type="submit" value="Update VSP Database" />
  88. </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
Last edited by jbobfunky; Oct 2nd, 2009 at 5:29 pm.
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
jbobfunky is offline Offline
9 posts
since Apr 2009
Permalink
Oct 2nd, 2009
1

Re: mysql checkbox problem

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.
Reputation Points: 21
Solved Threads: 15
Junior Poster in Training
wilch is offline Offline
76 posts
since Aug 2007
Permalink
Oct 2nd, 2009
0

Re: mysql checkbox problem

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 Syntax (Toggle Plain Text)
  1. <?php
  2. mysql_select_db($database_ppielogin, $ppielogin);
  3. $query_vsp_all = "SELECT * FROM vsp_admin WHERE vsp_coupon_sent = 1";
  4. $vsp_all = mysql_query($query_vsp_all, $ppielogin) or die(mysql_error());
  5. $row_vsp_all = mysql_fetch_assoc($vsp_all);
  6. $totalRows_vsp_all = mysql_num_rows($vsp_all);
  7. ?>
  8. <?php 
  9. function redirect_page($url)
  10. {
  11. 	header ('Location:' .$url);
  12. 	exit;
  13. }
  14.  
  15. if ($totalRows_vsp_all = 0)
  16. {	
  17. 	redirect_page('admin_vsp_view.php');
  18. }
  19. ?>
  20. <?php
  21. // when the "update vsp database" button is clicked, insert a 1 to the vsp_coupon redeemed column in the vsp_admin table 
  22. //UPDATE vsp_admin SET vsp_coupon_redeemed = 1 WHERE vsp_coupon_sent = 1 ||||| && $_POST['redeemed_chkbx'] == '1'
  23. function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
  24. {
  25.   $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
  26.  
  27.   switch ($theType) {
  28.     case "text":
  29.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  30.       break;    
  31.     case "long":
  32.     case "int":
  33.       $theValue = ($theValue != "") ? intval($theValue) : "NULL";
  34.       break;
  35.     case "double":
  36.       $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
  37.       break;
  38.     case "date":
  39.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  40.       break;
  41.     case "defined":
  42.       $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  43.       break;
  44.   }
  45.   return $theValue;
  46. }
  47.  
  48.  
  49.  
  50.  
  51. if((isset($_POST[Submit_1])) && ($_POST[Submit_1] == "form1"))
  52. {
  53.  
  54. 	while($row_vsp = mysql_fetch_assoc($vsp_all))
  55. 	{
  56. 		$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"));
  57.  
  58. 	}	
  59.  
  60. 	mysql_query($sql) or die(mysql_error());
  61. 	redirect_page('admin_vsp_view.php');
  62. }
  63. ?>
  64. -------------------------------------------------------------------------
  65. <form  name="form1" method="POST" >
  66. <table width="900" border="1">
  67.  
  68.   <tr  class="smallText" >
  69.     <td colspan="13"><?php echo $totalRows_vsp_all ?> Records Total</td>
  70.     </tr>
  71.   <tr class="col_header">
  72.    <td>Coupon Number</td>
  73.    <td>Sent</td>
  74.    <td>Redeem</td>
  75.  
  76.     <td>Birthday Month</td>
  77.     <td>First Name </td>
  78.     <td>Last Name </td>
  79.     <td>City</td>
  80.     <td>Email</td>
  81.   </tr>
  82.  
  83.   <?php do { ?>
  84.     <tr  class="smallText" >
  85. 	 <td>0<?php echo $row_vsp_all['vsp_record_id']; ?> </td>
  86.     <td>  <input <?php if (!(strcmp($row_vsp_all['vsp_coupon_sent'],1))) {echo "checked=\"checked\"";} ?> name="sent_chkbx" type="checkbox" value="1" /></td>
  87.       <td><input <?php if (!(strcmp($row_vsp_all['vsp_coupon_redeemed'],1))) {echo "checked=\"checked\"";} ?> name="redeemed_chkbx" type="checkbox" value="1"  />
  88.  
  89. 	  </td>
  90.  
  91.         <td><?php echo $row_vsp_all['vsp_birthday_month']; ?> </td>
  92.       <td><?php echo ucfirst($row_vsp_all['vsp_firstname']); ?> </td>
  93.       <td><?php echo ucfirst($row_vsp_all['vsp_lastname']); ?> </td>
  94.       <td><?php echo ucfirst($row_vsp_all['vsp_city']); ?> </td>
  95.       <td><?php echo strtolower($row_vsp_all['vsp_email']); ?> </td>
  96.     </tr>
  97. 	<input type="hidden" name="vsp_record_id" value="<?php echo $row_vsp_all['vsp_record_id']; ?>">
  98.     <?php } while ($row_vsp_all = mysql_fetch_assoc($vsp_all)); ?>
  99. 	<tr>
  100.  
  101. 	</tr>
  102.  
  103. </table><br />
  104.  
  105. <input type="hidden"  name="Submit_1" value="form1">
  106. <input type="submit" value="Update VSP Database" />
  107. </form>


you rock. I'll keep posting!


Johnny
Last edited by jbobfunky; Oct 2nd, 2009 at 6:57 pm.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
jbobfunky is offline Offline
9 posts
since Apr 2009
Permalink
Oct 2nd, 2009
0

Re: mysql checkbox problem

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

Johnny
Reputation Points: 10
Solved Threads: 0
Newbie Poster
jbobfunky is offline Offline
9 posts
since Apr 2009
Permalink
Oct 4th, 2009
0

Re: mysql checkbox problem

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.
Reputation Points: 21
Solved Threads: 15
Junior Poster in Training
wilch is offline Offline
76 posts
since Aug 2007
Permalink
Oct 9th, 2009
0

Still no success

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 Syntax (Toggle Plain Text)
  1. <?php
  2. mysql_select_db($database_ppielogin, $ppielogin);
  3. $query_vsp_all = "SELECT * FROM vsp_admin WHERE vsp_coupon_sent = 1";
  4. $vsp_all = mysql_query($query_vsp_all, $ppielogin) or die(mysql_error());
  5. $row_vsp_all = mysql_fetch_assoc($vsp_all);
  6. $totalRows_vsp_all = mysql_num_rows($vsp_all);
  7.  
  8. mysql_select_db($database_ppielogin, $ppielogin);
  9. $query_Recordset1 = "SELECT vsp_record_id FROM vsp_admin WHERE vsp_coupon_sent = 1";
  10. $Recordset1 = mysql_query($query_Recordset1, $ppielogin) or die(mysql_error());
  11.  
  12. $totalRows_Recordset1 = mysql_num_rows($Recordset1);
  13. ?>
  14. <?php 
  15. function redirect_page($url)
  16. {
  17. 	header ('Location:' .$url);
  18. 	exit;
  19. }
  20.  
  21. if ($totalRows_vsp_all = 0)
  22. {	
  23. 	redirect_page('admin_vsp_view.php');
  24. }
  25. ?>
  26. <?php
  27. // when the "update vsp database" button is clicked, insert a 1 to the vsp_coupon redeemed column in the vsp_admin table 
  28. //UPDATE vsp_admin SET vsp_coupon_redeemed = 1 WHERE vsp_coupon_sent = 1 ||||| && $_POST['redeemed_chkbx'] == '1'
  29. function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
  30. {
  31.   $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
  32.  
  33.   switch ($theType) {
  34.     case "text":
  35.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  36.       break;    
  37.     case "long":
  38.     case "int":
  39.       $theValue = ($theValue != "") ? intval($theValue) : "NULL";
  40.       break;
  41.     case "double":
  42.       $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
  43.       break;
  44.     case "date":
  45.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  46.       break;
  47.     case "defined":
  48.       $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  49.       break;
  50.   }
  51.   return $theValue;
  52. }
  53.  
  54.  
  55.  
  56. if((isset($_POST[Submit_1])) && ($_POST[Submit_1] == "form1"))
  57. {
  58.  
  59. 	while($row_Recordset1 = mysql_fetch_array($Recordset1))
  60. 	{
  61. 		$sql= sprintf("UPDATE vsp_admin SET vsp_coupon_redeemed = %s WHERE vsp_record_id = %s", 
  62. 		GetSQLValueString(isset($_POST['redeemed_chkbx']) ? "true" : "", "defined","1","0"),
  63. 		 GetSQLValueString($_POST['vsp_record_id'], "int"));
  64.  
  65. 	}	
  66.  
  67. 	mysql_query($sql) or die(mysql_error());
  68. 	redirect_page('admin_vsp_view.php');
  69. }
  70. ?>
  71. ---------------------html----------------------
  72.  
  73. <h2>Redeem VSP  Coupons</h2>
  74.  
  75. <table width="900" border="1">
  76.  
  77.   <tr  class="smallText" >
  78.     <td colspan="13"><?php echo $totalRows_Recordset1 ?> Records Total</td>
  79.     </tr>
  80.   <tr class="col_header">
  81.    <td>Coupon Number</td>
  82.    <td>Sent</td>
  83.    <td>Redeem</td>
  84.  
  85.     <td>Birthday Month</td>
  86.     <td>First Name </td>
  87.     <td>Last Name </td>
  88.     <td>City</td>
  89.     <td>Email</td>
  90.   </tr>
  91.  <form name="form1" method="POST" >
  92.   <?php do { ?>
  93.     <tr  class="smallText" >
  94. 	 <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>
  95.     <td>  <input <?php if (!(strcmp($row_vsp_all['vsp_coupon_sent'],1))) {echo "checked=\"checked\"";} ?> name="sent_chkbx" type="checkbox" value="1" /></td>
  96.       <td><input <?php if (!(strcmp($row_vsp_all['vsp_coupon_redeemed'],1))) {echo "checked=\"checked\"";} ?> name="redeemed_chkbx" type="checkbox" value="1"  />
  97.  
  98. 	  </td>
  99.  
  100.         <td><?php echo $row_vsp_all['vsp_birthday_month']; ?> </td>
  101.       <td><?php echo ucfirst($row_vsp_all['vsp_firstname']); ?> </td>
  102.       <td><?php echo ucfirst($row_vsp_all['vsp_lastname']); ?> </td>
  103.       <td><?php echo ucfirst($row_vsp_all['vsp_city']); ?> </td>
  104.       <td><?php echo strtolower($row_vsp_all['vsp_email']); ?> </td>
  105.     </tr>
  106.     <?php } while ($row_vsp_all = mysql_fetch_assoc($vsp_all)); ?>
  107. 	<input type="hidden"  name="Submit_1" value="form1">
  108. <input type="submit" value="Update VSP Database" />
  109. </form>
  110. 	<tr>
  111. 	</tr>
  112. 	</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
Last edited by jbobfunky; Oct 9th, 2009 at 2:48 pm.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
jbobfunky is offline Offline
9 posts
since Apr 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: .htaccess issue
Next Thread in PHP Forum Timeline: database





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC