Help with deleting from sql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Help with deleting from sql database

Post by dsjoes »

i could delete from the database before with help form some people on here but i have had to change the site because it would not display in internet explorer correctly.
this is the script that lets me select and delete from the database

Code: Select all

    <?php
$host="host"; // Host name 
$username="user"; // Mysql username 
$password="pass"; // Mysql password 
$db_name="KAW"; // 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 ORDER BY ID";
        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: admin.php'); // redirect
?>
<table align="center" 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 colspan="4" align="center" bgcolor="#000000"><strong>Targets</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#000000"><strong>Select</strong></td>
<td align="center" bgcolor="#000000"><strong>ID</strong></td>
<td align="center" bgcolor="#000000"><strong>Target</strong></td>
<td align="center" bgcolor="#000000"><strong>Comment</strong></td>
</tr>
<?php while($rows=mysql_fetch_array($result)){ ?>
        <tr>
                <td align="center" bgcolor="#000000">
                        <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>">
                </td>
                <td bgcolor="#000000"><? echo $rows['ID']; ?></td>
                <td bgcolor="#000000"><? echo $rows['Target']; ?></td>
                <td bgcolor="#000000"><? echo $rows['Comment']; ?></td>
        </tr>
        <tr>
        <td colspan="5" align="center" bgcolor="#000000"><input name="delete" type="submit" id="delete" value="Delete"></td>
        </tr>
<?php  }
        mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

and this is the web page (php)

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- TemplateBeginEditable name="doctitle" -->
<title>Admin</title>
<!-- TemplateEndEditable -->
<!-- TemplateBeginEditable name="head" -->
<!-- TemplateEndEditable -->
<link href="adminsettings.css" rel="stylesheet" type="text/css" />
</head>

<body>

<div class="container">
  <div class="header"><p><?php
date_default_timezone_set('Europe/London');
$today = date("l dS \of F Y h:i A (T)");
echo $today;
?></p><center><p><img src="pics/logo.png" alt="Logo" name="Insert_logo" width="540" height="196" id="Insert_logo" style="background: #000000; display:block;" /></p></center>
    <!-- end .header --></div>
  <div class="sidebar1">
    <ul class="nav">
      <li><a href="index.php"><img title='Generated button' src='Buttons/home.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/home1.png"' onmouseout='javascript:this.src="Buttons/home.png"' /></a></li>
      <li><a href="claninfo.php"><img title='Generated button' src='Buttons/clan info.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/clan info1.png"' onmouseout='javascript:this.src="Buttons/clan info.png"' /></a></li>
      <li><a href="faq.php"><img title='Generated button' src='Buttons/faq.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/faq1.png"' onmouseout='javascript:this.src="Buttons/faq.png"' /></a></li>
      <li><a href="admin.php"><img title='Generated button' src='Buttons/admin.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/admin1.png"' onmouseout='javascript:this.src="Buttons/admin.png"' /></a></li>
      <li><a href="password_protect.php?logout=1"><img title='Generated button' src='Buttons/logout.png' style="border-style: none" onmouseover='javascript:this.src="Buttons/logout1.png"' onmouseout='javascript:this.src="Buttons/logout.png"' /></a></li>
    </ul>
    <p>The admin page logs you out automatically after 30 minutes.</p>
    <!-- end .sidebar1 --></div>
  <div class="content">
    <center><h1>Admin</h1>
        <p>Enter the targets name and comment here.</p> 
    <p><form action="insert.php" method="post">
Target: <input type="text" name="Target" />
Comment: <input type="text" name="Comment" />
<input type="submit" /></p>
    <p>To delete a target click on the check box and then on delete.</p>
    <?php
$host="host"; // Host name 
$username="user"; // Mysql username 
$password="pass"; // Mysql password 
$db_name="KAW"; // 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 ORDER BY ID";
        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: admin.php'); // redirect
