943,910 Members | Top Members by Rank

Web Development > PHP > Deleting multiple rows from mysql with checkbox
Ad:
  • PHP Discussion Thread
  • Marked Solved
  • Views: 3251
  • PHP RSS
Permalink
Sep 18th, 2008
0

Deleting multiple rows from mysql with checkbox

Expand Post »
Hi all,

I am currently doing on this project and i am struck at this section - which is deleting multiple rows from mysql with checkbox.

The code i am using is
PHP Syntax (Toggle Plain Text)
  1. <?php
  2. $host="localhost"; // Host name
  3. $username="root"; // Mysql username
  4. $password="root"; // Mysql password
  5. $db_name="advert"; // Database name
  6. $tbl_name="test_mysql"; // Table name
  7. // Connect to server and select databse.
  8. mysql_connect("$host", "$username", "$password")or die("cannot connect");
  9. mysql_select_db("$db_name")or die("cannot select DB");
  10. $sql="SELECT * FROM $tbl_name";
  11. $result=mysql_query($sql);
  12. $count=mysql_num_rows($result);
  13. ?>
  14. <table width="400" border="0" cellspacing="1" cellpadding="0">
  15. <tr>
  16. <td><form name="form1" method="post" action="">
  17. <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  18. <tr>
  19. <td bgcolor="#FFFFFF">&nbsp;</td>
  20. <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
  21. </tr>
  22. <tr>
  23. <td align="center" bgcolor="#FFFFFF">#</td>
  24. <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
  25. <td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
  26. <td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
  27. <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
  28. </tr>
  29. <?php
  30. while($rows=mysql_fetch_array($result)){
  31. ?>
  32. <tr>
  33. <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
  34. <td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
  35. <td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
  36. <td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
  37. <td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
  38. </tr>
  39. <?php
  40. }
  41. ?>
  42. <tr>
  43. <td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
  44. </tr>
  45. <?
  46. // Check if delete button active, start this
  47. if($_POST['delete']){
  48. for($i=0;$i<$count;$i++){
  49. $del_id = $checkbox[$i];
  50. $sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
  51. $result = mysql_query($sql);
  52. }
  53. // if successful redirect to delete_multiple.php
  54. if($result){
  55. echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_multiple.php\">";
  56. }
  57. }
  58. mysql_close();
  59. ?>
  60. </table>
  61. </form>
  62. </td>
  63. </tr>
  64. </table>

The delete dont seem to work. Each time i click on the delete, it didnt delete my records. Where is the error from? Please help!

Thanks a lot.

Regards,
bear
Reputation Points: 10
Solved Threads: 0
Newbie Poster
bear24 is offline Offline
9 posts
since Sep 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

Helo Bear,
I guess the immediate problem is that you reference $checkbox instead of $_POST['checkbox']. You use $_POST['delete'] so you probably work in an environment with register_globals off.

There is a few other things that I would like to draw your attention to:
* I guess you wanted to use count($checkbox) in your for statement, instead of $count which is a # of your db records. Not that it wouldn't work like you have it now but it makes more sense to use count($checkbox) when you in fact want to traverse $checkbox.

* remove id="checkbox[]" - id must be unique so you can't have it the same for all checkboxes. Also, you don't need it at all if you don't reference the items with JavaScript or CSS.

* it's better to name variables in a way that suggest what information they carry. E.g. $deleteCarsIds[] for an array that holds ids of cars that should be deleted. Variable name $checkbox doesn't suggest anything. Also if you named your variables well you would probably choose $carsDbCount and then it would probably hint to you that using $carsDbCount while traversing $deleteCarsIds isn't right.

* <meta http-equi... works only if it's placed into HEAD part of your document. If it works in your browser then you really cannot believe it will work in other browsers as well.

* either use <?php or <? but choose one and use it consistently

Good luck
Reputation Points: 27
Solved Threads: 16
Junior Poster
petr.pavel is offline Offline
116 posts
since Mar 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

Hi,

I am new to php. This is sort of my first time dealing with codes. Moreover, this code is gotten from so of the website. So sorry! I dont really understand what you mean. I am so sorry!

