DELETE FROM syntax problem

randal_138 · January 22, 2011

Having issues with a small piece of script. Trying to delete entries from a database. First, the essentials:

the button being clicked is named 'delete'

the columns in the actual table are 'INDEX' 'SUBJECT' 'MSG_TEXT' and 'MSG_DATE'

Here is the code:


<?php

if($_POST['delete']) // from button name="delete"
{
for($i=0;$i<$count;$i++)
{
	$del_id = $i;
	$sql = "DELETE FROM FORUM_MESSAGE WHERE id='$del_id'";
	$result = mysql_query($sql) or die (mysql_error());
}

if($result)
{
	header('Location: message-deleted.php');
}
}

?>

Thanks again!

Ryan

Pikachu2000 · January 22, 2011

Where is $count defined?

randal_138 · January 23, 2011

I feel dumb, but I am still new to all this.

Here is the coding, but I still have incomplete syntax.

<?php

if($_POST['delete']) // from button name="delete"
{
for($i=0;$i<$num;$i++)
{
	$sql = "DELETE FROM FORUM_MESSAGE WHERE '$num'='$i'";
	$result = mysql_query($sql) or die (mysql_error());
}

if($result)
{
	header('Location: message-deleted.php');
}
}

?>

My problem is here:

$sql = "DELETE FROM FORUM_MESSAGE WHERE '$num'='$i'";

What exact syntax to I need to use after "WHERE" ?

Thanks again,

Ryan

Pikachu2000 · January 23, 2011

Now where is $num defined?

randal_138 · January 24, 2011

Here is the code for $num:


$query = "SELECT * FROM FORUM_MESSAGE";
$result = mysql_query($query);
$num = mysql_numrows($result);

the entire code is here:


<!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=iso-8859-1" />
<title>My Profile</title>
<link href="loginmodule.css" rel="stylesheet" type="text/css" />
</head>
<body>
<h1>My Profile </h1>
<a href="member-index.php">Home</a> | <a href="member-profile.php">My Profile</a> | Update Posts | <a href="logout.php">Logout</a>

<br /><br />

<?php
// connection to DB

$query = "SELECT * FROM FORUM_MESSAGE";
$result = mysql_query($query);
$num = mysql_numrows($result);

mysql_close();

$i = 0;
while ($i < $num)
{
$subject = mysql_result ($result, $i, "SUBJECT");
$message_text = mysql_result ($result, $i, "MSG_TEXT");

echo '<div style="width: 400px;padding:20px;">';
echo '<table border=0 width="400px">';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo 'DB#: ';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;">';
echo '<div>';
echo $i;
echo '</div>';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo 'Subject: ';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;">';
echo '<div>';
echo $subject;
echo '</div>';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo 'Message: ';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;">';
echo '<div>';
echo '<textarea id="message_text" name="message_text" cols="50" rows="10">';
echo $message_text;
echo '</textarea>';
echo '</div>';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;text-align:center;">';
echo '<form method="post">';
echo '<input type="hidden" name="update" value="true" />';
echo '<input type="submit" value="Update" />';
echo '         ';
echo '<input type="hidden" name="delete" value="true" />';
echo '<input type="submit" value="Delete" />';
echo '</form>';
echo '</td>';
echo '</tr>';
echo '</table>';
echo '<br />';
echo '<hr />';
echo '</div>';

$i++;
}

if($_POST['delete']) // from button name="delete"
{
for($i=0;$i<$num;$i++)
{
	$sql = "DELETE FROM FORUM_MESSAGE WHERE $i='$num'";
	$result = mysql_query($sql) or die (mysql_error());
}

if($result)
{
	echo 'Message deleted.';
}
}

?>

</body>
</html>

Hope this helps...

Thanks again!

Ryan

jcbones · January 24, 2011

Correct syntax is:

DELETE FROM table WHERE column = 'value'

Pikachu2000 · January 24, 2011

This query:

for($i=0;$i<$num;$i++)
   {
      $sql = "DELETE FROM FORUM_MESSAGE WHERE $i='$num'";

Is a very dangerous query. IF $i ever does actually equal $num, that query will delete every record in the table.

Maybe if you describe what it is you're trying to do, we can help you come up with a solution that won't inadvertently truncate a table

randal_138 · January 24, 2011

Pikachu,

Too late. I was toying around with the script the other day and ended up with this:

DELETE FROM FORUM_MESSAGE WHERE $i

Of course, as soon as I clicked one of the buttons, the entire table was wiped. Good thing nothing important was on it...

Will try the 'value' and reply back on whether or not it works.

Oh, and I am trying to order my database results with the $i and then have the option to edit and delete the entries.

Ryan

coupe-r · January 24, 2011

Shouldn't $num be this:

$num = mysql_num_rows($result);

NOT

$num = mysql_numrows($result);

randal_138 · January 24, 2011

I thought that looked wrong...

I still am stumped on the query:

"DELETE FROM FORUM_MESSAGE WHERE ..."

How do I get it to delete that numbered row? Maybe there is an easier way to do this, but I sure don't know it.

Ryan

rondog · January 25, 2011

what if you just

echo $sql . "<br/>";

?

What does it say?

lazylodr · January 25, 2011

the columns in the actual table are 'INDEX' 'SUBJECT' 'MSG_TEXT' and 'MSG_DATE'

Here is the code:


<?php

if($_POST['delete']) // from button name="delete"
{
for($i=0;$i<$count;$i++)
{
	$del_id = $i;
	$sql = "DELETE FROM FORUM_MESSAGE WHERE id='$del_id'";
	$result = mysql_query($sql) or die (mysql_error());
}

if($result)
{
	header('Location: message-deleted.php');
}
}

?>

Another problem you may be having is that the column "id" isn't in the list of columns you said were in the table...if you are trying to delete a specific index try

$sql = "DELETE FROM FORUM_MESSAGE WHERE index='$del_id'";

randal_138 · January 25, 2011

The only problem is that the INDEX column starts the increments at '1' and the $i increments start at '0'. I am trying to do a DELETE FROM table WHERE INDEX = $i + 1 (not exact syntax obviously), but that is not working out so well.

Can I start the $i increments at '1'?

jcbones · January 25, 2011

Of course, just change $i = 1 in your for loop.

Pikachu2000 · January 25, 2011

There really is a much easier way to do this. Since you're already echoing each record into its own form, just add another hidden field, and make its value that of the record's primary key. Then when the form is submitted, take that value and use it for your query. It's so much easier and less error-prone than what you're trying to do right now.

randal_138 · January 26, 2011

How would I have to word the query then? I need to make a connection between the delete button in that 'row' and pair it up with the primary key (SUBJECT) so that only that entry is deleted?

Ryan

Sign In

DELETE FROM syntax problem

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

Join the conversation

Important Information