Forum Moderators: open
Catalog search:
SELECT t.track_id, t.track_name, t.vol_id, v.vol_id, v.vol_name
FROM tracks AS t
INNER JOIN vol AS v ON (t.vol_id = v.vol_id)
WHERE (t.vol_id = %s OR -1 = %s)
%s is the _GET value or -1 if not set. The catalog results return the catalog data.
The catalog search is simplified, I've got 3 more INNER JOINS just like the one listed here for the 'vol' table.
The soundslike query is nested in a while loop within the catalog results. Each catalog item can have multiple results here.
SoundsLike search:
SELECT ts.track_id, ts.sdslike_id, s.sdslike_id, s.sdslike_artist
FROM tracks_sdslike AS ts
INNER JOIN sdslike AS s ON (ts.sdslike_id = s.sdslike_id)
WHERE ts.track_id = %s
%s is the track id from the catalog search above.
Where I am running in to trouble is that I can't just plug in the soundslike ID to the catalog search. The track_id will have multiple matches. You can't match 1,2,3 to 1, or can you?!
Here's my table structure:
tracks
+-------------------------+
+ track_id ¦ track_name
+-------------------------+
+ 1. . . . .¦ Name1 . . . +
+ 2. . . . .¦ Name2 . . . +
+ 3. . . . .¦ Name3 . . . +
+ 4. . . . .¦ Name4 . . . +
+ 5. . . . .¦ Name5 . . . +
+ 6. . . . .¦ Name6 . . . +
+-------------------------+
tracks_sdslike
+-------------------------+
+ track_id ¦ sdslike_id
+-------------------------+
+ 1. . . . .¦ 1 . . . . . +
+ 4. . . . .¦ 1 . . . . . +
+ 5. . . . .¦ 1 . . . . . +
+ 1. . . . .¦ 2 . . . . . +
+ 3. . . . .¦ 2 . . . . . +
+ 5. . . . .¦ 2 . . . . . +
+ 6. . . . .¦ 2 . . . . . +
+ 1. . . . .¦ 3 . . . . . +
+ 4. . . . .¦ 3 . . . . . +
+-------------------------+
sdslike
+--------------------------+
+ sdslike_id ¦ sdslike_name
+--------------------------+
+ 1. . . . . ¦ Name1 . . . +
+ 2. . . . . ¦ Name2 . . . +
+ 3. . . . . ¦ Name3 . . . +
+--------------------------+
The search I would expect to return only a handful of tracks per soundslike id.
ex. search for sdslike_id = 1, results would be: track_id 1, 4, 5.
I know this must be simple. Should I use another JOIN? or subselect?
Any general best practices for developing search pages? (ha! an easier question...)
SELECT t.track_id, t.track_name, t.vol_id, v.vol_id, v.vol_name,
ts.track_id, ts.sdslike_id, s.sdslike_id, s.sdslike_artist
FROM tracks AS t
INNER JOIN vol AS v ON (t.vol_id = v.vol_id)
INNER JOIN tracks_sdslike AS ts ON (t.track_id = ts.track_id)
INNER JOIN sdslike AS s ON (ts.sdslike_id = s.sdslike_id)
WHERE (t.vol_id = %s OR -1 = %s)
INNER JOIN tracks_sdslike AS ts ON (t.track_id = ts.track_id)
Thanks. I did end up using that. What I think I was doing was putting too many searches into one query... I separated the searches into 5 separate submit forms, and then set up an if statement for each of the forms. If submit this form, use this query. It was just way too complicated to REALLY merge all the forms into one big messy query. Now I have 5 medium messy queries.
