PHP MySQL search over 3 fields in 3 tables

Question

I've got three tables: bands, gigs, and venues. I want to be able to search by band name, band hometown, and venue name. Here's the general idea of what my tables look like:

bands

ID    |    NAME      |    LOCATION
------|--------------|------------
1     | Spinal Tap   | New York, NY
2     | Jimi Hendrix | Woodstock, PA

gigs

ID    |    VENUE_ID    |    START_TIME    |    BAND_ID
------|----------------|------------------|--------------
1     |    1           |    1371171600    |    1
2     |    2           |    1371171600    |    1
3     |    1           |    1371171600    |    2
4     |    2           |    1371171600    |    1

venues

ID    |    NAME
------|---------------------
1     |  Madison Square Garden
2     |  Jefferson Round Garden

So searching by band name and location is easy. Something like:

  SELECT id,name,location 
    FROM bands 
   WHERE name LIKE '%$search_string%' OR location LIKE '%$search_string%' 
ORDER BY name

What if I want to include the possibility of searching by venue name? Here's my horribly botched attempt:

    SELECT bands.id,bands.name,bands.location,venues.name 
      FROM bands 
     WHERE name LIKE '%$search_string%' OR location LIKE '%$search_string%' 
INNER JOIN gigs 
        ON bands.id=gigs.band_id 
INNER JOIN venues 
        ON gigs.venue_id=venues.id 
     WHERE start_time>'$now' 
  ORDER BY bands.name

I'm obviously new to this whole inner join thing...

score 2 · Accepted Answer · answered Jun 19 '13 at 21:26

You were close; just a few tweaks needed:

SELECT
  bands.id,
  bands.name,
  bands.location,
  venues.name
FROM bands
INNER JOIN gigs ON bands.id = gigs.band_id
INNER JOIN venues ON gigs.venue_id = venues.id
WHERE start_time>'$now'
  AND (
     bands.name LIKE '%whatever%' OR
     bands.location LIKE '%whatever%' OR
     venues.name LIKE '%whatever%'
      )

The parentheses in the WHERE are important because OR has a lower precedence than AND.

score 1 · Answer 2 · answered Jun 19 '13 at 21:24

There is somnething wrong in your JOIN, you should first pass through tables and join them and the use WHERE clauses. As you see i used ALIAS for table and i used those prepended to column names to let database understand what table are from.

SELECT a.id,a.name,a.location,c.name 
FROM bands a
INNER JOIN gigs b
ON a.id=b.band_id 
INNER JOIN venues c
ON b.venue_id=c.id 
WHERE (a.name LIKE '%$search_string%' OR a.location LIKE '%$search_string%' OR C.name LIKE '%search_string%')
AND b.start_time>'$now' 
ORDER BY a.name

PHP MySQL search over 3 fields in 3 tables

bands

gigs

venues

2 Answers2