943,912 Members | Top Members by Rank

Web Development > PHP > Deleting selections of Database tables
Ad:
  • PHP Discussion Thread
  • Marked Solved
  • Views: 1055
  • PHP RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Aug 9th, 2009
0

Deleting selections of Database tables

Expand Post »
I am having a problem using the script below. I want to be able to built a database table in html (using an 'id' to select which) and then be able to delete the rows selected by the checkboxes.

The building of the table works perfect. But when selecting row's and pressing delete the screen goes to blank and nothing has been deleted, the 'test' in the if($_POST['delete']) never appears.. i cant find my error! so i hope someone can help me!!

Gerald

The code:

PHP Syntax (Toggle Plain Text)
  1. <?php
  2. if ($_POST['submit']) {
  3. 	$id = $_POST['id'];
  4. 	$i = 0;
  5. 	include("verbinding.php");
  6.  
  7. 	mysql_connect("$dbhost", "$username", "$dbww")or die("cannot connect");
  8. 	mysql_select_db("$dbname")or die("cannot select DB");
  9.  
  10. 	$tabellen = mysql_list_tables("$dbname") or die(mysql_error());
  11.  
  12. 	while ($i < mysql_num_rows($tabellen)) {
  13.     		$t_name[$i] = mysql_tablename ($tabellen, $i);
  14. 		if ($id == $i) {
  15. 			$tbl_name = $t_name[$i];
  16.  
  17. 			$sql="SELECT * FROM $tbl_name";
  18. 			$result=mysql_query($sql);
  19.  
  20. 			$count=mysql_num_rows($result);
  21.  
  22. 			$fields_num = mysql_num_fields($result);
  23.  
  24. ?>
  25. <table width="400" border="0" cellspacing="1" cellpadding="0">
  26. 	<tr>
  27. 		<td>
  28. 		<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  29. 			<tr>
  30. 				<td bgcolor="#FFFFFF">&nbsp;</td>
  31. 				<td colspan="4" bgcolor="#FFFFFF"><strong>Table: <?php echo $t_name[$i]; ?></strong> </td>
  32. 			</tr>
  33. 			<tr>
  34.                                       <td bgcolor='#FFFFFF'>#</td>
  35. <?php
  36.    			for($k=0; $k<$fields_num; $k++) {
  37.          			$field = mysql_fetch_field($result);
  38.          			echo "<td bgcolor='#FFFFFF'>{$field->name}</td>";
  39.    			}
  40.    			while($row = mysql_fetch_row($result)) {
  41.        				echo "<tr>";
  42. ?>
  43. <td align="center" bgcolor="#FFFFFF"><form method="post" action="<?php echo $PHP_SELF;?>"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
  44. <?php
  45.        				foreach($row as $cell)
  46.            			echo "<td bgcolor='#FFFFFF'>$cell</td>";
  47.        				echo "</tr>\n";
  48.    			}
  49. ?>
  50. 			</tr>
  51. 			<tr>
  52. 				<td colspan="3" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
  53. 				<td colspan="2" align="center" bgcolor="#FFFFFF"><input name="back" type="submit" id="back" value="Back"></td></form>
  54. 			</tr>
  55. <?php
  56.  
  57. 			$checkbox = $_POST[checkbox];
  58. 			if($_POST['delete']){
  59. 				echo "test";
  60. 				for($i=0;$i<$count;$i++){
  61. 					$del_id = $checkbox[$i];
  62. 					$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
  63. 					$result = mysql_query($sql);
  64. 				}
  65. 				if($result){
  66. 					echo "<meta http-equiv=\"refresh\" content=\"0;URL=change.php\">";
  67. 				}
  68. 			}
  69. 			if($_POST['back']){
  70. 				echo "<meta http-equiv=\"refresh\" content=\"0;URL=admin.php\">";
  71. 			}
  72. 		}
  73. 	$i++;
  74. 	}
  75. mysql_close();
  76. }
  77. ?>
  78. 		</table>
  79. 		</td>
  80. 	</tr>
  81. </table>
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Gerald19 is offline Offline
12 posts
since Aug 2009
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

