1 row returned when deleting non-existant entry

[mlambert]

Hey guys,
I'm having a bit of trouble with a 3-tier application I'm working on. When I try to remove an entry that doesn't exist, I am getting back a value telling me one row was deleted. If I insert the same SQL into the mySQL command line it returns 0. Has anyone else ever had a similar problem? I looks like I'm going to have to query the database before I attempt to remoev the row to ensure it first exists... but I really don't want to have to do this. Can anyone help?

[bastien]

more details...like table structure, sample data and sample delete

[ctimmer]

I think in the first case you are getting an error return (1).
In the second you are getting a successful return(0).

[bastien]

that is true...mysql will return the result of the query in binary 1 for failure and 0 for success

[mlambert]

O.K. - I think I am confusing a failure (a return value of 1) with the return value of the number of rows affected.. e.g.: when I insert a value into the database, I get a return value of 1 - i.e. 1 row was inserted. When I delete a valid entry, i.e. an entry that IS in the database, I get 1 as a return value as well. I was using this return value to denote success or failure. However, if I attempt to delete a non-existent value I am also recieving a return value of 1 <- meaning 1 row was somehow affected when there should be a 0 return value as 0 rows have obviously been affected. My app. is quite a large 3-tier chemical tracking system, but the same results can be produced with a simple database script. It seems I am always getting this value, even with :

$link = mysql_connect($host, $username, $password) ;
mysql_select_db($databaseName) ;

$rows = mysql_query("DELETE FROM tblTemp WHERE name='Mat'", $conn) ;

return $rows ;

If there is no entry in the database with the name equal to Mat, I will still somehow get a 1 row affected message.

[mlambert]

Sorry, I mixed up a variable.... code should read:

$conn = mysql_connect($host, $username, $password) ;
mysql_select_db($databaseName) ;

$rows = mysql_query("DELETE FROM tblTemp WHERE name='Mat'", $conn) ;

return $rows

[laserlight]

Perhaps try:

$conn = mysql_connect($host, $username, $password);
mysql_select_db($databaseName);

$query = mysql_query("DELETE FROM tblTemp WHERE name='Mat'", $conn);

echo "Affected rows: " . mysql_affected_rows($query);

Do you get
"Affected rows: 1"
as output?

[mlambert]

Which unfortunately gives me the result..

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource

using the exact code you gave me.

[BlueMeteor]

function mysql_query() return 2 result
success return true(1).
fail return false(2),
if you SQL not get error,e.g

$rows = mysql_query("DELETE FROM tblTemp WHERE name='Mat'", $conn) ;
return $rows

It will always return true //true=1

if you want to know how many rows had been deleted,you should use mysql_affected_rows,as laserlight said

try.....
echo "Affected rows: " . mysql_affected_rows();

[mlambert]

Thanks for your help, but I am getting 0 as an affected rows return value (even when there has been more than 10 rows deleted) and mysql_num_rows() is telling me my result resource is not valid. But the SQL commands are actually working. All of my delete and insert statements work, I just cannot get the return value back properly. Please HELP!!!!!! arrrhgghhhhh!

[bastien]

post code

[mlambert]

ok... here it is. I've just made up this test script. Ive got a mySQL database with a table called tblTemp with one field in it that will hold a String of 50 chars. Here is the code I use to access the table...

$link = mysql_connect("localhost", "mlambert", "xt6jx10i") ;
mysql_select_db("mlambert") ;

$result = mysql_query("delete from tblTemp", $link) ;

//this should be the result identifier .. positive integer means sql
//was executed
echo ("Result : " . $result) ;

//this should give me the number of rows that were deleted...but
//doesn't
echo ("Affected Rows : " . mysql_affected_rows($link)) ;

//this should work..but i get an error
echo ("Num Rows : " . mysql_num_rows($result)) ;

.. If I fill this table with 10 rows and then run this script, the mysql_affected_rows($link) always returns 0 even if ten rows are deleted and the mysql_num_rows($result) function signals an error : mysql_num_rows(): supplied argument is not a valid MySQL result resource

Hope this makes sense...

[laserlight]

Actually, no rows have been deleted, since for some reason your query isnt working.

I suggest you test with some error messages, e.g.

$conn = mysql_connect($host, $username, $password)
	OR die("Could not connect to database server");
mysql_select_db($databaseName)
	OR die("Could not select database");

$query = mysql_query("DELETE FROM tblTemp WHERE name='Mat'", $conn)
	OR die(mysql_error());

echo "Affected rows: " . mysql_affected_rows($query);

[mlambert]

ahmmm, not really. The query seems to be working fine. It actually seems to be deleting the information correctly.