Deleting selections of Database tables

[Gerald19]

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) never appears.. i cant find my error! so i hope someone can help me!!

Gerald

The code:

<?php
if ($_POST['submit']) {
	$id = $_POST['id'];
	$i = 0;
	include("verbinding.php");

	mysql_connect("$dbhost", "$username", "$dbww")or die("cannot connect");
	mysql_select_db("$dbname")or die("cannot select DB");

	$tabellen = mysql_list_tables("$dbname") or die(mysql_error());
	
	while ($i < mysql_num_rows($tabellen)) {
    		$t_name[$i] = mysql_tablename ($tabellen, $i);
		if ($id == $i) {
			$tbl_name = $t_name[$i];

			$sql="SELECT * FROM $tbl_name";
			$result=mysql_query($sql);

			$count=mysql_num_rows($result);

			$fields_num = mysql_num_fields($result);

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
	<tr>
		<td>
		<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
			<tr>
				<td bgcolor="#FFFFFF">&nbsp;</td>
				<td colspan="4" bgcolor="#FFFFFF"><strong>Table: <?php echo $t_name[$i]; ?></strong> </td>
			</tr>
			<tr>
                                      <td bgcolor='#FFFFFF'>#</td>
<?php
   			for($k=0; $k<$fields_num; $k++) {
         			$field = mysql_fetch_field($result);
         			echo "<td bgcolor='#FFFFFF'>{$field->name}</td>";
   			}
   			while($row = mysql_fetch_row($result)) {
       				echo "<tr>";
?>
<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>
<?php
       				foreach($row as $cell)
           			echo "<td bgcolor='#FFFFFF'>$cell</td>";
       				echo "</tr>\n";
   			}
?>
			</tr>
			<tr>
				<td colspan="3" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
				<td colspan="2" align="center" bgcolor="#FFFFFF"><input name="back" type="submit" id="back" value="Back"></td></form>
			</tr>
<?php

			$checkbox = $_POST[checkbox];
			if($_POST['delete']){
				echo "test";
				for($i=0;$i<$count;$i++){
					$del_id = $checkbox[$i];
					$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
					$result = mysql_query($sql);
				}
				if($result){
					echo "<meta http-equiv=\"refresh\" content=\"0;URL=change.php\">";
				}
			}
			if($_POST['back']){
				echo "<meta http-equiv=\"refresh\" content=\"0;URL=admin.php\">";
			}
		}
	$i++;
	}
mysql_close();
}
?>
		</table>
		</td>
	</tr>
</table>

[FlashCreations]

Here is your problem area:

$checkbox = $_POST[checkbox]; //Problem #1
if($_POST['delete']){ //Problem #2
echo "test"; //Nice Debugging But This Should Fix It So We'll Remove It
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i]; //Problem #3
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'"; //And to save a line We'll condense this
$result = mysql_query($sql);

Problem #1
This line should be:

$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:

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:

$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:

if($_POST['delete']) {
$checkbox = $_POST[checkbox];
for($i=0;$i<$count;$i++){
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.

[Gerald19]

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. ->

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") 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.

if($_POST['delete']=="Delete") {
$checkbox = $_POST['checkbox[]'];
for($i=0;$i<$count;$i++) {
$del_id = $checkbox[$i];
$result = mysql_query("DELETE FROM $tbl_name WHERE id='.$del_id'") or die(mysql_error());  
}
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=change.php\">";
}

[FlashCreations]

First off, Your Welcome! :D
About that line: I didn't realize that you used that result variable so here is the corrected version with explanation.

$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.

[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:

header("Location: admin.php");

By the way have a great day. :)

[FlashCreations]

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:

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!).

[matthewl]

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:

<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <table border="1">
            <thead>
                <tr>
                    <th>blah</th>
                    <th>blah</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>blah</td>
                    <td>blah</td>
                </tr>
                <tr>
                    <td>blah</td>
                    <td>blah</td>
                </tr>
            </tbody>
        </table>

        <?php
        header("Location: index.php");
        ?>
    </body>
</html>

[FlashCreations]

http://www.w3schools.com/php/func_http_header.asp

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):

<html>
<?php
// This results in an error.
// The output above is before the header() call
header('Location: http://www.example.com/');
?>

[Gerald19]

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),

<!-- verbinding.php -->
<?php
 $dbhost = "***";
 $db = "***";
 $dbname = "***";
 $username = "***";
 $dbww = "***";
 ?>

<!-- admin.html -->
<HTML>
<HEAD>
<TITLE>admin.html</TITLE>
</HEAD>
<BODY>
Welkom op de admin.
<br><br>
A.u.b. inloggen:
<form method=post action=admin.php>
	Username: <input type="text" name="name">
	Wachtwoord: <input type"password" name="password">
	<input type="submit" name="submit">
