0

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>";
	}
}
3
Contributors
11
Replies
16
Views
9 Years
Discussion Span
Last Post by nav33n
0

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 ?

0

Hi

Do below statements not work on mysql?

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

As nav33n already suggested.

krs,
tesu

0

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?

0

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!

0

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?

0

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

0

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..

0

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

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.