Query works from pgAdmin. Won't work from PHP with no errors

Question

I'm rebuilding app for my client. I write scripts to migrate data from old db to new one. To prevent empty values in column etiquette_start (what happened in old db), I run

UPDATE items 
SET etiquette_start = 'E' 
WHERE trim(both ' ' from etiquette_start) = '' 
OR etiquette_start is null;

just after importing data. The problem is that scripts works fine on pgAdmin III, but it won't execute on php script. PHP Syntax error isn't the issue here - putting other queries in variable holding query quoted above resulted in successfull data manipulation. There is no PHP error on the page and in logs.

Problem could be solved easily if there were no ' ' like strings in db instead of just empty values or nulls. Any suggestions? — ex3v, Mar 30 '13 at 22:31
What does the database log say? Set `log_statement=all` and try again. What does the database actually get? — Erwin Brandstetter, Mar 30 '13 at 22:39

score 2 · Accepted Answer · answered Mar 30 '13 at 23:23

2

Try this:

UPDATE items 
SET etiquette_start = 'E' 
WHERE length(trim(both ' ' from etiquette_start)) = 0 
OR etiquette_start is null;

answered Mar 30 '13 at 23:23

klin

112,967
15
204
232

The expression `length(trim(both ' ' from etiquette_start)) = 0` does *exactly* the same as `trim(both ' ' from etiquette_start) = ''`, which in turn is *exactly* the same as `trim(etiquette_start) = ''`. There is no reason to do it like this *at all*. – Erwin Brandstetter Mar 31 '13 at 11:47
1

@Erwin You are perfectly right but you probably did not carefully read the question. The author had problem with PHP, not Postgres. – klin Mar 31 '13 at 15:01
How would the presented alternative help with PHP? The OP has an unrelated problem, that's not covered properly in the question. – Erwin Brandstetter Mar 31 '13 at 15:16
1

@ErwinBrandstetter - It should be quite clear from the question that the author does not know how to properly write down empty string constant as a parameter to his sql query in PHP. As we do not see the code I suggested sql query in which he does not have to use empty string. – klin Mar 31 '13 at 16:50
That's not clear *at all* from the question, especially as there are more single quotes in the statement. – Erwin Brandstetter Mar 31 '13 at 16:55
@ErwinBrandstetter - In this case I cannot help you, sorry. – klin Mar 31 '13 at 17:36
I wasn't asking for help. I want to make clear that nobody should use this inefficient query. No offense. – Erwin Brandstetter Mar 31 '13 at 17:37

Erwin Brandstetter · Answer 2 · 2013-03-31T11:50:17.297

0

Either way, you can simplify the trim() call and prevent empty updates:

UPDATE items 
SET    etiquette_start = 'E'
WHERE (trim(etiquette_start) = '' OR etiquette_start IS NULL) -- parens needed
AND    etiquette_start IS DISTINCT FROM 'E';

Since space (' ') is the default character to be removed by trim(), these expressions are 100% equivalent:

trim(etiquette_start)

trim(both ' ' from etiquette_start

edited Mar 31 '13 at 11:50

answered Mar 30 '13 at 23:42

Erwin Brandstetter

605,456
145
1,078
1,228

Query works from pgAdmin. Won't work from PHP with no errors

2 Answers2