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."'";
Jeremy Murray
  • 1
  • 1
  • 2
    `update messagealert set userID='".$userID."', tally=tally - 1 where tally>0 AND userID='".$userID."'"` ?? –  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
  • It's just carried over from the code I copied from a different page I have. That page the code updates the users last visit in the table upon logging in. I didn't realize it wasn't needed in this case and wasn't messing things up so I hadn't removed it. – Jeremy Murray Nov 13 '18 at 01:15
  • Ah, makes sense. Thanks for satisfying my curiosity ;-) – Don't Panic Nov 13 '18 at 16:24

0 Answers0