Help with deleting from sql database

dsjoes · June 5, 2010

i could delete from the database before 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

<?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 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) withe the script in

<!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&#058;this.src="Buttons/home1.png"' onmouseout='javascript&#058;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&#058;this.src="Buttons/clan info1.png"' onmouseout='javascript&#058;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&#058;this.src="Buttons/faq1.png"' onmouseout='javascript&#058;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&#058;this.src="Buttons/admin1.png"' onmouseout='javascript&#058;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&#058;this.src="Buttons/logout1.png"' onmouseout='javascript&#058;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="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 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> </h4>
    <!-- end .sidebar2 --></div>
  <div class="footer">
    <p> </p>
    <!-- end .footer --></div>
  <!-- end .container --></div>
</body>
</html>

Pikachu2000 · June 5, 2010

And what, if any, errors are you getting displayed or logged (you are logging errors, right)? What is the script doing/not doing that is should or should not be doing?

dsjoes · June 6, 2010

i don't get any error messages it just dosn't delete the selected row it creates a new blank row every time i try to delete one

Pikachu2000 · June 6, 2010

The query for the DELETE looks off to me. Have you tried echoing it to make sure it contains the values you think it should contain?

dsjoes · June 6, 2010

no i haven't how do i do it. :confused:

Pikachu2000 · June 7, 2010

Right after the $sql variable is assigned its value, just put in an: echo $sql;

dsjoes · June 7, 2010

i don't get an error message

i put it after this line

$sql = "DELETE FROM $tbl_name WHERE";

Pikachu2000 · June 7, 2010

At that point, the whole query string hasn't been built. You'd need to put it right before this line, and it should out the string to the screen so you can see what it actually contains.

$result = mysql_query($sql);

dsjoes · June 8, 2010

At that point, the whole query string hasn't been built. You'd need to put it right before this line, and it should out the string to the screen so you can see what it actually contains.
$result = mysql_query($sql);

that is all ready in the code it displays everything in the table

it is in this bit at the bottom

     // 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
?>

if i put echo $sql; before $result = mysql_query($sql); then i get the below message above the table

SELECT * FROM Target

Pikachu2000 · June 8, 2010

if i put echo $sql; before $result = mysql_query($sql); then i get the below message above the table

SELECT * FROM Target

Are you sure it isn't "SELECT * from Target WHERE"? In any event, I don't think the for() loop is working in that query in the manner it was intended. I need to go get some work done, but I'll have a closer look at it later today.

mcdsoftware · June 8, 2010

Try this and let me know if it works

$sql = "DELETE FROM $tbl_name WHERE 1=1 ";

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

dsjoes · June 8, 2010

no it dosen't work it just does the same as it does before i changed it. it creates an empty row after i click delete

dsjoes · June 11, 2010

bump

dsjoes · June 14, 2010

anyone :confused:

dsjoes · June 15, 2010

TO THE TOP

dsjoes · June 17, 2010

any one :confused:

using

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

i got the following message

This is the SQL : SELECT * FROM Targetresource(3) of type (mysql result)

Pikachu2000 · June 17, 2010

I had lost this thread in the shuffle, but now that I look at this again, my gut reaction is that you need to rewrite this script. It could be simplified and made more efficient with less code. I haven't tested this, and this likely won't be copy and paste-able, but something along these lines should work better for you.

<?php
if( isset($_POST['submit']) && $_POST['submit'] == 'Delete' && is_array($_POST['checkbox']) ) {
$id = $_POST['checkbox'];
foreach( $id as $val ) {
	$val = (int) $val; // cast as an integer, since the field is expected to be an integer anyhow.
	$query = "DELETE FROM table WHERE id = " . $val . " LIMIT 1";
}
} else {

echo "<form action=\"\" method=\"POST\">";
$query = "SELECT ID, Target, Comment FROM " .  $tbl_name; // this will pull EVERY record from a table, unless you add a WHERE clause.
$result = mysql_query($query);
while( $array = mysql_fetch_assoc($result) ) {
	echo "Target: " . $array['Target'] . " Comment: " . $array['Comment'] . " Select to delete: <input type=\"checkbox\" name=\"checkbox[]\" value=\"" . $array['ID'] . "\"><br />";
}
echo "<input type =\"submit\" name=\"submit\" value=\"Delete\"><input type=\"submit\" name=\"submit\" value=\"Cancel\"></form>";
}
?>

