WHERE clause problem 2

[jasc2k]

Hi there,

This maybe (or at least I hope) really obvious to someone and I apologize if this is in the wrong forum (there are sooo many! lol) but basically sql seems to always return a value of 1 whether a: the query completes, or b: the query did not find any results in either UserFrom or mail_id

$query = "DELETE FROM ".TBL_MAIL_SENT." WHERE UserFrom = '$session->username' AND mail_id = '$msgid' LIMIT 1";
        
                $x = $database->query($query) or die("An error occurred resulting that this message has not been marked read!");

$x is always = 1 how can I tell if the query completes or not?

Thanks all

 

[r937]

yes, you are in the wrong forum, this sounds like a perl problem

the obvious answer to your question is that the query actually does complete in both cases

a) if the query does delete LIMIT 1 row, then at least one row is no longer there, and you can detect this by running COUNT queries to see how many rows with that particular UserFrom and mailid existed before the delete query, and how many are still left afterwards

b) if the query does not delete any row, then your COUNT query will indicate 0 rows to be deleted before the delete query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[audiopro]

Are you sure $x is equal to '1' and not '-1' because that sql statement does not look to be properly formed.

Keith

http://www.studiosoft.co.uk

 

[jasc2k]

its possible but when I echo $x I just get 1?

what makes you say its not properly formed?
Thanks

 

[audiopro]

That does not look like Perl syntax, it could however be PHP. Might be worth checking with the PHP boys.

FROM ".TBL_MAIL_SENT." W

Keith

http://www.studiosoft.co.uk

 

[r937]

perl, php, what's tha difference

the mysql part looks okay



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jasc2k]

hi,
Yes its PHP syntax - I have also posted this in their forum much to their dislike (

As far as I can tell the statement is correct and works fine my main problem is because it always returns 1 I dont know if the statement failed because no records returned true to both WHERE clauses or just one of the clauses?

For instance the mail_id was found but the UserTo did NOT match the statement does not delete anything but still returns 1?

Thanks

 

[vacunita]

What dislike? jpadie asked you a question that you have not answered.

thread434-1582118
And is even helping you in another thread.

Anyway, assuming that your TBL_MAIL_SENT constant actually exists and has a value normally queries are considered successful as long as they run correctly. Whether they can perform an action or not based on the criteria provided is not important.

That means in your case, as long as the query runs and even if it finds nothing to delete it will return a 1. Because it was successfully executed.

With that said, try running your query outside of PHP in a DB GUI such as MYSQL's own Query Browser. Se if it returns any possible rows for the deletion.






----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ingresman]

Because you are doing a delete the executequery will return true (1) if it worked and false (-1) if it failed.
If you issued an SQL select $x will be the pointer to the result structure.
As you get 1 back it looks to be ok, try changing the query to say something like

mail_idddddd = '$msgid'

si it will fail and put a @ before $x to supress PHP handling the error. Don't do this in live, it's a bad idea.
You might get -1 back now.
This particular feature on SQL does trip people up but when an SQL query runs to completion it always says it works , it's just it might not have done anything so you need to you need to call mysql_error

http://php.net/manual/en/function.mysql-errno.php

to see what went on (and

http://www.php.net/manual/en/function.mysql-error.php

to get a text description) as well as

http://www.php.net/manual/en/function.mysql-affected-rows.php

which tells you how many rows got deleted.
So if you get 1 back it worked, and the affected rows is greater than 0 you deleted something. If it says 0 it didn't find anything to delete.
Hope that makes sense.

 

[jasc2k]

Firstly thanks to all who replied you have all helped

vacunita and ingresman both your posts have helped me realize why it returns 1.

I have attempted to code around the issue for the check with another query instead of relying on the AND statement returning something for my error.

Thanks again