• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Why is query sometimes not executing

Hi guys

I have this query

$update = "
            UPDATE Users t1 SET
                NewMail =
                    CASE
                        WHEN (NewMail=0 OR NewMail IS NULL) THEN 1
                        ELSE NewMail + 1
                    END
            WHERE
                UserId = $rUserId
            LIMIT 1
        ";
        mysql_query($update);

it is executed with other queries..

IT DOES NOT FAIL.

But sometimes it does not update and the NewMail keeps beeing 0. why is that?
0
tim_carter
Asked:
tim_carter
  • 8
  • 4
1 Solution
 
Aaron ShiloCommented:
hi

probably becuse you use the LIMIT 1
this means update the last value

change the value to a larger number or remove.
0
 
tim_carterAuthor Commented:
It is only 1 user that it should update at this point? why would i run through every record?
0
 
Aaron ShiloCommented:
just to be clear you need to update only one record at a time ?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
tim_carterAuthor Commented:
YES. it is only one user..

like UserId=2


0
 
Aaron ShiloCommented:
that isent what i asked.

when you use Userid= 2 will you allways retrive only one row from the table?
0
 
tim_carterAuthor Commented:
and that is what i need. but sometimes it does not update the NewMail value
0
 
Aaron ShiloCommented:
hi
when you run this query

select * from Users
                    where NewMail=0 OR NewMail IS NULL
                     and UserId = $rUserId

notice i removed the LIMIT 1

how meny rows come bac ?
0
 
tim_carterAuthor Commented:
what has this query to do with what i want?

1st of the query you have done here will return almost anything because you have not encapsulated (newmail)

MY STATEMENT should update 1 user. and if the user has the value 0 or NULL in newmail it should set the value to 1, else it should add to the current value,.
0
 
Muhammad WasifCommented:
>>But sometimes it does not update and the NewMail keeps beeing 0. why is that?
Are you sure that $rUserId is not empty during this time? May be query is failing due to some eorr or $rUserId has incorrect value. Use mysql_error() to detec the error and print the query to debug the issue in this way.

mysql_query($update) or die("Error in Query: $update - ". mysql_error());

Open in new window

0
 
tim_carterAuthor Commented:
wasifg

I am already doing that in the script..

It does not fail. And it is randomly it happens.
0
 
tim_carterAuthor Commented:
Can it have something to do with CASE?
0
 
tim_carterAuthor Commented:
I found out that sometimes my value was less than zero.
0
 
tim_carterAuthor Commented:
less than zero. My own error
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now