So I’m still working on all that SQL stuff - now it’s up to figuring out how to get the multiple keyword search going again.
I have a method, not sure how efficient it is (I’m going to run some tests) but so far it’s working.
-
/****** GETTING A LIST OF IMAGES BACK *****/
-
SELECT
-
it.Filename,
-
it.Record_ID AS Record_ID,
-
it.Path,
-
it.File_Description,
-
"image_server" AS database_name,
-
"1" AS using_databases,
-
"1" AS databaseID
-
FROM
-
image_server.Item_Table it
-
JOIN
-
image_server.Item_Keyword ik
-
ON
-
ik.Item_ID = it.Record_ID
-
JOIN
-
image_server.Keyword k
-
ON
-
k.Record_ID = ik.Keyword_ID
-
WHERE
-
k.Keyword = ‘dog’
-
AND
-
ik.Item_ID IN (
-
SELECT
-
ik.Item_ID
-
FROM
-
image_server.Item_Keyword ik
-
WHERE
-
ik.Keyword_ID = (SELECT
-
k.Record_ID
-
FROM
-
image_server.Keyword k
-
WHERE
-
k.Keyword = ‘cat’) )
Update: I figured I’d use that search method above for now until I find a better method.
That’s not the end of my troubles, however. I’ve got some issue with a JOIN statement that’s not returning the right result set (the right row):
-
SELECT
-
sl.id,
-
sl.userID,
-
sl.insertdate,
-
sl.searchString,
-
sl.galleryID,
-
sl.imageType,
-
sl.imageColor,
-
sl.databaseID,
-
sl.using_database,
-
sg.Name,
-
db.dbcommonname
-
FROM
-
CE_search_log2 sl
-
JOIN
-
Saved_Galleries sg
-
ON
-
sl.galleryID = sg.Record_ID
-
JOIN
-
CE_dblist db
-
ON
-
db.id = sl.databaseID
-
WHERE
-
sl.userID = ‘cedge’
-
ORDER BY
-
sl.insertdate DESC
-
LIMIT 0,1
-
– returns search ID # 186
-
-
SELECT * FROM CE_search_log2 ORDER BY insertdate DESC LIMIT 0,1
-
– returns search ID # 194
If you notice, the first query returns the CE_search_log.id row of 186. The second query returns the correct row of 194. I think it might be because of my join statement, but I’m not sure. Argh, frustrating!
Update (again): Looks like it was a JOIN problem. I needed a LEFT JOIN for the CE_dblist line. D’oh!
Categories