INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to filter data (PHP)

How to filter data (PHP)

(OP)
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

CODE

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!

RE: How to filter data (PHP)

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:

CODE

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'
   AND images.imageID IN (SELECT MIN(imageID) as tmp from images GROUP BY cityID)

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

RE: How to filter data (PHP)

(OP)
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



 

RE: How to filter data (PHP)

(OP)

CODE

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

RE: How to filter data (PHP)

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: MySQL AB: MySQL after you check the MySQL manual. See FAQ222-2244: How to get the best answers for forum usage guidance.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close