Combine MySQL Queries in PHP

Question

I have the following MySQL queries:

Query #1:

SELECT imgURL, imgTitle, FROM images

Query #2:

SELECT DISTINCT imgFamily FROM images

Query #3:

SELECT DISTINCT imgClass FROM images

I'm wondering if it is possible to combine them into one query? In other words, SELECT imgURL, imgTitle, DISTINCT imgFamily, and DISTINCT imgClass.

When I attempted to do it as above (without the and), it failed. Any suggestions?

The result I get is the following: MySql ErrorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT imgFamily FROM images

The result I expect is to not have an error, and have rows returned.

DISTINCT is per result row, not per individual result field. Are you trying to return the entire list of images along with the entire list of imgFamilies and entire list of imgClasses? That doesn't make sense, as they are different pieces of information and must thus be separate queries. — Wiseguy
– Wiseguy, Commented Aug 25, 2011 at 19:02
To fix the syntax error, you'd have to remove all DISTINCT keywords except for one immediately after SELECT (optional, obviously). However, as explained above, I don't think those results would yield what you're actually looking for. — Wiseguy
– Wiseguy, Commented Aug 25, 2011 at 19:05
If you were to do SELECT imgURL, imgTitle, imgFamily, imgClass ..., that would give you the family and class that each particular image is in. If 30 of the images had the same family, then those 30 result rows would all have the same value in family. I don't think that's what you want, right? — Wiseguy
– Wiseguy, Commented Aug 25, 2011 at 19:10

Marc B · Accepted Answer · 2011-08-25 19:03:23Z

1

The distinct keyword in MySQL is a synonym for distinctrow - eg. If you want a 'select distinct'. you're going to get only the rows where the combination of fields has not yet appeared in the results. By selecting non-distinct fields, you won't get ANY distinct results. This has to be done as separate queries.

answered Aug 25, 2011 at 19:03

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Comments

piotrp · Accepted Answer · 2011-08-25 19:05:31Z

1

You can combine them with the UNION keyword:

SELECT imgURL, imgTitle, FROM images
UNION
SELECT DISTINCT imgFamily, NULL FROM images
UNION
SELECT DISTINCT imgClass, NULL FROM images

but it's a really bad idea. All columns in subsequent queries must have the same type as columns from all subsequent queries, or some data may get truncated.

Generally, you shouldn't combine unrelated queries.

answered Aug 25, 2011 at 19:05

piotrp

3,9841 gold badge26 silver badges28 bronze badges

Comments

Andrey · Accepted Answer · 2011-08-25 19:06:27Z

1

SELECT distinct imgURL, imgTitle, imgFamily, imgClass 
FROM images

The above will give you unique row.

SELECT imgURL, imgTitle, imgFamily, imgClass 
FROM images

This one will give you all data.

it is hard to figure out what you are trying to do.

answered Aug 25, 2011 at 19:06

Andrey

1,8281 gold badge17 silver badges28 bronze badges

Collectives™ on Stack Overflow

Combine MySQL Queries in PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related