?>
<table align="center" 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 colspan="4" align="center" bgcolor="#000000"><strong>Targets</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#000000"><strong>Select</strong></td>
<td align="center" bgcolor="#000000"><strong>ID</strong></td>
<td align="center" bgcolor="#000000"><strong>Target</strong></td>
<td align="center" bgcolor="#000000"><strong>Comment</strong></td>
</tr>
<?php while($rows=mysql_fetch_array($result)){ ?>
        <tr>
                <td align="center" bgcolor="#000000">
                        <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>">
                </td>
                <td bgcolor="#000000"><? echo $rows['ID']; ?></td>
                <td bgcolor="#000000"><? echo $rows['Target']; ?></td>
                <td bgcolor="#000000"><? echo $rows['Comment']; ?></td>
        </tr>
        <tr>
        <td colspan="5" align="center" bgcolor="#000000"><input name="delete" type="submit" id="delete" value="Delete"></td>
        </tr>
<?php  }
        mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>
</p>
    <!-- end .content --></div>
  <div class="sidebar2">
    <h4>&nbsp;</h4>
    <!-- end .sidebar2 --></div>
  <div class="footer">
    <p>&nbsp;</p>
    <!-- end .footer --></div>
  <!-- end .container --></div>
</body>
</html>
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Help with deleting from sql database

Post by Jonah Bron »

What doesn't work? Do you get an error message, or does it just not do it? But, looking at your code, I think you need to replace AND with OR in your SQL.
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

the deletion part of the script it just creates a new blank row everytime i try to delete a row. it worked fine before i had to change the site design.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Help with deleting from sql database

Post by Jonah Bron »

Try echoing the value of mysql_error() after you execute the query. Also, don't forget to clean any variables that you put into your SQL with mysql_real_escape_string().
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

where do i put them because i carnt get them to work

thanks
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Help with deleting from sql database

Post by Jonah Bron »

Put

Code: Select all

echo mysql_error();
right below

Code: Select all

$result = mysql_query($sql);
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

i didn't get an error message
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Help with deleting from sql database

Post by Eran »

Code: Select all

var_dump($result);
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

var_dump($result);
that says null above the table when the page loads and dosent change when i try to delete a row
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Help with deleting from sql database

Post by Eran »

Post your code again with the placement of the var_dump() please
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

Code: Select all

  
<?php  
       
$host="host"; // Host name 

$username="user"; // Mysql username 

$password="pass"; // Mysql password 

$db_name="Kaw"; // 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] . "'";
                }
     }
var_dump($result);
       $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="#000000">&nbsp;</td>
<td colspan="4" align="center" bgcolor="#000000"><strong>Targets</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#000000">Select</td>
<td align="center" bgcolor="#000000"><strong>ID</strong></td>
<td align="center" bgcolor="#000000"><strong>Target</strong></td>
<td align="center" bgcolor="#000000"><strong>Comment</strong></td>
</tr>
<?php while($rows=mysql_fetch_array($result)){ ?>
        <tr>
                <td align="center" bgcolor="#000000">
                        <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['ID']; ?>">
                </td>
                <td bgcolor="#000000"><? echo $rows['ID']; ?></td>
                <td bgcolor="#000000"><? echo $rows['Target']; ?></td>
                <td bgcolor="#000000"><? echo $rows['Comment']; ?></td>
        </tr>
        <tr>
        <td colspan="5" align="center" bgcolor="#000000"><input name="delete" type="submit" id="delete" value="Delete"></td>
        </tr>
<?php  }
        mysql_close();
?>
</table>
</form>
</td>
</tr>
</table> </p>
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

bump
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

anyone
dsjoes
Forum Commoner
Posts: 41
Joined: Thu May 20, 2010 3:15 pm

Re: Help with deleting from sql database

Post by dsjoes »

TO THE TOP
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Help with deleting from sql database

Post by mikosiko »

try this changes to debug your problem (you are wasting your time bumping your post instead of testing alternatives) :

Code: Select all

 echo "This is the SQL : " . $sql;  // to check if your sql sentence is ok
 $result = mysql_query($sql) or die(mysql_error()); // to control errors
 var_dump($result);
Post Reply