Page 1 of 1

Help with deleting from sql database

Posted: Thu May 20, 2010 3:22 pm
by dsjoes
i have this code below it shows the info i need but when i click a checkbox to delete the selected row and then click delete it just refreshes the page.
does anyone know what i need to do to fix this.
Thanks

Code: Select all

<?php
$host="Host"; // Host name
$username="user"; // Mysql username
$password="pass"; // Mysql password
$db_name="Targets"; // Database name
$tbl_name="Target"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

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

$count=mysql_num_rows($result);

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="delete" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="4" align="center" bgcolor="#FFFFFF"><strong>Targets</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>ID</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Target</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Comment</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['ID']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['Target']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['Comment']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_ID = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE ID='$del_ID'";
$result = mysql_query($sql);
}

// if successful redirect to delete.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete.php\">";
}
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 3:42 pm
by JakeJ
You have no action in the form. It doesn't do anything.

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 4:14 pm
by mikosiko
JakeJ wrote:ction in the form. It doesn't do anything.
that is incorrect.... even when is better practice to use $PHP_SELF global

main problem is that you are not using $_POST to get/process your form input

Code: Select all

// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_ID = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE ID='$del_ID'";
$result = mysql_query($sql);
}
you should write this part +- (just a pointer code... you must complete it)

Code: Select all

if (isset($_POST['delete'])) {
    --- then check if something was choose or not... same method... and only then use the result array
}
in addition you should move all that code to the beginning to better flow control

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 4:35 pm
by JakeJ
I didn't realize a blank action was supposed to be self-referring, but you're right, better to use php_self, more specifically, $_SERVER['PHP_SELF'];

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 4:38 pm
by _64k
This should work.

Code: Select all

<?php	
	
	$host="Host"; // Host name
	$username="user"; // Mysql username
	$password="pass"; // Mysql password
	$db_name="Targets"; // Database name
	$tbl_name="Target"; // Table name
	
	// Connect to server and select databse.
	mysql_connect("$host", "$username", "$password")or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");
	
	// Build SQL query
	if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name";
	else {
		$sql = "DELETE FROM $tbl_name WHERE";

		// add row id to where section
		for($i=0;$i<count($_POST['checkbox']);$i++){
			if($i != 0) $sql.= "AND ";
			$sql .= " ID='" . $_POST['checkbox'][$i] . "'";
		}
     }

      $result = mysql_query($sql);
      if(isset($_POST['delete']))  header('Location: delete.php'); // redirect
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="4" align="center" bgcolor="#FFFFFF"><strong>Targets</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>ID</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Target</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Comment</strong></td>
</tr>
<?php while($rows=mysql_fetch_array($result)){ ?>
	<tr>
		<td align="center" bgcolor="#FFFFFF">
			<input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>">
		</td>
		<td bgcolor="#FFFFFF"><? echo $rows['ID']; ?></td>
		<td bgcolor="#FFFFFF"><? echo $rows['Target']; ?></td>
		<td bgcolor="#FFFFFF"><? echo $rows['Comment']; ?></td>
	</tr>
	<tr>
	<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
	</tr>
<?php  } 
	mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>



Re: Help with deleting from sql database

Posted: Thu May 20, 2010 5:01 pm
by dsjoes
64k that script only shows the table as empty and the delete button is not there. it just shows the tables titles.
sorry i am new to all of this

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 5:11 pm
by _64k
edited my original code, sorry, the query was'nt being excuted if $_POST['delete'] was false.

also removed the error checking on the mysql delete query as mysql_query would cause a fatal error anyway.

edit:
jakej: main problem is that you are not using $_POST to get/process your form input
- I agree

your code see below. Was looping through $checkbox which had'nt been populated with $_POST['checkbox']. which you did'nt need to do to get it to work, you could of just looped through $_POST['checkbox'] directly, which is what my code does. probably worth noting without any validation of $_POST['checkbox'] it would be open SQL injection.

Code: Select all

// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_ID = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE ID='$del_ID'";
Jakej: in addition you should move all that code to the beginning to better flow control
- I agree

as you said you new to this, but a fundamental concept with PHP is that you want to separate your processing from your output, by moving the processing code to the beginning of the file, even going as far as moving it outside and using a require_once is good practice imo.

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 5:47 pm
by dsjoes
thank you that works.

Re: Help with deleting from sql database

Posted: Thu May 20, 2010 5:50 pm
by _64k
your welcome, make sure you take the time to understand how it works :D happy coding