How Update table if ... else

Question

I want update table, but something goes wrong. How can I update my table if something is TRUE or NOT. For example if it is true I want set value 0, if not increase +1. Here my code:

$fp = @fsockopen($host, $port, $errno, $errstr, 30);
            if ($fp) { 
                print "<TR><TD>$idt</TD><TD>$name<TD>OK</TD><TD>$quantity</TD></TR>\n";
            } else { 
             print "<TR><TD>$idt</TD><TD>$name<TD>Error</TD><TD>$quantity</TD></TR>\n";
                }

if ($conn->query($fp) == TRUE) {
    $resetsql = "UPDATE addresses SET count=0";
} else {
   $updatesql = "UPDATE addresses SET count=count+1";
}

Instead of checking the value of `$fp`, you want to run a query and check it's result. — Aryeh Beitz, Oct 12 '19 at 22:16
[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 12 '19 at 22:51

score 0 · Answer 1 · answered Oct 12 '19 at 21:24

0

You seem to be looking for CASE:

UPDATE addresses SET cnt = CASE WHEN :param = 1 THEN 0 ELSE cnt + 1 END

If :param is 1, then 0 will be assigned to cnt. Else, cnt is incremented.

In MysQL this can shortened as:

UPDATE addresses SET cnt = CASE WHEN :param THEN 0 ELSE cnt + 1 END

answered Oct 12 '19 at 21:24

GMB

216,147
25
84
135

Maybe OP looks for `execute()` or something like that also? – u_mulder Oct 12 '19 at 21:25
Maybe something simplier? – xyz91 Oct 12 '19 at 21:28
@xyz91: this gives you a simple query that gets the job done at once, instead of implementing the logic in your application. You just need to provide the query with a `0/1` value... – GMB Oct 12 '19 at 21:30
I found something like that: UPDATE table SET ( failuretime=NOW(), count=count+1 ) UPDATE table SET ( failuretime=null, count=0 ) How to use it – xyz91 Oct 12 '19 at 21:33
Ok I try it. Should I paste like that? if ($conn->query($fp) == TRUE) { $resetsql = "UPDATE addresses SET cnt = CASE WHEN :param THEN 0 ELSE cnt + 1 END"; – xyz91 Oct 12 '19 at 21:45

How Update table if ... else

1 Answers1