dsjoes · June 18, 2010

it just does the same. when i click a checkbox and delete it just creates an empty row and same if i press cancel

siwelis · June 18, 2010

Are the checkbox ID's what you expect them to be?

Just before this:

$result = mysql_query($sql);

I'd to echo $sql to see if it's what you expect it to be.

dsjoes · June 18, 2010

Are the checkbox ID's what you expect them to be?

Just before this:

$result = mysql_query($sql);

I'd to echo $sql to see if it's what you expect it to be.

i get this message

SELECT * FROM Target ORDER BY ID

siwelis · June 18, 2010

I'm sorry I didn't realize you had already posted the SQL statement. Its definitely sounds wrong if it's supposed to be deleting though.

It sounds like it's not getting the right $_POST['delete'] information. put this in your code to get all POST's and see if they're whta you expect...

while (list ($key,$val) = each ($_POST)) {
echo "\$$key = $val";
echo "<br>";
}

dsjoes · June 18, 2010

I'm sorry I didn't realize you had already posted the SQL statement. Its definitely sounds wrong if it's supposed to be deleting though.

It sounds like it's not getting the right $_POST['delete'] information. put this in your code to get all POST's and see if they're whta you expect...
while (list ($key,$val) = each ($_POST)) {
echo "\$$key = $val";
echo "<br>";
}

where do i put this code because i can not get it to display a message

siwelis · June 18, 2010

Put it right where you did an echo of the sql.

If it's not echoing anything, there are probably no POST values being posted from a form at all.

Try replacing your submit button code with this:

I don't think values are submitted the way you were thinking with the submit button, but I could be wrong.

I assume the name of this file is insert.php.

Pikachu2000 · June 18, 2010

it just does the same. when i click a checkbox and delete it just creates an empty row and same if i press cancel

That's impossible. There is no INSERT or UPDATE query anywhere in the script. It's even more impossible since I just noticed a typo in the code, causing the query to never actually execute. Are you sure there isn't more code related to this that you haven't posted?

<?php
if( isset($_POST['submit']) && $_POST['submit'] == 'Delete' && is_array($_POST['checkbox']) ) {
   $id = $_POST['checkbox'];
   foreach( $id as $val ) {
      $val = (int) $val; // cast as an integer, since the field is expected to be an integer anyhow.
      $query = "DELETE FROM table WHERE id = " . $val . " LIMIT 1";
                mysql_query($query); // This line was missing.
   }
} else {

   echo "<form action=\"\" method=\"POST\">";
   $query = "SELECT ID, Target, Comment FROM " .  $tbl_name; // this will pull EVERY record from a table, unless you add a WHERE clause.
   $result = mysql_query($query);
   while( $array = mysql_fetch_assoc($result) ) {
      echo "Target: " . $array['Target'] . " Comment: " . $array['Comment'] . " Select to delete: <input type=\"checkbox\" name=\"checkbox[]\" value=\"" . $array['ID'] . "\"><br />";
   }
   echo "<input type =\"submit\" name=\"submit\" value=\"Delete\"><input type=\"submit\" name=\"submit\" value=\"Cancel\"></form>";
}
?>

dsjoes · June 18, 2010

Pikachu2000 that script refreshes the page and it removes all the info until i click on another page and back on to it and nothing has been deleted.

This is the whole of the original script for the table with the delete button.

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

Sign In

Help with deleting from sql database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information