Bear
Reputation Points: 10
Solved Threads: 0
Newbie Poster
bear24 is offline Offline
9 posts
since Sep 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

Quote ...
Hi,

I am new to php. This is sort of my first time dealing with codes. Moreover, this code is gotten from so of the website. So sorry! I dont really understand what you mean. I am so sorry!

Bear


great .....................
Reputation Points: 15
Solved Threads: 21
Posting Whiz in Training
nikesh.yadav is offline Offline
219 posts
since Feb 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

i have modified your code..
and its working now:
php Syntax (Toggle Plain Text)
  1. <?php
  2. $host="localhost"; // Host name
  3. $username="root"; // Mysql username
  4. $password="1234"; // Mysql password
  5. $db_name="opulent_online1"; // Database name
  6. $tbl_name="users"; // Table name
  7. // Connect to server and select databse.
  8. mysql_connect("$host", "$username", "$password")or die("cannot connect");
  9. mysql_select_db("$db_name")or die("cannot select DB");
  10. if($_SERVER['REQUEST_METHOD']=='POST')
  11. {
  12. if(isset($_POST['check_compare']))
  13. {
  14. $sql = "DELETE FROM $tbl_name WHERE find_in_set(uid,'".$_POST['check_compare']."')";
  15. $result = mysql_query($sql);
  16. }
  17. }
  18.  
  19. $sql="SELECT * FROM $tbl_name";
  20. $result=mysql_query($sql);
  21. $count=mysql_num_rows($result);
  22. ?>
  23. <?
  24. // Check if delete button active, start this
  25.  
  26.  
  27.  
  28. ?>
  29. <script>
  30. function comparision(){
  31. 		d=document.form1;
  32. 		var total="";
  33. 		if(!d.c.length){
  34. 			if(d.c.checked) {
  35. 				d.check_compare.value=d.check_compare.value+d.c.value+',';
  36. 				return true;
  37. 			} else {
  38. 				alert("Please select check Box");
  39. 				return false;
  40. 			}		
  41. 		}
  42. 		for(var i=0; i < d.c.length; i++){
  43. 			if(d.c[i].checked) {
  44. 				total +=d.c[i].value + "\n";
  45. 				d.check_compare.value=d.check_compare.value+d.c[i].value+',';
  46. 			}
  47. 		}
  48. 		if(d.check_compare.value=="") {
  49. 		 alert("Please select atleast one check Box");
  50. 		 return false;
  51. 		}
  52. 	}
  53. </script>
  54. <table width="400" border="0" cellspacing="1" cellpadding="0">
  55. <tr>
  56. <td><form name="form1" method="post" action="deleterows.php"onSubmit="return comparision()">
  57. 		<input type="hidden" name="check_compare">
  58. <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  59. <tr>
  60. <td bgcolor="#FFFFFF">&nbsp;</td>
  61. <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
  62. </tr>
  63. <tr>
  64. <td align="center" bgcolor="#FFFFFF">#</td>
  65. <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
  66. <td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
  67. <td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
  68. <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
  69. </tr>
  70. <?php
  71. while($rows=mysql_fetch_array($result)){
  72. ?>
  73. <tr>
  74. <td align="center" bgcolor="#FFFFFF">
  75. <input type="checkbox" name="c" value="<?=$rows['uid'];?>" id="c"/>
  76. </td>
  77. <td bgcolor="#FFFFFF"><? echo $rows['uid']; ?></td>
  78. <td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td>
  79. <td bgcolor="#FFFFFF"><? echo $rows['utype']; ?></td>
  80. <td bgcolor="#FFFFFF"><? echo $rows['uemail']; ?></td>
  81. </tr>
  82. <?php
  83. }
  84. ?>
  85. <tr>
  86. <td colspan="5" align="center" bgcolor="#FFFFFF">
  87.  
  88.  <input name="compbutton" type="submit" class="Button" value="Compare" /></td>
  89. </tr>
  90.  
  91. </table>
  92. </form>
  93. </td>
  94. </tr>
  95. </table>
