[Resolved] Delete from database - I'm totally stuck

[Falkenberg]

Hi,

I've been reading everything I could find on deleting from a database using php, but I am out of luck, being a newbie.

I stripped my code and hope that some nice soul could help me out: why doesn't this work? The page reloads, but the entry is still there? The id is called "id" and the table is called 'copy'. To add new entries works perfectly.

A 1000 thank you's to anybody with tips!

---- 8<-------
Code edited, see below
---- 8<-------

[GreyBoy]

add 'or die(mysql_error());' to the end of all your mysql function calls. So mysql_connect() would be:

mysql_connect('blah','blah','blah') or die(mysql_error());

That will let you know if there is an error that needs to be fixed.

Also you need to put all mysql functions into variables before it can be used in an if statement. So:

$select_db = mysql_select_db('blah') or die(mysql_error());

$query = mysql_query('blah','blah') or die(mysql_error());
//etc...

Never do a mysql_query() before declaring the statement variable.

I didnt really look over the code with a fine comb, but that is what I noticed.

[Falkenberg]

Thanks. I tried to follow your good suggestions, and hope I got it right. I still got the same problem: the page reloads when I hit the delete button after checking the entry I want to delete, but nothing happens.

Can the problem be the in the first part of the delete process (marked with ****)? Am I naming things wrongly?

PS! Sorry for the ugly code last time...

-Falkenberg

<?php

$connect = mysql_connect("mysql.speech.kth.se", "musicrefuser", "********") or die(mysql_error());
$select_db = mysql_select_db("musicref") or die(mysql_error());
$dbcnx = @mysql_connect("mysql.speech.kth.se", "musicrefuser", "********") or die(mysql_error());
$query = mysql_query("SELECT * from copy;") or die(mysql_error());

if (!$dbcnx)
{
	echo( "<p>Unable to connect.</p>");
	exit();
}


//select the references database
if (! @mysql_select_db("musicref", $dbcnx) )
{
	echo( "<p>Unable lo locate the references database at this time.</p>" );
	exit();
}

// If a conference has been submitted
if ($submit == "SUBMIT")
{
	//Insert into the displayed database
	$sql = "INSERT INTO `copy` SET
		`datefrom`='$datefrom',
		`dateto`='$dateto',
		`abbrtitle`='$abbrtitle',
		`fulltitle`='$fulltitle',
		`link`='$link',
		`host`='$host',
		`city`='$city',
		`country`='$country',
		`deadline`='$deadline',
		`comment`='$comment',
		`submitby`='$submitby',
		`type`='$type'";

	//Control for error messages from sql-database
	if (@mysql_query($sql))
	{
		echo ("<p>The conference is added</p>");
	}
	else
	{
		echo ("<p>Error adding the conference: " .
		mysql_error());
	}
}




