CODEWITHSUNDEEP
phppostgresqlphppgadmin
ex3v
ex3v

Reputation: 3566

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

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.

Upvotes: 2

Views: 756

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656734

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

Upvotes: 0

klin
klin

Reputation: 121604

Try this:

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

Upvotes: 2

Related Questions