SQL UPDATE using CASE WHEN with PHP variable as argument

[andygout]

I have a database storing materials in their respective formats, i.e. The Idiot (novel), Hamlet (play), Star Wars (screenplay), etc.

I’m using a PHP function that removes articles (i.e. The/A/An/', etc.) and creates a variable ($mat_alph, assigned NULL if non-existent) by which it can later be alphabetised. This is only required for titles with articles, but for those without one, I want this saved in the database as a NULL value.

Saving the variable in a NULL state stores it as an empty string rather than an actual NULL value, which causes problems later when filtering results by IS NULL/IS NOT NULL (which I’ll need to do when using SELECT commands with CASE WHEN to call mat_nm for those without an article and mat_alph for those with). I don’t want to save a mat_alph value for titles without an article as this will apply to a huge amount, repeating the mat_nm value and wasting memory.

Is it possible to use CASE WHEN in an UPDATE statement using the PHP variable as the argument?

The below attempt handles titles without articles well enough (i.e. Hamlet updates mat_alph to NULL) but with articles (i.e. The Idiot) creates this error:-

Truncated incorrect DOUBLE value: ‘Idiot’

$sql=	"UPDATE mat SET
	mat_nm='$mat_nm',
	mat_alph=CASE WHEN ('$mat_alph') THEN '$mat_alph' END,
	mat_url='$mat_url',
	frmtid=(SELECT frmt_id FROM frmt WHERE frmt_url='$frmt_url')
	WHERE mat_id='$mat_id'";

Is this possible or do I need a separate SQL UPDATE command solely for updating the mat_alph field?

The problem also applies to the initial INSERT commands and I expect the solution will apply to both.

$sql =	"INSERT INTO mat(mat_nm, mat_alph, mat_url, frmtid)
	SELECT
	'$mat_nm',
	'$mat_alph',
	'$mat_url',
	frmt_id FROM frmt WHERE frmt_url='$frmt_url'";

Thanks in advance.

[SpacePhoenix]

Just a quick note about the PHP aspect, you might well be wide open to potential SQL Injection attack as it appears your possibly letting user-submitted data near the database without being escaped. The best way to escape it is by using Prepared Statements which are available to use in the mysqli_* extension (if you’re using MySQL) or PDO.

If you’re currently using the mysql_* extension, that one has been deprecated as of version 5.5 of PHP and is being removed from version 7 of PHP

[r937]

two comments:

first, it’s awfully difficult to understand how an SQL statement works if it’s written with php variables – any chance you could show the actual SQL that produced the error?

second, the WHEN of a CASE expression usually involves values and an operator (e.g. WHEN columnname = ‘no’) – your use is very puzzling, and i would find a more explicit way of doing whatever it is you’re doing with CASE WHEN (‘something’)

also, “incorrect DOUBLE value” suggests a datatype problem, and you haven’t shown us the datatypes of your columns

[andygout]

All strings are sanitised before entry.

mat_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
mat_nm VARCHAR(255) NOT NULL
mat_alph VARCHAR(255)
mat_url VARCHAR(255) NOT NULL
frmtid INT UNSIGNED NOT NULL

function alph($a)
{
if(preg_match(‘/^(A |An |The |\W+)(\S+.)$/i’, $a)) {$alph=preg_replace('/^(A |An |The |\W+)(\S+.)$/i’, ‘$2’, $a);}
else {$alph=NULL;}
return $alph;
}

$mat_alph=alph($mat_nm);

Then the SQL UPDATE statement as above is the one that creates the error (Truncated incorrect DOUBLE value).

I have tried making the CASE argument as below, but this does not save non-NULL values as NULL, just as empty strings (although titles with articles are handled properly).

mat_alph=CASE WHEN (‘$mat_alph’ IS NOT NULL) THEN ‘$mat_alph’ END

[r937]

your php (if that’s what it is) is greek to me, sorry

try –

mat_alph=CASE WHEN NULLIF('$mat_alph','') IS NOT NULL THEN '$mat_alph' END
that checks to see if it’s an empty string first

that also, if i’m guessing right, will make your DOUBLE error go away

[andygout]

Amazing - thank you!

This also works:-

mat_alph=CASE WHEN ‘$mat_alph’=‘’ THEN NULL ELSE ‘$mat_alph’ END

[andygout]

And this is a shorter version (NULL is given as the default value for non-matching cases):-

mat_alph=CASE WHEN ‘$mat_alph’!=‘’ THEN ‘$mat_alph’ END

Thank you again!

[r937]

your solutions will fail if, for some reason, the php code actually passes a null

mine covers both conditions

future proofing

[r937]

[quote=“andygout, post:1, topic:114817, full:true”]Saving the variable in a NULL state stores it as an empty string rather than an actual NULL value, which causes problems later when filtering results by IS NULL/IS NOT NULL (which I’ll need to do when using SELECT commands with CASE WHEN to call mat_nm for those without an article and mat_alph for those with). I don’t want to save a mat_alph value for titles without an article as this will apply to a huge amount, repeating the mat_nm value and wasting memory.[/quote]by the way, this is well thought out and quite sensible

since you appreciate brevity, you can use COALESCE instead of your CASE WHEN

there’s also a way to do it with pure SQL (i.e. no stored extra column) – MySQL: Proper sorting of a column - #22 by casbboy

[andygout]