deleting a row in database

lilRachie

I'm trying to delete rows in my database table. Here is my code
delete.php

<?
include('ddelete.php');
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><link href="style.css" rel="stylesheet" type="text/css"></head><body marginheight="0" marginwidth="0" topmargin="0" leftmargin="0" leftmargin="0" rightmargin="0">
<p>
<table width="300" border="0" align="center" height="30"><tr>
<td align="center"><a href="index.php">Add An Entry</a></td>
<td align="center">Delete An Entry</td>
</tr></table>
<form method="POST" action="">
<table border=1 CELLPADDING=1 CELLSPACING=0 align=center width="700">
<?
include('table.php');
?>
<tr><td align="center" colspan="4"><input type=submit value="Delete Channels" name="Delete"></td></tr></table></form>
</body></html>
<?
}
?>

table.php

<?
include('connect.php');

//get table info
$query = "SELECT * FROM `objects` ORDER BY `order`";
if($result = mysql_query($query)) {
    while ($row = mysql_fetch_array ($result)) {
        //print table info
        print "<tr><td class=\"border2\" align=center height=30><input type='checkbox' name=" . $row["order"] . " value=" . $row["order"] . "></td><td class=\"border2\" align=center height=30>" . $row["order"] . "</td><td class=\"border2\" align=center height=30>". $row["name"] .  "</td><td class=\"border2\" align=center height=30>" . $row["descr"] . "</td></tr>";
    }// end while
}// end if

?>

ddelete.php

<?PHP
include('connect.php');

if (isset($_POST["Delete"])) // if it's not set, do nothing.
{
$delete = $_POST["name"];
        $sql = 'DELETE FROM `objects` WHERE `order` = $delete';
        echo $sql;

}else{

}
?>

I'm getting this for the echo $sql
DELETE FROM objects WHERE order = $delete

I'm sure it's something dumb. TIA

jkurrle

I believe "order" is a keyword. You may need to specify it as "tablename.order"

lilRachie

Nope
Still empty
DELETE FROM objects WHERE objects.order = $delete

jkurrle

It should be 'objects.order', not 'objects'.'order'

lilRachie

Nope its not deleting and still saying
DELETE FROM objects WHERE objects.order = $delete

bpat1434

Question 1: Why do you have a closing "}" in your main script when there isn't an opening "{"? Seems like a parse-error waiting to happen to me 🙁

Okay, so since order is in fact a MySQL keyword what you had initially in your first set of code is fine 😉 So you can go back to that.

Your problem is the type of quotes you're using. Single quotes ( ' ) contain strings that the PHP engine will not parse. So variables won't automatically be replaced with their values. So when you try to write your sql string to the variable $sql, you're writing the literal string $delete instead of the "interpreted" value of the variable $delete. This is why 99.9% of SQL strings are written with double quotes ( " ) instead of single-quotes.

So if you do this:

$sql = "DELETE FROM `objects` WHERE `order` = $delete";

Then echo out $sql, you'll be in business 😉

Just a word of caution. You should always make sure what the user-input you receive is a form in which you expect it. Since it's an order, I'd expect it to be a numerical value. So the functions [man]int_val/man, [man]ctype_digit/man would help you get the integer value and make sure it's a digit respectively.

If you're dealing with a string (like a unique order name for a user) you should use [man]mysql_real_escape_string/man to help ensure that special MySQL characters are escaped and arbitrary SQL statements aren't run on your database 😉

EDIT: If you want to delete the row, you do need to run [man]mysql_query/man with that sql string as well, but I'm sure you know that 😉

EDIT II:

bpat1434 wrote:
Okay, so since order is in fact a MySQL keyword what you had initially in your first set of code is fine. So you can go back to that.

Just to clarify for anyone else who is not aware, using back-ticks ( ` ) with column names should be common practice. The back-ticks actually tell MySQL that the string between the ticks is not to be treated as a reserved word (reserved words being words like ORDER, SELECT, TIME, NOW, DATE, etc.).

Secondly, when using table-names and column names, typically the table-name is not encapsulated with quotes, and the column name is still surrounded by quotes. For example:
tablename.column

lilRachie

delete.php

<?
include('ddelete.php');
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><link href="style.css" rel="stylesheet" type="text/css"></head><body marginheight="0" marginwidth="0" topmargin="0" leftmargin="0" leftmargin="0" rightmargin="0"><p>
<table width="300" border="0" align="center" height="30"><tr>
<td align="center"><a href="index.php">Add An Entry</a></td>
<td align="center">Delete An Entry</td>
</tr></table>
<form method="POST" action="">
<table border=1 CELLPADDING=1 CELLSPACING=0 align=center width="700">
<?
include('table.php');
?>
<tr><td align="center" colspan="4"><input type=submit value="Delete Channels" name="Delete"></td></tr></table></form>
</body></html>

table.php

<?
include('connect.php');

//get table info
$query = "SELECT * FROM `objects` ORDER BY `order`";
if($result = mysql_query($query)) {
    while ($row = mysql_fetch_array ($result)) {
        //print table info
        print "<tr><td class=\"border2\" align=center height=30><input type='checkbox' name=" . $row["order"] . " value=" . $row["order"] . "></td><td class=\"border2\" align=center height=30>" . $row["order"] . "</td><td class=\"border2\" align=center height=30>". $row["name"] .  "</td><td class=\"border2\" align=center height=30>" . $row["descr"] . "</td></tr>";
    }// end while
}// end if

?>

ddelete.php

<?PHP
include('connect.php');

if (isset($_POST["Delete"])) // if it's not set, do nothing.
{
$delete = $_POST["name"];
        $sql = "DELETE FROM `objects` WHERE `order` = $delete";
        mysql_query($sql);
var_dump($_POST);
        echo $sql;

}else{

}
?>

there are my files

aduljr

Your query is improperly written.

This is what is should look like

"DELETE FROM objects WHERE order = '". mysql_escape_string($delete). "'";

Whenever you put data from the outside into any query you should filter it in same way shape or form. Fortunately mysql includes a few methods to handle that. mysql_escape_string is one, mysql_real_escape_string is another one, or add slashes at the very least.

Second you should have used double quotes if you want the variable inside the string to be treated as a variable and not just some text.

third that variable should have single quotes around it, otherwise mysql thinks its a command a column name or something else. Its a sure fire way to generate an error.

Did I meantion you should never ever directly put in a variable into a query without some sort filter done on it. Its very easy to do a sql injection attack when one doesn't take care of outside inputs. In fact, its best to error on the side of over caution when dealing with variables within a query.

bpat1434

Okay, so here is my suggestion. You should standardize the name of the checkboxes to something like "remove". Now, since I guess you want to allow multiple row deletions at a time, then you need to make the name have square brackets at the end, like: "remove[]". The square brackets turn it into an array which can hold many values 😉

So your print statement in table.php would become:

        print "<tr><td class=\"border2\" align=center height=30><input type='checkbox' name='remove[]' value=" . $row["order"] . "></td><td class=\"border2\" align=center height=30>" . $row["order"] . "</td><td class=\"border2\" align=center height=30>". $row["name"] .  "</td><td class=\"border2\" align=center height=30>" . $row["descr"] . "</td></tr>";

Then you need to account for this in your ddelete.php file by now looping through $POST['remove']:

if (isset($_POST["Delete"])) // if it's not set, do nothing.
{
    foreach($_POST['remove'] as $delete)
    {
        $sql = "DELETE FROM `objects` WHERE `order` = $delete";
        mysql_query($sql);
        // var_dump($_POST);
        echo $sql;
    }
}

Hope that helps.