</form>
</BODY>
</HTML>

<!-- admin.php -->
<HTML>
<HEAD>
<TITLE>Admin.php</TITLE>
</HEAD>
<?php
include("verbinding.php");
if ($_POST['submit']) {
$name = $_POST['name'];
$password = $_POST['password'];

if (($name == $username) && ($password == $dbww)) {
	mysql_connect("$dbhost","$username","$dbww");
	echo "kies een tabel.<br>";
	show(); ?>
<BODY>
<form action=change.php method=post>
	<input type="text" name="id">
	<input type="submit" name="submit">
</form>

<?php
} else { ?>

Foute username en wachtwoord combinatie ingevuld, <a href="admin.html">probeer opnieuw</a> // wrong password/user combination

<?php }
} 

function show()
{
$tabellen = mysql_list_tables("$dbname");
$i=0;
while ($i < mysql_num_rows($tabellen)){
    $t_name[$i] = mysql_tablename ($tabellen, $i);
    echo $i, $t_name[$i]. "<br>";
    $i++;
}
}?>
</BODY>
</HTML>

<!-- change.php -->
<?php

if ($_POST['submit']) {
	$id = $_POST['id'];
	$i = 0;
	include("verbinding.php");

	mysql_connect("$dbhost", "$username", "$dbww")or die("cannot connect");
	mysql_select_db("$dbname")or die("cannot select DB");

	$tabellen = mysql_list_tables("$dbname") or die("cannot list tables");
	
	while ($i < mysql_num_rows($tabellen)) {
    		$t_name[$i] = mysql_tablename ($tabellen, $i);
		if ($id == $i) {
			$tbl_name = $t_name[$i];

			$sql="SELECT * FROM $tbl_name";
			$result=mysql_query($sql);

			$count=mysql_num_rows($result);

			$fields_num = mysql_num_fields($result);

?>
<HTML>
<HEAD>
<TITLE>Change.php</TITLE>
</HEAD>
<BODY>
<table width="400" border="0" cellspacing="1" cellpadding="0">
	<tr>
		<td>
		<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
			<tr>
				<td bgcolor="#FFFFFF">&nbsp;</td>
				<td colspan="4" bgcolor="#FFFFFF"><strong>Table: <?php echo $t_name[$i]; ?></strong> </td>
			</tr>
			<tr>
<?php
			echo "<td bgcolor='#FFFFFF'>#</td>";
   			for($k=0; $k<$fields_num; $k++) {
         			$field = mysql_fetch_field($result);
				
         			echo "<td bgcolor='#FFFFFF'>{$field->name}</td>";
   			}
   			while($row = mysql_fetch_row($result)) {
       				echo "<tr>";
?>
<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>
<?php
       				foreach($row as $cell)
           			echo "<td bgcolor='#FFFFFF'>$cell</td>";
       				echo "</tr>\n";
   			}
?>
			</tr>
			<tr>
				<td colspan="3" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
				<td colspan="2" align="center" bgcolor="#FFFFFF"><input name="back" type="submit" id="back" value="Back"></td></form>
			</tr>
<?php
			if($_POST['delete']=="Delete") {
				$checkbox = $_POST['checkbox[]'];
				for($i=0;$i<$count;$i++) {
					$del_id = $checkbox[$i];
					$result = mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($del_id)."'") or die("Query Error!");
				}
				if($result){
					//Here a code to reload change.php
				} 
			}
			if($_POST['back']=="Back"){
				//Here a code to reload admin.php
			}
		}
	$i++;
	}
mysql_close();
}
?>
		</table>
		</td>
	</tr>
</table>
</BODY>
</HTML>

[FlashCreations]

Ok this makes much more sense now!! :D
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!

if($_POST['delete']=="Delete") {
  $checkbox = $_POST['checkbox'];
  if($checkbox) {
    foreach($checkbox as $box) {
      $result = mysql_query("DELETE FROM $tbl_name WHERE id='".mysql_real_escape_string($box)."'") or die("Query Error!");
    }
  }
  if($result){
    //Here a code to reload change.php
  }
}

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.

[Gerald19]

Thanks again for your tips! I will take them into account.

for the code, at first i still wasn't able to make it work. But after i removed the first

if ($_POST['submit']) {

statement in change.php and in stead of that used

<a href="changeguestbook.php">Guestbook</a>
<a href="changesometable.php">Some table</a>

in admin.php so that the whole 'selecting the right table' alogorithm can be removed.

It is a bit less delinquent, but it seems to work.. if you can explain why I cant use 2 'if($_POST[])' statements like this (because I am really beginning to think thats also a problem) it would help me a lot!

I will mark this thread solved however, because this will do for me.

Thanks for helping out!