Here is your problem area:
PHP Syntax (Toggle Plain Text)
  1. $checkbox = $_POST[checkbox]; //Problem #1
  2. if($_POST['delete']){ //Problem #2
  3. echo "test"; //Nice Debugging But This Should Fix It So We'll Remove It
  4. for($i=0;$i<$count;$i++){
  5. $del_id = $checkbox[$i]; //Problem #3
  6. $sql = "DELETE FROM $tbl_name WHERE id='$del_id'"; //And to save a line We'll condense this
  7. $result = mysql_query($sql);

Problem #1
This line should be:
PHP Syntax (Toggle Plain Text)
  1. $checkbox = $_POST['checkbox[]'];
This is because the name of the checkbox is checkbox[] and $_POST['checkbox[]'] holds the value.

Problem #2
This line should be:
PHP Syntax (Toggle Plain Text)
  1. if($_POST['delete']=="Delete"){
This is because the submit button is named delete and when clicks sends the post variable $_POST['delete'] with the value "Delete".

Problem #3
You don't need this line. As I mentioned in Problem #1, the variable $checkbox will contain the id that you seek in this line.

Housekeeping
I always hate when people make a variable to pass to a mysql function. Why not just put it in the function like this:
PHP Syntax (Toggle Plain Text)
  1. $result = mysql_query("DELETE FROM {$tbl_name} WHERE id='".$del_id."'");
You should also know that your script is vulnerable to a MySQL Injection. So I would protect against that (See below I did it for you).

The End Result
So fixing all those lines would make the problem area look like this:
PHP Syntax (Toggle Plain Text)
  1. if($_POST['delete']) {
  2. $checkbox = $_POST[checkbox];
  3. for($i=0;$i<$count;$i++){
  4. mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($del_id)."'");

Try that out and see if it works. You also have a few other little things that are making your code longer. If you absolutely want to, post the corrected code and I'll revise it so that it will run with maximum ease.
Last edited by FlashCreations; Aug 10th, 2009 at 1:48 am.
Reputation Points: 47
Solved Threads: 47
Posting Whiz
FlashCreations is offline Offline
393 posts
since Sep 2008
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

First of all thank you for helping me out!

But I still cant make the code work properly, also I don't quite follow this line of your end result. ->
php Syntax (Toggle Plain Text)
  1. mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($del_id)."'");

I have tried a lot of things with your adjustments but I think the code never enters the if($_POST['delete']=="Delete") statement.

This is how I have impelented it right now, eventhough I know it's not the best way but like I said the main problem is (I think) that the code never enters the if statement to begin with.

php Syntax (Toggle Plain Text)
  1. if($_POST['delete']=="Delete") {
  2. $checkbox = $_POST['checkbox[]'];
  3. for($i=0;$i<$count;$i++) {
  4. $del_id = $checkbox[$i];
  5. $result = mysql_query("DELETE FROM $tbl_name WHERE id='.$del_id'") or die(mysql_error());  
  6. }
  7. if($result){
  8. echo "<meta http-equiv=\"refresh\" content=\"0;URL=change.php\">";
  9. }
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Gerald19 is offline Offline
12 posts
since Aug 2009
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

First off, Your Welcome!
About that line: I didn't realize that you used that result variable so here is the corrected version with explanation.
PHP Syntax (Toggle Plain Text)
  1. $result = mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($del_id)."'") or die("Query Error!");
First off, never echo the actual MySQL Error to a page visible to anyone. This could cause the integrity of your database to be sacrificed (I always test a script and if it doesn't work, run the problem queries through the PphMyAdmin Sql section). Next, the mysql_real_escape_string() is so that malicious users can't perform a MySQL Injection attack on your script (Looking at it now it seems pretty vulnerable). And the reason for the quotes is basic PHP. When you put a function result in a string you have to put a quote that started the string (In you case the ", double quote, from "DELETE) to tell PHP execute this and then a period, the function, and another period and quote. In your case you also need to include the MySQL ' and ' (single) quotes so that MySQL determines that $del_id .
Beyond that I just realized that your implementation with the checkboxes it incorrect. $_POST[] variables are not arrays, they are strings (or integers, etc.). So in this case you will need to change the code that creates the checkboxes. I also determined that their are problems way beyond the scope of this post. For one, you add a meta tag to a page that has content. (In PHP you can't add a header after you start the page (meaning after you type the body tag). This whole script needs a serious overhaul, and I'd be happy to do so, but I need to know the structure of the database you are using, what you want to accomplish it, and other relevant details. If you could provide me with those I will be able to make the fixes to this script to make it better.
Reputation Points: 47
Solved Threads: 47
Posting Whiz
FlashCreations is offline Offline
393 posts
since Sep 2008
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

I apoligize for suggesting this, but I noticed that you use 'echo "<meta http-equiv=\"refresh\" content=\"0;URL=change.php\">";' and 'echo "<meta http-equiv=\"refresh\" content=\"0;URL=admin.php\">";'. There is a better method of redirecting users it also has less over head I think on both sides of processing the web page/there is less typing involved which makes development easier. I thought I could pass this on for future reference for you.

As example:
php Syntax (Toggle Plain Text)
  1. header("Location: admin.php");

By the way have a great day.
Reputation Points: 11
Solved Threads: 2
Junior Poster in Training
matthewl is offline Offline
59 posts
since Aug 2009
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

Click to Expand / Collapse  Quote originally posted by matthewl ...
I apoligize for suggesting this, but I noticed that you use 'echo "<meta http-equiv=\"refresh\" content=\"0;URL=change.php\">";' and 'echo "<meta http-equiv=\"refresh\" content=\"0;URL=admin.php\">";'. There is a better method of redirecting users it also has less over head I think on both sides of processing the web page/there is less typing involved which makes development easier. I thought I could pass this on for future reference for you.

As example:
php Syntax (Toggle Plain Text)
  1. header("Location: admin.php");

By the way have a great day.
Matthewl beings up a good point. The only problem is that once you have placed content on a page (He has already echoed a table), neither the meta tag (which belongs in the head) nor the header() function (which only works before there is any text outputted) will work. This also made me realize that not only is this script incorrect in PHP, but also is not a valid HTML document (There is no HTML, HEAD, or BODY tags!).
Reputation Points: 47
Solved Threads: 47
Posting Whiz
FlashCreations is offline Offline
393 posts
since Sep 2008
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

I know this is improper, but you can call the header() anywhere on the page just like calling a meta in the body and header() never gives me any problems. In fact I do it all the time using header() in the body because I modularize my code quite often.

as example this works:
php Syntax (Toggle Plain Text)
  1. <!--
  2. To change this template, choose Tools | Templates
  3. and open the template in the editor.
  4. -->
  5. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  6. <html>
  7.     <head>
  8.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  9.         <title></title>
  10.     </head>
  11.     <body>
  12.         <table border="1">
  13.             <thead>
  14.                 <tr>
  15.                     <th>blah</th>
  16.                     <th>blah</th>
  17.                 </tr>
  18.             </thead>
  19.             <tbody>
  20.                 <tr>
  21.                     <td>blah</td>
  22.                     <td>blah</td>
  23.                 </tr>
  24.                 <tr>
  25.                     <td>blah</td>
  26.                     <td>blah</td>
  27.                 </tr>
  28.             </tbody>
  29.         </table>
  30.  
  31.         <?php
  32.         header("Location: index.php");
  33.         ?>
  34.     </body>
  35. </html>
Last edited by matthewl; Aug 10th, 2009 at 4:38 pm.
Reputation Points: 11
Solved Threads: 2
Junior Poster in Training
matthewl is offline Offline
59 posts
since Aug 2009
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

http://www.w3schools.com/php/func_http_header.asp
Quote originally posted by W3Schools ...
The header() function sends a raw HTTP header to a client.

It is important to notice that header() must be called before any actual output is sent (In PHP 4 and later, you can use output buffering to solve this problem):
PHP Syntax (Toggle Plain Text)
  1. <html>
  2. <?php
  3. // This results in an error.
  4. // The output above is before the header() call
  5. header('Location: http://www.example.com/');
  6. ?>
Last edited by FlashCreations; Aug 10th, 2009 at 4:38 pm.
Reputation Points: 47
Solved Threads: 47
Posting Whiz
FlashCreations is offline Offline
393 posts
since Sep 2008
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

Thanks again for your help!

I will explain my intensions more thoroughly. I have a website with e.g. a guestbook, guestbook entries will be stored in the guestbook table on the mysql database. This all works properly. Using PhpMyAdmin I would be able to delete a row from this table, but I want to be able to do this on the webpage. So I built a password protected area (admin.php) where all tables of the database are shown like this:

0 Guestbook
1 Some table
2 Some other table

Choose table: [ 'id' ]
'Submit'

Using the id of the table (0-1-2 in this case) you can select that table and that table will be built by change.php. Using the checkboxes selected rows must be deleted.

So everything works properly, except the deleting part..

here are all the files I use for this (upgraded a bit after your improvements),

php Syntax (Toggle Plain Text)
  1. <!-- verbinding.php -->
  2. <?php
  3.  $dbhost = "***";
  4.  $db = "***";
  5.  $dbname = "***";
  6.  $username = "***";
  7.  $dbww = "***";
  8.  ?>

html Syntax (Toggle Plain Text)
  1. <!-- admin.html -->
  2. <HTML>
  3. <HEAD>
  4. <TITLE>admin.html</TITLE>
  5. </HEAD>
  6. <BODY>
  7. Welkom op de admin.
  8. <br><br>
  9. A.u.b. inloggen:
  10. <form method=post action=admin.php>
  11. 	Username: <input type="text" name="name">
  12. 	Wachtwoord: <input type"password" name="password">
  13. 	<input type="submit" name="submit">
  14. </form>
  15. </BODY>
  16. </HTML>

php Syntax (Toggle Plain Text)
  1. <!-- admin.php -->
  2. <HTML>
  3. <HEAD>
  4. <TITLE>Admin.php</TITLE>
  5. </HEAD>
  6. <?php
  7. include("verbinding.php");
  8. if ($_POST['submit']) {
  9. $name = $_POST['name'];
  10. $password = $_POST['password'];
  11.  
  12. if (($name == $username) && ($password == $dbww)) {
  13. 	mysql_connect("$dbhost","$username","$dbww");
  14. 	echo "kies een tabel.<br>";
  15. 	show(); ?>
  16. <BODY>
  17. <form action=change.php method=post>
  18. 	<input type="text" name="id">
  19. 	<input type="submit" name="submit">
  20. </form>
  21.  
  22. <?php
  23. } else { ?>
  24.  
  25. Foute username en wachtwoord combinatie ingevuld, <a href="admin.html">probeer opnieuw</a> // wrong password/user combination
  26.  
  27. <?php }
  28. } 
  29.  
  30. function show()
  31. {
  32. $tabellen = mysql_list_tables("$dbname");
  33. $i=0;
  34. while ($i < mysql_num_rows($tabellen)){
  35.     $t_name[$i] = mysql_tablename ($tabellen, $i);
  36.     echo $i, $t_name[$i]. "<br>";
  37.     $i++;
  38. }
  39. }?>
  40. </BODY>
  41. </HTML>

php Syntax (Toggle Plain Text)
  1. <!-- change.php -->
  2. <?php
  3.  
  4. if ($_POST['submit']) {
  5. 	$id = $_POST['id'];
  6. 	$i = 0;
  7. 	include("verbinding.php");
  8.  
  9. 	mysql_connect("$dbhost", "$username", "$dbww")or die("cannot connect");
  10. 	mysql_select_db("$dbname")or die("cannot select DB");
  11.  
  12. 	$tabellen = mysql_list_tables("$dbname") or die("cannot list tables");
  13.  
  14. 	while ($i < mysql_num_rows($tabellen)) {
  15.     		$t_name[$i] = mysql_tablename ($tabellen, $i);
  16. 		if ($id == $i) {
  17. 			$tbl_name = $t_name[$i];
  18.  
  19. 			$sql="SELECT * FROM $tbl_name";
  20. 			$result=mysql_query($sql);
  21.  
  22. 			$count=mysql_num_rows($result);
  23.  
  24. 			$fields_num = mysql_num_fields($result);
  25.  
  26. ?>
  27. <HTML>
  28. <HEAD>
  29. <TITLE>Change.php</TITLE>
  30. </HEAD>
  31. <BODY>
  32. <table width="400" border="0" cellspacing="1" cellpadding="0">
  33. 	<tr>
  34. 		<td>
  35. 		<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
  36. 			<tr>
  37. 				<td bgcolor="#FFFFFF">&nbsp;</td>
  38. 				<td colspan="4" bgcolor="#FFFFFF"><strong>Table: <?php echo $t_name[$i]; ?></strong> </td>
  39. 			</tr>
  40. 			<tr>
  41. <?php
  42. 			echo "<td bgcolor='#FFFFFF'>#</td>";
  43.    			for($k=0; $k<$fields_num; $k++) {
  44.          			$field = mysql_fetch_field($result);
  45.  
  46.          			echo "<td bgcolor='#FFFFFF'>{$field->name}</td>";
  47.    			}
  48.    			while($row = mysql_fetch_row($result)) {
  49.        				echo "<tr>";
  50. ?>
  51. <td align="center" bgcolor="#FFFFFF"><form method="post" action="<?php echo $PHP_SELF;?>"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
  52. <?php
  53.        				foreach($row as $cell)
  54.            			echo "<td bgcolor='#FFFFFF'>$cell</td>";
  55.        				echo "</tr>\n";
  56.    			}
  57. ?>
  58. 			</tr>
  59. 			<tr>
  60. 				<td colspan="3" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
  61. 				<td colspan="2" align="center" bgcolor="#FFFFFF"><input name="back" type="submit" id="back" value="Back"></td></form>
  62. 			</tr>
  63. <?php
  64. 			if($_POST['delete']=="Delete") {
  65. 				$checkbox = $_POST['checkbox[]'];
  66. 				for($i=0;$i<$count;$i++) {
  67. 					$del_id = $checkbox[$i];
  68. 					$result = mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($del_id)."'") or die("Query Error!");
  69. 				}
  70. 				if($result){
  71. 					//Here a code to reload change.php
  72. 				} 
  73. 			}
  74. 			if($_POST['back']=="Back"){
  75. 				//Here a code to reload admin.php
  76. 			}
  77. 		}
  78. 	$i++;
  79. 	}
  80. mysql_close();
  81. }
  82. ?>
  83. 		</table>
  84. 		</td>
  85. 	</tr>
  86. </table>
  87. </BODY>
  88. </HTML>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Gerald19 is offline Offline
12 posts
since Aug 2009
Permalink
Aug 10th, 2009
0

Re: Deleting selections of Database tables

Ok this makes much more sense now!!
Your problem is this: Everything is correct with the checkboxes. Your checkbox method does work, but your PHP script is not receiving these values correctly. I have fixed the problem area so that it should now work!
PHP Syntax (Toggle Plain Text)
  1. if($_POST['delete']=="Delete") {
  2.   $checkbox = $_POST['checkbox'];
  3.   if($checkbox) {
  4.     foreach($checkbox as $box) {
  5.       $result = mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($box)."'") or die("Query Error!");
  6.     }
  7.   }
  8.   if($result){
  9.     //Here a code to reload change.php
  10.   }
  11. }
Also as a note, the changing file doesn't require admin authentication. If anyone knew the location of this change file they could access it and delete rows in tables without loging into the admin panel you have built. I would suggest checking the admin username and password before deleting.
Last edited by FlashCreations; Aug 10th, 2009 at 7:50 pm.
Reputation Points: 47
Solved Threads: 47
Posting Whiz
FlashCreations is offline Offline
393 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: Dynamic Login
Next Thread in PHP Forum Timeline: PHP + Basic Joomla





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


Follow us on Twitter


© 2011 DaniWeb® LLC