There are many threads on this but I can't seem to find a solution that will work. Here is the query I have:
$sql = "SELECT DISTINCT `People`,`People2`,`People3`,`People4`,`People5`
FROM `album1`
WHERE `People` != '' ORDER BY `People`";
I'm running into multiple problems. First, duplicates are still showing. Also, I can't figure out how to do a WHERE on multiple columns...
The tables look like this, for example.
People | People2 | People3 | People4 | People5
--------+----------+---------+---------+--------
Alex | Frank | | | John
Alex | | John | Frank |
They are used essentially for "tagging" a photo. Since the name of a person could be entered in any of the 5 People input fields, names end up all over the place.
All I'm doing is trying to get the name to show up once. People != '' is do not show empty cells.
Any ideas? Please let me know if I need to include more information.
You should denormalize this, so you only have one People column. You probably should call it Person to emphasize its singularity.
But, if you can't do that in your data model for some reason, then you can do this. It makes a virtual single Person column.
SELECT DISTINCT People as Person, Place, Year, Filename, Orientation
FROM (
SELECT People AS People, Place, Year, Filename, Orientation
FROM ALBUM1
WHERE People IS NOT NULL and People <> ''
UNION ALL
SELECT People1 AS People, Place, Year, Filename, Orientation
FROM ALBUM1
WHERE People1 IS NOT NULL and People1 <> ''
UNION ALL
SELECT People2 AS People, Place, Year, Filename, Orientation
FROM ALBUM1
WHERE People2 IS NOT NULL and People2 <> ''
/* etc */
)a
ORDER BY People, Place, Year, Filename, Orientation
First, "distinct" only works on whole result rows, so when you select 3 columns, they will be only distinct in combination, for example result rows "A,B,C, and "A,A,B" are distinct, but another "A,B,C" will be dismissed.
So, assuming you want to generate a list of all people in your album (correct me if I'm wrong) you need to jam all columns together to make DISTINCT work, where UNION comes to mind:
select people from album where...
UNION DISTINCT
select people2 from album where...
UNION DISTINCT
select people3 from album where...
(you don't really need to state DISTINCT in the union-clause, since it is default behaviour)
This connects all the "people" columns to a single list where DISTINCT can operate on.
And on a side note, the other commenters and answers are right, you should normalize this.
Try something like this (it will remove the blank columns), BUT, I very much agree with Jon C, this is a workaround for an improper database design...
select id,max(people) as People,max(people2) as Pep2,
max(people3) as Pep3,max(people4) as Pep4,max(people5) as Pep5
from
(
select id,people,'' as People2,'' as People3,'' as People4,'' as People5
from dbo.album
where people <> ''
union
select id,'',people2,'','',''
from dbo.album
where people2 <> ''
union
select id,'','',people3,'',''
from dbo.album
where people3 <> ''
union
select id,'','','',people4,''
from dbo.album
where people4 <> ''
union
select id,'','','','',people5
from dbo.album
where people5 <> ''
) xx
group by id
I would take a totally different approach: if it's too difficult/complicate to write an sql for such a simple task - then maybe the table structure should be different!
If I understood correctly, what you're trying to achieve is a display of "who tagged who", meaning, you always want the first column (people) to be != NULL, and then you want the information from the other columns (ppl2,ppl3...) that are not NULL.
If that's correct, then I would change the structure of this table to hold only two columns: "tagger" and "tagged". There's simply no need for all the other columns since every row represents only one person that tagged another person.
With this implementation it will be very easy for you to select all the people that were tagged by Mark, or all the people that tagged Robert.
Hope that helps!