How to filter data (PHP)

[shop73]

Hello everyone,

I have two tables (cities and images)

table cities
-----------
cityID
cityName
cityShow

table images
------------
imageID
imageName
imagePath
cityID

cityID = 1 cityName = Paris cityShow = 'Y'
cityID = 2 cityName = Berlin cityShow = 'Y'
cityID = 3 cityName = New York cityShow = 'Y'
cityID = 4 cityName = Prag cityShow = 'N'
etc...

imageID = 1 paris1 cityID = 1 imagePath = paris1.jpg
imageID = 2 paris2 cityID = 1 imagePath = paris2.jpg
imageID = 3 paris3 cityID = 1 imagePath = paris3.jpg
imageID = 4 berlin1 cityID = 2 imagePath = berlin1.jpg
imageID = 5 newyork cityID = 3 imagePath = newyork1.jpg
imageID = 6 prag cityID = 4 imagePath = prag1.jpg
etc...

I tried with this query

SELECT cities.cityName, cities.cityID, images.imagePath, images.imageName
FROM cities LEFT JOIN images ON cities.cityID=images.cityID
WHERE cities.cityID=images.cityID AND cities.cityShow = 'Y'

but I got a duplicate data for cities.cityID.

cityID=1 cityName=Paris imageName=paris1 imagePath=paris1.jpg
cityID=1 cityName=Paris imageName=paris2 imagePath=paris2.jpg
cityID=1 cityName=Paris imageName=paris3 imagePath=paris3.jpg
cityID=2 cityName=Berlin imageName=berlin imagePath=berlin1.jpg
cityID=3 cityName=NewYork imageName=newyork imagePath=newyork1.jpg

As a result wanted to get the following results:

cityID=1 cityName=Paris imageName=paris1 imagePath=paris1.jpg
cityID=2 cityName=Berlin imageName=berlin imagePath=berlin1.jpg
cityID=3 cityName=NewYork imageName=newyork imagePath=newyork1.jpg

City of Prague is not shown because cityShow = N (No).

Thanks everyone!

Best Regards!

 

[traingamer]

This isn't really a php question - it is a SQL (or database) query question. In any case, you need to have some criteria for which picture you want to display if there are more than one. You may want to use something like this:

SELECT cities.cityName, cities.cityID, images.imagePath, images.imageName
FROM cities LEFT JOIN images ON cities.cityID=images.cityID
WHERE cities.cityID=images.cityID AND cities.cityShow = 'Y'
[blue]   AND images.imageID IN (SELECT MIN(imageID) as tmp from images GROUP BY cityID)
[/blue]

This will return the image with the lowest ID (MIN) from your images table. If you have some other criteria, feel free to substitute that rather than using the MIN function. This may work [em]as is[/em] in your query or may need to be adapted depending on the database you are using.


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[shop73]

It is great!This is what I was looking for.

Sample:

cityID = 1 cityName = Paris cityShow = 'Y'
cityID = 2 cityName = Berlin cityShow = 'Y'
etc...

imageID = 1 paris1 cityID = 1 imagePath = paris1.jpg
imageID = 2 paris2 cityID = 1 imagePath = paris2.jpg
imageID = 3 paris3 cityID = 1 imagePath = paris3.jpg
imageID = 4 berlin1 cityID = 2 imagePath = berlin1.jpg

As a result we have:

-paris - paris1.jpg
- berlin - berlin1.jpg

This is it!

Thank you very much 'traingamer'.

Best regards

 

[shop73]

SELECT cities.cityName, cities.cityID, images.imagePath, images.imageName, cities.description, photographers.photographerName, years.yearName
FROM photographers, years, product LEFT JOIN images ON cities.cityID = images.cityID 
WHERE cities.cityID = images.cityID
AND cities.cityShow = 'Y' 
AND cities.photographerID = photographers.photographerID 
AND cities.yearID = years.yearID
AND images.imageID IN (SELECT MIN(imageID) as tmp from images
GROUP BY cityID)
ORDER BY cityID

I extend this request and it 'works' but with error:

MySQL Error#: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2

Do you know what the problem is?

Best regards

 

[johnwm]

Unmatched brackets? Error 1064 is a parse error. See

http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_parse_error

You would usually do better with MySQL questions if you asked in forum436 after you check the MySQL manual. See faq222-2244 for forum usage guidance.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller