mysql ignore empty string when using select?

[darksniperx]

I would like to do a query that selects field a,b,c. Trims field c, and ignores the whole record if field c is  empty.

something like this.

SELECT A , B, trim('C') FROM TableA
WHERE C != ''

 

Any ideas on how to get the code work.

[aschk]

Firstly you're using C like a string by enclosing it in single quotes (')

 

You have two options

SELECT A , B, trim(C) FROM TableA
WHERE trim(C) != ''

 

SELECT A , B, trim(C) as 'c' FROM TableA
WHERE c != ''

[darksniperx]

ok, that works, how would I put it inside php.

if($selectAll) {

$all = '';

}

else { $all = "WHERE trim© != ' ' "}

 

$sql = "

SELECT A , B, trim© FROM TableA

{$all}

";

 

exept I cannot get the single quotes to show up.

[darksniperx]

Firstly you're using C like a string by enclosing it in single quotes (')

 

You have two options

SELECT A , B, trim(C) FROM TableA
WHERE trim(C) != ''

 

SELECT A , B, trim(C) as 'c' FROM TableA
WHERE c != ''

 

I have fixed it by:

 

SELECT A , B, C FROM TableA
WHERE trim(C) != ''

[fenway]

SELECT A , B, trim(C) as 'c' FROM TableA
WHERE c != ''

The other posted code is the only way this will work -- you can't use a column alias in a WHERE clause, because the column names aren't "used" until *after* the result set has been collected -- at which point it's too late.  However, you can use it in an ORDER BY / GROUP BY / HAVING clause for the same reason.

[darksniperx]

you got me confused?!? ???, you mean my where clause is not suppose to work, I have also tryed "not like ' ' ".

[fenway]

you got me confused?!? ???, you mean my where clause is not suppose to work, I have also tryed "not like ' ' ".

The code you posted that "worked" is fine... because the expression in in the where clause.

[darksniperx]

ah ok, thx