Reputation Points: 137
Solved Threads: 162
Posting Virtuoso
Shanti C is offline Offline
1,641 posts
since Jul 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

I do like to know if i need 2 differ php in order for it to work? In order for the php code to work, i modify some of the parts to suit to my database and so on. But it seems that it still dont work.

The following is what i change:

php Syntax (Toggle Plain Text)
  1. <?php
  2. $host="localhost"; // Host name
  3. $username="root"; // Mysql username
  4. $password="root"; // Mysql password <- i change
  5. $db_name="advert"; // Database name <- i change
  6. $tbl_name="test_mysql"; // Table name <- i change
  7. // Connect to server and select databse.
  8. mysql_connect("$host", "$username", "$password")or die("cannot connect");
  9. mysql_select_db("$db_name")or die("cannot select DB");
  10. if($_SERVER['REQUEST_METHOD']=='POST')
  11. {
  12. if(isset($_POST['check_compare']))
  13. {
  14. $sql = "DELETE FROM $tbl_name WHERE find_in_set(id,'".$_POST['check_compare']."')";
  15. $result = mysql_query($sql); //<- i change uid to id
  16. }
  17. }
  18.  
  19. $sql="SELECT * FROM $tbl_name";
  20. $result=mysql_query($sql);
  21. $count=mysql_num_rows($result);
  22. ?>
  23. <?
  24. // Check if delete button active, start this
  25.  
  26.  
  27.  
  28. ?>
  29. <script>
  30. function comparision(){
  31. 		d=document.form1;
  32. 		var total="";
  33. 		if(!d.c.length){
  34. 			if(d.c.checked) {
  35. 				d.check_compare.value=d.check_compare.value+d.c.value+',';
  36. 				return true;
  37. 			} else {
  38. 				alert("Please select check Box");
  39. 				return false;
  40. 			}		
  41. 		}
  42. 		for(var i=0; i < d.c.length; i++){
  43. 			if(d.c[i].checked) {
  44. 				total +=d.c[i].value + "\n";
  45. 				d.check_compare.value=d.check_compare.value+d.c[i].value+',';
  46. 			}
  47. 		}
  48. 		if(d.check_compare.value=="") {
  49. 		 alert("Please select atleast one check Box");
  50. 		 return false;
  51. 		}
  52. 	}
  53. </script>
  54. <table width="400" border="0" cellspacing="1" cellpadding="0">
  55. <tr>
  56. <td><form name="form1" method="post" action="delete_multiple.php"onSubmit="return comparision()"> //<- i change the action file name 
  57. 		<input type="hidden" name="check_compare">
  58. <table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  59. <tr>
  60. <td bgcolor="#FFFFFF">&nbsp;</td>
  61. <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
  62. </tr>
  63. <tr>
  64. <td align="center" bgcolor="#FFFFFF">#</td>
  65. <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td> //<- i change
  66. <td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>//
  67. <td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>//<- i change
  68. <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>//<- i change
  69. </tr>
  70. <?php
  71. while($rows=mysql_fetch_array($result)){
  72. ?>
  73. <tr>
  74. <td align="center" bgcolor="#FFFFFF">
  75. <input type="checkbox" name="c" value="<?=$rows['id'];?>" id="c"/>
  76. </td>
  77. <td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td> //<- i change to read my db
  78. <td bgcolor="#FFFFFF"><? echo $rows['name']; ?></td> 
  79. <td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td> //<- i change to read my db
  80. <td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>//<- i change to read my db
  81. </tr>
  82. <?php
  83. }
  84. ?>
  85. <tr>
  86. <td colspan="5" align="center" bgcolor="#FFFFFF">
  87.  
  88.  <input name="compbutton" type="submit" class="Button" value="Compare" /></td>
  89. </tr>
  90.  
  91. </table>
  92. </form>
  93. </td>
  94. </tr>
  95. </table>

I am so sorry that i am so dumb! Do you mind teaching me what are the necessary things i need to change?
Last edited by bear24; Sep 18th, 2008 at 12:29 pm.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
bear24 is offline Offline
9 posts
since Sep 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

