how to set an update query to not update if field int is at 0
I've got a website that has personal messages and what I'm trying to do is when the person goes to the page to read a message it subtracts one from the amount of messages.
So in the table it has 3 columns - id, userID, and tally.
So for example, say for a user that tally is listed as 2, when they go to the page the code below is supposed to subtract to make it 1.
The problem I'm having is when it gets to 0 I don't want it to keep subtracting to where there's negative numbers.
I've been trying to use the code in this topic here - MySQL: update a field only if condition is met - but it's not working properly when I do it's giving me 500 errors or synax errors depending on what I try so I'm lost on what I'm doing wrong.
Original code (works but leaves the possibility for negative numbers).
<?php
$con=mysqli_connect("");
// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();
$userID=$_SESSION['user'];
// escape variables for security
$sql="update messagealert set userID='".$userID."', tally=tally - 1 where userID='".$userID."'";
if (!mysqli_query($con,$sql))
die('Error: ' . mysqli_error($con));
echo "";
mysqli_close($con);
?>
Here's the updated statement as I have it:
$sql="update messagealert set userID='".$userID."', tally=tally - 1, WHEN tally>0 THEN tally-1
ELSE tally where userID='".$userID."'";
php
asked Nov 12 '18 at 22:33
Jeremy Murray
11
|
show 2 more comments
I've got a website that has personal messages and what I'm trying to do is when the person goes to the page to read a message it subtracts one from the amount of messages.
So in the table it has 3 columns - id, userID, and tally.
So for example, say for a user that tally is listed as 2, when they go to the page the code below is supposed to subtract to make it 1.
The problem I'm having is when it gets to 0 I don't want it to keep subtracting to where there's negative numbers.
I've been trying to use the code in this topic here - MySQL: update a field only if condition is met - but it's not working properly when I do it's giving me 500 errors or synax errors depending on what I try so I'm lost on what I'm doing wrong.
Original code (works but leaves the possibility for negative numbers).
<?php
$con=mysqli_connect("");
// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();
$userID=$_SESSION['user'];
// escape variables for security
$sql="update messagealert set userID='".$userID."', tally=tally - 1 where userID='".$userID."'";
if (!mysqli_query($con,$sql))
die('Error: ' . mysqli_error($con));
echo "";
mysqli_close($con);
?>
Here's the updated statement as I have it:
$sql="update messagealert set userID='".$userID."', tally=tally - 1, WHEN tally>0 THEN tally-1
ELSE tally where userID='".$userID."'";
php
asked Nov 12 '18 at 22:33
Jeremy Murray
11
2
update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'"??
– user10226920
Nov 12 '18 at 22:36
I think I posted the wrong update part, this is what's been giving the error: $sql="update messagealert set userID='".$userID."', tally=tally - 1 WHEN tally>0 THEN tally-1 ELSE tally where userID='".$userID."'"; and it gives me this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN tally=0 THEN tally-1 ELSE tally where userID='1'' at line 1
– Jeremy Murray
Nov 12 '18 at 22:40
Because it's syntaxically invalid. But you probably don't need a CASE/WHEN anyway (unless you always want to update other columns), what's wrong with @IdontDownVote's (quite obvious) solution?
– Jeto
Nov 12 '18 at 22:45
I hadn't tried it yet, I misunderstood the question marks he put at the end of it as saying the original update part was the wrong way to do it. I just overthought it. I did just try it and it appears to be working like I need it to so thank you guys for your help.
– Jeremy Murray
Nov 12 '18 at 22:51
Why are you resetting the userID to the same thing? Seems like you only need to update the tally column
– Don't Panic
Nov 12 '18 at 23:09
|
show 2 more comments
I've got a website that has personal messages and what I'm trying to do is when the person goes to the page to read a message it subtracts one from the amount of messages.
So in the table it has 3 columns - id, userID, and tally.
So for example, say for a user that tally is listed as 2, when they go to the page the code below is supposed to subtract to make it 1.
The problem I'm having is when it gets to 0 I don't want it to keep subtracting to where there's negative numbers.
I've been trying to use the code in this topic here - MySQL: update a field only if condition is met - but it's not working properly when I do it's giving me 500 errors or synax errors depending on what I try so I'm lost on what I'm doing wrong.
Original code (works but leaves the possibility for negative numbers).
<?php
$con=mysqli_connect("");
// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();
$userID=$_SESSION['user'];
// escape variables for security
$sql="update messagealert set userID='".$userID."', tally=tally - 1 where userID='".$userID."'";
if (!mysqli_query($con,$sql))
die('Error: ' . mysqli_error($con));
echo "";
mysqli_close($con);
?>
Here's the updated statement as I have it:
$sql="update messagealert set userID='".$userID."', tally=tally - 1, WHEN tally>0 THEN tally-1
ELSE tally where userID='".$userID."'";
php
asked Nov 12 '18 at 22:33
Jeremy Murray
11
I've got a website that has personal messages and what I'm trying to do is when the person goes to the page to read a message it subtracts one from the amount of messages.
So in the table it has 3 columns - id, userID, and tally.
So for example, say for a user that tally is listed as 2, when they go to the page the code below is supposed to subtract to make it 1.
The problem I'm having is when it gets to 0 I don't want it to keep subtracting to where there's negative numbers.
I've been trying to use the code in this topic here - MySQL: update a field only if condition is met - but it's not working properly when I do it's giving me 500 errors or synax errors depending on what I try so I'm lost on what I'm doing wrong.
Original code (works but leaves the possibility for negative numbers).
<?php
$con=mysqli_connect("");
// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();
$userID=$_SESSION['user'];
// escape variables for security
$sql="update messagealert set userID='".$userID."', tally=tally - 1 where userID='".$userID."'";
if (!mysqli_query($con,$sql))
die('Error: ' . mysqli_error($con));
echo "";
mysqli_close($con);
?>
Here's the updated statement as I have it:
$sql="update messagealert set userID='".$userID."', tally=tally - 1, WHEN tally>0 THEN tally-1
ELSE tally where userID='".$userID."'";
php
php
asked Nov 12 '18 at 22:33
Jeremy Murray
11
asked Nov 12 '18 at 22:33
Jeremy Murray
11
edited Nov 12 '18 at 22:36
asked Nov 12 '18 at 22:33
Jeremy Murray
11
asked Nov 12 '18 at 22:33
Jeremy Murray
11
asked Nov 12 '18 at 22:33
Jeremy Murray
11
11
2
update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'"??
– user10226920
Nov 12 '18 at 22:36
I think I posted the wrong update part, this is what's been giving the error: $sql="update messagealert set userID='".$userID."', tally=tally - 1 WHEN tally>0 THEN tally-1 ELSE tally where userID='".$userID."'"; and it gives me this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN tally=0 THEN tally-1 ELSE tally where userID='1'' at line 1
– Jeremy Murray
Nov 12 '18 at 22:40
Because it's syntaxically invalid. But you probably don't need a CASE/WHEN anyway (unless you always want to update other columns), what's wrong with @IdontDownVote's (quite obvious) solution?
– Jeto
Nov 12 '18 at 22:45
I hadn't tried it yet, I misunderstood the question marks he put at the end of it as saying the original update part was the wrong way to do it. I just overthought it. I did just try it and it appears to be working like I need it to so thank you guys for your help.
– Jeremy Murray
Nov 12 '18 at 22:51
Why are you resetting the userID to the same thing? Seems like you only need to update the tally column
– Don't Panic
Nov 12 '18 at 23:09
|
show 2 more comments
2
update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'"??
– user10226920
Nov 12 '18 at 22:36
I think I posted the wrong update part, this is what's been giving the error: $sql="update messagealert set userID='".$userID."', tally=tally - 1 WHEN tally>0 THEN tally-1 ELSE tally where userID='".$userID."'"; and it gives me this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN tally=0 THEN tally-1 ELSE tally where userID='1'' at line 1
– Jeremy Murray
Nov 12 '18 at 22:40
Because it's syntaxically invalid. But you probably don't need a CASE/WHEN anyway (unless you always want to update other columns), what's wrong with @IdontDownVote's (quite obvious) solution?
– Jeto
Nov 12 '18 at 22:45
I hadn't tried it yet, I misunderstood the question marks he put at the end of it as saying the original update part was the wrong way to do it. I just overthought it. I did just try it and it appears to be working like I need it to so thank you guys for your help.
– Jeremy Murray
Nov 12 '18 at 22:51
Why are you resetting the userID to the same thing? Seems like you only need to update the tally column
– Don't Panic
Nov 12 '18 at 23:09
2
2
update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'" ??– user10226920
Nov 12 '18 at 22:36
update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'" ??– user10226920
Nov 12 '18 at 22:36
I think I posted the wrong update part, this is what's been giving the error: $sql="update messagealert set userID='".$userID."', tally=tally - 1 WHEN tally>0 THEN tally-1 ELSE tally where userID='".$userID."'"; and it gives me this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN tally=0 THEN tally-1 ELSE tally where userID='1'' at line 1
– Jeremy Murray
Nov 12 '18 at 22:40
I think I posted the wrong update part, this is what's been giving the error: $sql="update messagealert set userID='".$userID."', tally=tally - 1 WHEN tally>0 THEN tally-1 ELSE tally where userID='".$userID."'"; and it gives me this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN tally=0 THEN tally-1 ELSE tally where userID='1'' at line 1
– Jeremy Murray
Nov 12 '18 at 22:40
Because it's syntaxically invalid. But you probably don't need a CASE/WHEN anyway (unless you always want to update other columns), what's wrong with @IdontDownVote's (quite obvious) solution?
– Jeto
Nov 12 '18 at 22:45
Because it's syntaxically invalid. But you probably don't need a CASE/WHEN anyway (unless you always want to update other columns), what's wrong with @IdontDownVote's (quite obvious) solution?
– Jeto
Nov 12 '18 at 22:45
I hadn't tried it yet, I misunderstood the question marks he put at the end of it as saying the original update part was the wrong way to do it. I just overthought it. I did just try it and it appears to be working like I need it to so thank you guys for your help.
– Jeremy Murray
Nov 12 '18 at 22:51
I hadn't tried it yet, I misunderstood the question marks he put at the end of it as saying the original update part was the wrong way to do it. I just overthought it. I did just try it and it appears to be working like I need it to so thank you guys for your help.
– Jeremy Murray
Nov 12 '18 at 22:51
Why are you resetting the userID to the same thing? Seems like you only need to update the tally column
– Don't Panic
Nov 12 '18 at 23:09
Why are you resetting the userID to the same thing? Seems like you only need to update the tally column
– Don't Panic
Nov 12 '18 at 23:09
|
show 2 more comments
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53271059%2fhow-to-set-an-update-query-to-not-update-if-field-int-is-at-0%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53271059%2fhow-to-set-an-update-query-to-not-update-if-field-int-is-at-0%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'"??– user10226920
Nov 12 '18 at 22:36
I think I posted the wrong update part, this is what's been giving the error: $sql="update messagealert set userID='".$userID."', tally=tally - 1 WHEN tally>0 THEN tally-1 ELSE tally where userID='".$userID."'"; and it gives me this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN tally=0 THEN tally-1 ELSE tally where userID='1'' at line 1
– Jeremy Murray
Nov 12 '18 at 22:40
Because it's syntaxically invalid. But you probably don't need a CASE/WHEN anyway (unless you always want to update other columns), what's wrong with @IdontDownVote's (quite obvious) solution?
– Jeto
Nov 12 '18 at 22:45
I hadn't tried it yet, I misunderstood the question marks he put at the end of it as saying the original update part was the wrong way to do it. I just overthought it. I did just try it and it appears to be working like I need it to so thank you guys for your help.
– Jeremy Murray
Nov 12 '18 at 22:51
Why are you resetting the userID to the same thing? Seems like you only need to update the tally column
– Don't Panic
Nov 12 '18 at 23:09