Topic awaiting preservation: PHP/MySQL and deleting rows?

I have a database that will enter info from a form just fine. However, I can't get the editing or deleting part working right. Here is the delete.php script that I am working with just for testing purposes and it's not working.

<? 
//connect to mysql
$link = mysql_connect("localhost","username","password");
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
	
//select database
mysql_select_db("newsy",$link); 

//If cmd is not hit
if(!isset($cmd)){
   //display all the news
   $result = mysql_query("SELECT * FROM news"); 
   
   //run the while loop that grabs all the news scripts
   while($r=mysql_fetch_array($result)){ 
      //grab the title and the ID of the news table
      $title=$r["title"];//grab the title
      $id=$r["id"];//grab the id
     
      echo "<a href='delete.php?cmd=delete&id=$id'>$title - Delete</a>";
      echo "<br>";
    }
}

if($cmd=="delete"){
    $sql = "DELETE FROM news WHERE id=$id";
    $result = mysql_query($sql,$link);
    echo "Row deleted!";
}
?>

It will display the titles and the links seem to work as they are displayed correctly but they are just not deleting. If I can get some help with what is wrong with this part, I think I can get the edit.php script to work.

Thanks in advance!

Later,

C:\

What's the ID that's going into the delete command?

I'd spit out that sql and drop it into phpmyadmin to see what's happening. Also test for errors.

if($cmd=="delete"){
    $sql = "DELETE FROM news WHERE id=$id";
   echo "SQL: $sql";  // Use this for testing in phpmyadmin
   $result = mysql_query($sql,$link);
if ( !$result ) {
echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
} else {
echo "Row deleted"
}

.:[ Never resist a perfect moment ]:.

Also note SQL errors no longer send warnings in php you need to explicitly echo the error messages



.:[ Never resist a perfect moment ]:.

well I ran this query in phpMyAdmin and it worked fine: DELETE FROM news WHERE id=4

There was a post that had the id of 4. The id field is auto incrementing. Not sure why it's not working. The query works fine in phpmyadmin.

I will add your code in and see what happens.

Later,

C:\

From my experience, you need to put single quotes around $id -

$sql = "DELETE FROM news WHERE id='$id'";

Only if it's a varchar etc. If its an integer or other number type puting single quotes would cause errors.

Just add the line

print addslashes(mysql_error())

after the query is done, this will print the errors for the last query.

quote:

---

If its an integer or other number type puting single quotes would cause errors.

---

I don't think that's true.



.:[ Never resist a perfect moment ]:.

It doesnt seem to care these days actually, but adding quotes to a number doesnt make a difference. I did some tests and if its type varchar etc, you can put numbers in with or without quotes - however if its a word "testing" it doesnt work unless its quoted.

For a number type field you can put a number in with or without quotes. I know i used to have a problem with this in some type of database, might have been earlier versions not sure. Could have been another database all together done too many to remember.

Unless I'm misunderstanding (which is certainly possible), the issue is not what the data actually is, it's a matter of it being a variable...isn't it?

That has been my understanding...



(Edited by DL-44 on 10-06-2005 04:02)

Not really

I usually always put the single quotes in just for safety in case the variable ends up blank.

wheresomething = ''

will parse

wheresomething =

(nothing on the end) will not.



.:[ Never resist a perfect moment ]:.

(Edited by bitdamaged on 10-06-2005 04:50)

still nothing. I used everyones method that was posted here and nothing happens.

I used bit's code to check for errors and none came up. Just....does nothing Click the link and it just...sits there.


I did run this code and it worked:

//connect to database
$dbcnx=mysql_connect("localhost","######","######");
//if it doesn't connect, die
if(!$dbcnx){
	die("Could not connect : " . mysql_error());
}
//select database
$db=mysql_select_db("newsy",$dbcnx);

//make a query to delete the entry
$query="DELETE FROM news WHERE id = 8";
//send the query to the database
$result=mysql_query($query,$dbcnx);

if(!$result){
	echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
}else{
	echo "Row deleted";
}

So there must be something wrong in the way that I am passing what is to be deleted and how? I am guessing from the if(cmd=="delete") stuff?


Later,

C:\



.. removed the connection user, pass - bit

(Edited by bitdamaged on 10-06-2005 18:11)

Looking through the code a little more closely, two things come to mind -

You say the data displays as it should - the way your code is setup, you should nto be seeing the data if $cmd is set. So if you *are* seeing the data after clicking the link to delete, then $cmd is not being passed.

Have you verified that $cmd is being passed? Soemthing as simple as
echo $cmd;. You may need to specify $cmd = $_GET['cmd'];

Also, it seems that the if($cmd=="delete") should be elseif($cmd=="delete"), since your checking to see if $cmd is set - it can't be both not set, and set to "delete". Don't know if that would cause a problem or not.

That sounds like it could be the problem. If the query is never getting run then you are going to have an issue.

I'd try the same code with a dynmic $id var and then do it with the $cmd flag. Also echo both of those vars before running the query.



.:[ Never resist a perfect moment ]:.

OK....got that one going. You guys were right. $cmd was never being passed...now it is. Thanks.

Have another question though. After the row has been deleted, how can I redirect the page to the index? I can't use header('Location : ../index.php'); since the header is already in the page. Any other suggestions?

I might have to rethink on how these pages are going to be parsed anyway. I have a different page for add, edit and delete. Might be better to just spit out all the entries and have something like one check box for delete and one for edit. Depending on which is clicked, determines which page is loaded?

This is just for me to goof off with and try to learn something. Little bit of a slow pace right now but at least I am learning something

Thanks in advance.

Later,

C:\

why are the headers already sent?

Because you echoed something?



.:[ Never resist a perfect moment ]:.

well I just have it as a full page so it has the doc type and everything in it already. Or am I just confused on what that is doing?
Like I said, I think I need to figure out how to do each on of these things and then do some rearranging of how the pages are loaded.

So, if the admin logs in and everything is ok then it takes them to a page where they can either add, edit or delete posts. Make sense?


<edit>

OK...well it seems that it doesn't matter if the doc type is there or not, still says the headers are already sent?....

quote:

---

Warning: Cannot add header information - headers already sent by

---

</edit>

Later,

C:\



(Edited by CPrompt on 10-08-2005 21:27)

you'll need to detect what state you are in before outputting anything.

take a look at PHP's ob_start() and ob_end_flush

so something like -

<?php
ob_start();

if (empty($cmd)) {

?>

html goes here...

<?php

}

elseif($cmd == "delete") {
header(location:...);
}
ob_end_flush();
?>

Adding whatever other checks may be needed....

Yeah a good habit to get in is to place all of your business logic at the top of your page before outputting any content to the page.

A common mistake is to put a ton of logic relative to the page, this is a bad habit. ouput buffering a'la DL is one way around it.

Also another common mistake is that you can't have anything (including a single space) before your first <?php tag.

In answer to your question is that you have a couple of solutions about what to do for a redirect. First is just don't output anything before trying to redirect. Either by moving your code around or using a buffer like DL suggested

Another solution is to include the appropriate HTML file based on where you are in the process. I use this a lot. Most of my php is in some file but I include the right HTML depending on what I'm doing.



.:[ Never resist a perfect moment ]:.

Also another common mistake is that you can't have anything (including a single space) before your first <?php tag.

Or after your last tag! God i had this one problem from that once, took me forever to bloody find - then i deleted after the end tag to be sure and it worked. What a nightmare that was!