I have come out with a new code
PHP Syntax (Toggle Plain Text)
  1. 		<?php
  2. 			//mysql connection
  3. 			$dbconn = mysql_connect("localhost","root","root") or die();
  4. 			mysql_select_db("advert") or die();
  5.  
  6. 			$sqlquery = "SELECT * FROM test_mysql"; // query on table
  7. 			$sqlresult = mysql_query($sqlquery, $dbconn);
  8. 			$count = mysql_num_rows($sqlresult); // count query result			
  9.  
  10. 		?>
  11. <table width="400" border="1" cellspacing="1" cellpadding="0">
  12. <tr><td>	<form method="post" action="delete_multiple.php">
  13. <table width="400" border="1" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC"><tr>
  14. <td bgcolor="#FFFFFF">&nbsp;</td>
  15. <td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td></tr>
  16. <tr><td align="center" bgcolor="#FFFFFF">#</td>
  17. <td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
  18. <td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
  19. <td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
  20. <td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td></tr>
  21. <?php while($row = mysql_fetch_array($sqlresult)){ ?>
  22.  
  23. <tr><td align="center" bgcolor="#FFFFFF">
  24. <input type="checkbox" name="checkbox[]" id="checkbox[]"  value="<?php echo $row['id']?>" />
  25. </td><td bgcolor="#FFFFFF"><?php echo $rows['id']; ?></td>
  26. <td bgcolor="#FFFFFF"><?php echo $rows['name']; ?></td><td bgcolor="#FFFFFF"><?php echo $rows['lastname']; ?>
  27. </td><td bgcolor="#FFFFFF"><?php echo $rows['email']; ?>
  28. </td></tr>
  29.  
  30. <?php } ?>
  31.  
  32. <tr><td colspan="5" align="center" bgcolor="#FFFFFF">
  33. <input name="delete" type="submit" id="delete" value="Delete"></td></tr>
  34.  
  35. <?php
  36. 		//mysql connection here
  37.  
  38. 		if($_POST['delete']) // from button name="delete"
  39. 		{
  40. 			$checkbox = $_POST['checkbox']; //from name="checkbox[]"
  41. 			$countCheck = count($_POST['checkbox']);
  42.  
  43. 			for($i=0;$i<$countCheck;$i++)
  44. 			{
  45. 				$del_id  = $checkbox[$i];
  46. 				$sql = "delete from test_mysql where id = $del_id";
  47. 				$result = mysql_query($sql, $dbconn);
  48. 			}
  49. 				if($result)
  50. 				{
  51. 					echo "successful delete";
  52. 				}
  53. 				else
  54. 				{
  55. 					echo "Error: ".mysql_error();
  56. 				}
  57. 		}
  58. 	?></table>
  59. </form></td></tr></table>

Now it can delete but my data dont show. I have attached the result of the code. SigH!
Reputation Points: 10
Solved Threads: 0
Newbie Poster
bear24 is offline Offline
9 posts
since Sep 2008
Permalink
Sep 18th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

This:
php Syntax (Toggle Plain Text)
  1. <?php while($row = mysql_fetch_array($sqlresult)){ ?>
Should be this:
php Syntax (Toggle Plain Text)
  1. <?php while($rows = mysql_fetch_assoc($sqlresult)){ ?>
Your variable was named $row and you called it later in the loop as $rows. I've always used mysql_fetch_assoc for loops like this, but I'm not sure if that makes a difference or not.
Last edited by MVied; Sep 18th, 2008 at 3:57 pm.
Reputation Points: 21
Solved Threads: 11
Junior Poster
MVied is offline Offline
111 posts
since Aug 2008
Permalink
Sep 20th, 2008
0

Re: Deleting multiple rows from mysql with checkbox

ok. Thanks. Thanks a lot!
Reputation Points: 10
Solved Threads: 0
Newbie Poster
bear24 is offline Offline
9 posts
since Sep 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

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: How do I strip HTML tags from user text box in a form?
Next Thread in PHP Forum Timeline: mail error





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


Follow us on Twitter


© 2011 DaniWeb® LLC