PHP RSS PHP RSS

mysql checkbox problem

Reply

•
•
Join Date: Apr 2009
Posts: 8
Reputation: jbobfunky is an unknown quantity at this point 
Solved Threads: 0
jbobfunky jbobfunky is offline Offline
Newbie Poster

mysql checkbox problem

 
-1
  #1
Oct 2nd, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 55
Reputation: wilch is an unknown quantity at this point 
Solved Threads: 9
wilch wilch is offline Offline
Junior Poster in Training

Re: mysql checkbox problem

 
1
  #2
Oct 2nd, 2009
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.
umm.. by the way how do you do it ?
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 8
Reputation: jbobfunky is an unknown quantity at this point 
Solved Threads: 0
jbobfunky jbobfunky is offline Offline
Newbie Poster

Re: mysql checkbox problem

 
0
  #3
Oct 2nd, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 8
Reputation: jbobfunky is an unknown quantity at this point 
Solved Threads: 0
jbobfunky jbobfunky is offline Offline
Newbie Poster

Re: mysql checkbox problem

 
0
  #4
Oct 2nd, 2009
Not sure what I should do about the mysql_fetch_assoc thing. Should I use another record set?

Johnny
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 55
Reputation: wilch is an unknown quantity at this point 
Solved Threads: 9
wilch wilch is offline Offline
Junior Poster in Training

Re: mysql checkbox problem

 
0
  #5
Oct 4th, 2009
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.
umm.. by the way how do you do it ?
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2009
Posts: 8
Reputation: jbobfunky is an unknown quantity at this point 
Solved Threads: 0
jbobfunky jbobfunky is offline Offline
Newbie Poster

Still no success

 
0
  #6
Oct 9th, 2009
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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



ajax apache api array beginner beneath binary broadband broken button cakephp checkbox class cms code countingeverycharactersfromastring crack cron curl database date decode display dynamic echo email error file files folder form forms function functions google href htaccess html image include insert integration ip javascript joomla limit link links login mail match md5 menu mlm multiple mysql mysql_real_escape_string oop paypal pdf php problem protocol query radio random recursion regex remote script search searchbox server session sessions sms smtp soap source space sql strip_tags survey syntax system table tutorial undefined update upload url validator variable video virus votedown web window.onbeforeunload=closeme; xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC