Select values from not empty column in mysql

Question

Table Photos-------

photo1  imagem1.jpg
photo2  imagem2.jpg
photo3  empty
photo4  empty
photo5  enpty

I have a table, with 5 columns who represents photos. So, I want to select * from table, all columns... but if one or more of them are empty, I don't want to select. is that possible?

For example, if I do this

Select * from table Photos where photo1 is not null, and photo2 is not null, and photo3 is not null and photo4 is not null and photo5 is not null

that is not possible, because if one of them are empty... the select don't give any values.

anyone know another way?

and also, i need that because if I want to do this: the problem is.... when I use php for example this

if($project['photo5']!='' and $project['photo5']!=null){ 
(here i show the picture)
}

If the row is empty... php is ignored and show a error image

by empty do you mean the string 'empty' or do you mean NULL? and what's the table structure for photo table it appears to be 2 columns. If so what are the column names? If there are 5 columns called photo1 through photo5 then I have a different approach. based on the PHP and query there are 5 columns... — xQbert
– xQbert, Commented Jan 26, 2016 at 0:01

xQbert · Accepted Answer · 2016-01-25 23:39:08Z

1

Concat() will return null if any one of the values concat is null (mySQL DOCS) Since you want to only show records where none are null concat together and check for not null.

Select * 
from table Photos 
where concat(photo1,photo2,photo3,photo4,photo5) is not null

AND... your approach should work if you used or's instead of AND and possibly a not. I just find the above easier to read.

edited Jan 25, 2016 at 23:39

answered Jan 25, 2016 at 23:33

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Luís Morim Over a year ago

if I have photo1, photo2, photo3 with content and photo4, photo5 empty i need that my select, only return the photo1, photo2, and photo3... not all another empty content

Luís Morim Over a year ago

the problem is.... when I use php for example this if($project['photo5']!='' and $project['photo5']!=null){ (here i show the picture) } If the row is empty... php is ignored and show a error image

xQbert Over a year ago

A select statement will always return the columns in the select. So if you want photo1-5 columns you will get 5 columns. The application (phP) will need to handle the absence of data for the column.

Collectives™ on Stack Overflow

Select values from not empty column in mysql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related