MySQL delete specific row

[shadwickman]

Hi, I couldn't get this to work, but I was using a PHP script to go through the rows in a table in my database and if it found a specific value, to delete that row. However, it deletes ALL the rows no matter what I do and throws an exception after. Any help is appreciated:

// connect (host, usr, and pwd are previously defined, correctly)
$cid = mysql_connect( $host, $usr, $pwd );

// get all columns from the table 'accounts', $db was previously defined (database name)
$query = "SELECT * FROM accounts ORDER BY id";
$result = mysql_db_query( $db, $query, $cid );

// run through each row looking for column 'usrn' with the value 'caughtusername'
while( $row = mysql_fetch_array( $result ) ) {
	if( $row['usern'] == "caughtusername" ) {
		$id = $row[ 'id' ];
		$query = "DELETE  FROM accounts WHERE id=$id";

		// execute the deletion statement
		$result = mysql_db_query( $db, $query, $cid );
		echo "<b>Deleted: </b>" . $row['usern'] . "<br>";
	}
	else {
		echo "<b>Spared: </b>" . $row['usern'] . "<br>";
	}
}

[nav33n]

What exception does it throw ? I don't see anything wrong with your script :S
Umm.. one question though. Why don't you query the table using the condition where usern="caughtusername"; and delete all those records ?

[tesuji]

Hi

Do below statements not work on mysql?

DELETE FROM accounts WHERE usrn = 'caughtusername';
COMMIT;

As nav33n already suggested.

krs,
tesu

[shadwickman]

It's good because by changing my SQL query to what you suggested, it only clears that one row and leaves the others. The only problem left is that it still gives me the error output:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /www/99k.org/s/h/a/shadwick/htdocs/db/delete_data.php on line 28

I have this page on my site: shadwick.99k.org/ in my folder called db. Where it says the error originates is line 28 which is this one:

while( $row = mysql_fetch_array( $result ) ) {

Any ideas?

[nav33n]

What exactly are you trying to do ? I mean, Do you have to show that message at the end ? ie., spared and deleted ?
If no, You can use the query,

$query = "delete from accounts where usern = 'caughtusername'";
mysql_query($query);

This will delete all the records from table accounts where usern is "caughtusername".
But, If you want to display that message, you can try this way.

$cid = mysql_connect( $host, $usr, $pwd );

// get all columns from the table 'accounts', $db was previously defined (database name)
$query = "SELECT * FROM accounts";
$result = mysql_db_query( $db, $query, $cid );

// run through each row looking for column 'usrn' with the value 'caughtusername'
while( $row = mysql_fetch_array($result)) {
	$username = $row['usern'];
	$id = $row['id'];
	if($username == "caughtusername") {
		$query = "DELETE  FROM accounts WHERE id=".$id;
		// execute the deletion statement
		$result = mysql_db_query( $db, $query, $cid );
		echo "<b>Deleted: </b>" . $username . "<br>";
	} else {
		echo "<b>Spared: </b>" . $username . "<br>";
	}
}

Give it a try and tell us if it works!

[shadwickman]

Hmm.. if it spares all the rows, then it's ok, but if it deletes one row it gives me the same error. The correct row gets deleted and everything, but it gives the error anyway. When I made my table, I only specified NOT NULL for the id column, but should I have specified it for the other columns I was making (such as 'usern', which I used VARCHAR(20) for)? Is that causing the problem?

[nav33n]

Umm.. Let me get something clear.. You want to delete only the records where the usern field has the value "caughtusername". Right ?

[shadwickman]

Yes that's right. Sorry if I'm not too clear. I only got in PHP and MySQL 2 days ago.

[nav33n]

Then this will do the trick for you.

<?php
// connect (host, usr, and pwd are previously defined, correctly)
$cid = mysql_connect( $host, $usr, $pwd );

// get all columns from the table 'accounts', $db was previously defined (database name)
$query = "DELETE FROM accounts where usern='caughtusername'";
$result = mysql_db_query( $db, $query, $cid );
?>

Ofcourse, $db, $host,$usr and $pwd has to be defined..

[tesuji]

can you give us the exact error message?

does error also happen if you execute mysql_query($query) ?

does the row you want to delete still exists?

What database engine, what about commit?

-----
tesu

[shadwickman]

Oh thank you so much! I don't know why I got the original idea to move through every row when I could've done it like that.

[nav33n]

:) You are welcome!