//************************
//Delete rows from table part I
if (isset($_post['DELETE']))
{
?>
Entries have been deleted<br><br>
<?php
		while (@list($key, $value) = each($_post['ID']))
		{
			$queryd = "delete from `copy` where `ID` = $value";
			$resultd = mysql_query($queryd) or die(mysql_error());
		}


?>

<form method="post" action="<?$PHP_SELF?>">

<?php

//delete rows part I pauses
//to delete rows, the following request must be done 2 times


//Request content and sort by the date
$result = @mysql_query("SELECT * FROM `copy` ORDER BY `datefrom` ASC") or die(mysql_error());
if (!$result)
{
	echo("<p>Error performing query: " . mysql_error() . "</p>");
	exit();
}


//Display the name of the each entry in a paragraph
while ( $row = mysql_fetch_array($result) or die(mysql_error()) )
{
	$refid = $refid + 1;
	$ID = $row["ID"];
	$color = changeColor($color);
	echo ("<div class=\"partreference\" id='ref" . $refid . "short'> <table align=\"center\" class=\"first\">");
	echo ("<tr bgcolor='$color'>" .
			"<td class=\"abbrtitle\"> <a onclick=\"toggleDesc('$refid'); return false;\">" . $row["abbrtitle"] . "</a></td>" .
			"<td class=\"fulltitle\">" . $row["fulltitle"] . " </td>" .
			"<td class=\"type\">" . $row["type"] . "</td>" .
			"<td class=\"datefrom\">" . $row["datefrom"] .  "</td>" .
			"<td class=\"link\"><a href=\"http://" .$row["link"] . "\">Webpage</a></td>" .
			"<td><input type=\"checkbox\" name=\"ID\" VALUE=\"".$row["ID"]."\"></td>" .
		"</tr>" . "\n");
	echo ("</table> </div>");

}

?>

<input type="submit" value="DELETE" name="DELETE"></form>

<?php

//Delete rows from table part II

}
else
{

?>

<form method="post" action="<?$PHP_SELF?>">

<?php

//Delete rows part II pauses
//This is the second request...


//Request content and sort by the date
$result = @mysql_query("SELECT * FROM `copy` ORDER BY `datefrom` ASC") or die(mysql_error());
if (!$result)
{
	echo("<p>Error performing query: " . mysql_error() . "</p>");
	exit();
}



//Display the name of the each entry in a paragraph
while ( $row = mysql_fetch_array($result) )
{
	$refid = $refid + 1;
	$ID = $row["ID"];
	$color = changeColor($color);
	echo ("<div class=\"partreference\" id='ref" . $refid . "short'> <table align=\"center\" class=\"first\">");
	echo ("<tr bgcolor='$color'>" .
			"<td class=\"abbrtitle\"> <a onclick=\"toggleDesc('$refid'); return false;\">" . $row["abbrtitle"] . "</a></td>" .
			"<td class=\"fulltitle\">" . $row["fulltitle"] . " </td>" .
			"<td class=\"type\">" . $row["type"] . "</td>" .
			"<td class=\"datefrom\">" . $row["datefrom"] .  "</td>" .
			"<td class=\"link\"><a href=\"http://" .$row["link"] . "\">Webpage</a></td>" .
			"<td><input type=\"checkbox\" name=\"ID\" VALUE=\"".$row["ID"]."\">
			</td>" .
		"</tr>" . "\n");
	echo ("</table> </div>");

}


?>

<input type="submit" value="DELETE" name="DELETE">
</form>

<?php
}
?>


<?php

//form for adding conferences:

?>


  <form name="newRef" method="post" action="<?$PHP_SELF?>">
  <table class="form" align="center">
    <tr>
      <td class="head1" rowspan="3">Dates<br>
        (yymmdd)</td>
      <td class="head2">
        <div align="left">From</div>
      </td>
      <td class="head1">
        <input type="text" name="datefrom">
      </td>
    </tr>
  </table>

<input type="submit" name="submit" value="SUBMIT"></form>

[helz]

hmm seems like the delete thing is: WHERE id='$value'
where below it states that the input name is id. so you might need to change it to:
WHERE id='$id'
also, where is it that you state which id of the info that you want to delete? or is the table used to store 1 row

[Falkenberg]

Thanks, Helz.

Now I changed to

WHERE id='$id'

Still it won't delete. Have I missed something in these lines:

		while (@list($key, $value) = each($_post['ID']))
		{
			$queryd = "delete from `copy` where `ID` = $id";
			$resultd = mysql_query($queryd) or die(mysql_error());
		}

I know I don't fully understand the code, I borrowed some here and there. Should the

@list($key,$value)

be something else? For instance

@list($id,$id)

? Does this answer your question? The table stores 13 rows (listed somewhere in the code as abbrtitle, fulltitle...etc), including an id (primary) called ID (capitol letters).

[helz]

all i can say is that ive never used the list thingy, ive no idea how useful it is or not...

what i do is use a while loop that displays all the rows with their id number and other information and creates a link that goes to the part where you can delete it:
see if you can read this:

$query = mysql_query("SELECT id, fulltitle FROM copy");
while ($row = mysql_fetch_array($query)) {
   $id = $row['id'];
   $fulltitle = $row['fulltitle'];
   echo "Would you like to delete this record with the id of: $id and the title of: $fulltitle? <a href='$PHP_SELF?task=delete&id=$id'>Click Here To Delete!</a><br>";
}


if (($task=="delete") && isset($_GET['id'])) {
$delete = mysql_query("DELETE FROM copy WHERE id='$_GET['id']' ");
   if (!$delete) {
      die("Record could not be deleted!!<br><br>mysql_error()");
   } else {
      echo "The record has been successfully deleted!!";
   }
}

[Falkenberg]

Sorry to be difficult...but I have problems with the sentence:

$delete = mysql_query("DELETE FROM copy WHERE id='$_GET['id']' "); 

and then probably also the above one;

if (($task=="delete") && isset($_GET['id'])) { 

Can it be the $GET['id'] that messes it up? The page won't load unless I remove GET[' '] and let $id remain. But still it won't delete it.

(Thanks a lot for help so far!)

[stolzyboy]

this line

$delete = mysql_query("DELETE FROM copy WHERE id='$_GET['id']' ");

should be this

$delete = mysql_query("DELETE FROM copy WHERE id='" . $_GET['id'] . "';") or die(mysql_error());

and your are doing the list each thing wrong, you want to put $value in the query, not id, cuz you are assigning the $key and $value for each $id

[Falkenberg]

OK, that solved the error messages (I don't use the list anylonger), and it now states "Record deleted", so it runs correctly to that point. But the record is not deleted from the database.

This is the stripped code I try with now (everything fancy thrown out long time ago...)

$connect = mysql_connect("mysql.kth.se", "musicrefuser", "********") or die(mysql_error());
$select_db = mysql_select_db("musicref") or die(mysql_error());
$dbcnx = @mysql_connect("mysql.kth.se", "musicrefuser", "********") or die(mysql_error());
$query = mysql_query("SELECT * from copy;") or die(mysql_error());

$ID = $_GET['ID'];

if (!$dbcnx)
{
	echo( "<p>Unable to connect.</p>");
	exit();
}


//select the references database
if (! @mysql_select_db("musicref", $dbcnx) )
{
	echo( "<p>Unable lo locate the references database at this time.</p>" );
	exit();
}


while ($row = mysql_fetch_array($query))
	{
	   $ID = $row['ID'];
	   $fulltitle = $row['fulltitle'];
	   echo "Delete record with id $ID, $fulltitle? <a href='$PHP_SELF?task=delete&ID=$ID'>Delete!</a><br>";
	}

if (($task=="delete") && isset($_GET['ID']))
	{
	$delete = mysql_query("DELETE FROM `copy` WHERE ID='" . $_GET['id'] . "';") or die(mysql_error());
	   if (!$delete)
		{
	      die("Record could not be deleted!!<br>mysql_error()");
	   	}
	   	else
	   	{
	      echo "Record has been deleted!!";
		}
	}

[stolzyboy]

change this line

$delete = mysql_query("DELETE FROM copy WHERE ID='" . $_GET['id'] . "';") or die(mysql_error());

to these lines

$query = "DELETE FROM copy WHERE ID='" . $_GET['id'] . "';";
echo $query;
$delete = mysql_query($query) or die(mysql_error());

then make sure the id is coming thru, it should echo something like:

DELETE FROM copy WHERE ID=5 //or whatever id is

and make sure ID is capitalized in your database field

[Falkenberg]

At last!

It seems to work now...thanks a bunch for all help! I will very soon mark thread as Resolved...only one little question:

the echo query - is it that one that outputs to the browser the whole line

"DELETE FROM copy WHERE ID='5';The record has been successfully deleted!!

It's not a problem, I only wonder...

All the best,
Falkenberg

[stolzyboy]

yes it is, that is merely for troubleshooting, you can comment it out or delete it and it will work just fine