PHP MYSQL "Advanced" JOIN Query

Question

I have a web page that displays a list of events. By default all events are being displayed. The user can use an input field to filter that list. He can either enter a city_code, a city_name or a city_state. Here below my DB tables and their content:

Locations

city_code (pk)  
city_name  
city_state

Events

event_id (pk)  
event_name  
city_code(foreign key)

My idea to achieve what I want is:
1/ Filter the locations table based on the input value:

(SELECT * FROM locations WHERE city_code LIKE input OR city_name LIKE input or city_state LIKE input).

2/ Make a JOIN query between the events table and the filtered locations table.

My problem is that I know how to filter a table, I know how to make a join query, but not how to make both "together". I hope someone can help. Thank you in advance for your replies. Cheers. Marc.

Simon at The Access Group · Accepted Answer · 2012-02-27 10:35:04Z

2

Have you tried just using both together? You should find that it does as you desire. The syntax you are looking for is one of the following

SELECT
    ...
FROM events
INNER JOIN locations
ON locations.city_code = events.city_code
AND (
    locations.city_code = ...
    OR 
    locations.city_name = ...
    OR
    locations.city_state = ...
)

Or the slightly less efficient but equally valid

SELECT
    ...
FROM events
INNER JOIN locations
ON locations.city_code = events.city_code
WHERE (
    locations.city_code = ...
    OR 
    locations.city_name = ...
    OR
    locations.city_state = ...
)

But really you should have given it a try before posting here.

answered Feb 27, 2012 at 10:35

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

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

1 Comment

Marc Over a year ago

Hello Simon. Thank you for the help. I tried with my very little knowledge but did not come with that syntax. Again thank you.

Collectives™ on Stack Overflow

PHP MYSQL "Advanced" JOIN Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related