SQLite, MySQL, PHP: Ternary operator (IF() statement) in MySQL and SQLite SQLite, MySQL, PHP: operatur ta 'tlett fibri (IF () dikjarazzjoni) fir MySQL u SQLite

Posted on 03. Posted on 03. Feb, 2010 by Dragos in Coding , MySQL , PHP , SQLite Frar, 2010 minn Dragos fl-Kodifika, MySQL, PHP, SQLite

While working on a proxy checker tool for one of my projects, I was struggling on how to execute a conditional query on my SQLite database to update a column only if its value is greater than 0 (it would be pointless in my case to let the script update the column with negative values). Filwaqt li taħdem fuq għodda kontrollur prokura għal waħda ta 'proġetti tiegħi, jien dwar kif kienet qed titħabat biex tesegwixxi mistoqsija kondizzjonali fuq id-database SQLite tiegħi biex jiġi aġġornat kolonna biss jekk il-valur ikun ikbar minn 0 (ikun inutili fil-każ tiegħi li tikri l - iskrittura taġġorna l-kolonna bil-valuri negattivi).

In Mysql one would do like this (we use the ternary operator IF(to_check_expression>0,expression1,expression2) ): Fil Mysql wieħed li ser jagħmlu bħal din (li nużaw l-operatur ta 'tlett fibri IF (to_check_expression> 0, expression1, expression2)):

update table_name set column_name=IF((column_name-1)>0,(column_name-1),0)

However, in SQLite this method will not work. Madankollu, fil SQLite dan il-metodu mhux ser taħdem. Instead I had to use the case when (condition) then expression1 else expression2 end method. Minflok I kellhom jużaw il-każ meta (kundizzjoni) imbagħad expression1 inkella expression2 tmiem il-metodu.

The above query in MySQL can be rewritten like this in SQLite: Il-mistoqsija t'hawn fuq fil MySQL tista 'tinkiteb mill-ġdid bħal din fl SQLite:

update table_name set column_name=case when (column_name-1)>0 then (column_name-1) else 0 end

That's it. That's it. I hope it helps someone! Nispera li jgħin xi ħadd!