UPDATE a field only if it is zero

[Pedroski55]

I want to save the weekly logon times of the students in the table logon_times_20BECW.

This table just has columns id, studentnr, Week1 to Week19

I get the logon times from the table allstudents20BE

But I only want to UPDATE the table logon_times_20BECW IF the field is zero.

To start with, all fields are zero. The query below works.

UPDATE logon_times_20BECW, allstudents20BE SET logon_times_20BECW.Week2=IF(logon_times_20BECW.Week2=0,allstudents20BE.logon_time, logon_times_20BECW.Week2) WHERE logon_times_20BECW.studentnr = 2025010101

This query works, but when the value of the field is not zero, I get an error. I don’t know how to avoid that.

Error:

#1292 - Truncated incorrect DOUBLE value: ‘2022-02-16 10:48:48’

[CodingHelp]

sounds like you first need to make an if statement to check if the field is 0.
I guess something like this: (btw not sure about it because I can’t see the code )

if($row['logon_times_20BECW'] = 0;){
$sql = "UPDATE logon_times_20BECW, allstudents20BE SET logon_times_20BECW.Week2=IF(logon_times_20BECW.Week2=0,allstudents20BE.logon_time, logon_times_20BECW.Week2) WHERE logon_times_20BECW.studentnr = 2025010101";
// and all the other code you want to add to it
}else{
//what you want to do if the field isn't zero
}

[Pedroski55]

Thanks for your reply. You gave me an idea!

I can read the content of the cell/field first in PHP. If it is zero, go ahead and write, if not, exit;

I didn’t think of that!

But I’d still like to know how to do this with a simple MySQL query in phpMyAdmin!

From what I could find, the IF clause works like this: IF(condition, true_value, false_value)

So I thought, if the field concerned was not ZERO, nothing would happen. But I am still getting the error.

[benanamen]

@CodingHelp

if($row['logon_times_20BECW'] = 0;)

This is wrong. A single equals is an assignment. You need two equals

[Pedroski55]

Yes, I noticed that, not a big problem.

I solved my original problem, but I would still like to know how to implement IF directly in MySQL query.

I looked at IF … THEN, CASE but I can’t get it!

[benanamen]

Try this…

https://www.w3resource.com/mysql/control-flow-functions/if-function.php

[Pedroski55]

Thanks for the link. I will try to make it work!

I get the feeling I will need a function.