I'm trying to build a related images section that uses image tags.
My DB Structure is pretty straight forward:
table: images table: image2tag table: tags
|image_id|image_path| |t2i_id|image_id|tag_id| |tag_id|tag_name|
What I would like to do is find images that share a high relation to the current image based on the tags in both. From going through other posts I think I might need to use MATCH AGASINT, the values being compared are numeric IDs, but according to dev.mysql.com MATCH AGAINST is for comparing text strings, so I'm confused.
I've found examples on stackoverflow but none where joins are involved, could someone help me out with this. What I've tried so far:
$getRelated = $conn -> prepare("SELECT *,
MATCH(it.image_tag_id) AGAINST (?) AS score
FROM images i
LEFT JOIN image2tag i2t
ON i.image_id = i2t.i2t_image_id
LEFT JOIN image_tags it
ON i2t.i2t_tag_id = it.image_tag_id
WHERE MATCH(it.image_tag_id) AGAINST (?)
ORDER BY score DESC LIMIT 5");
$getRelated -> bindParam(1, $tags, PDO::PARAM_STR);
$getRelated -> bindParam(2, $tags, PDO::PARAM_STR);
$getRelated -> execute();
I think I'm going about this the wrong way, any help would be appreciated.
It's a long time ago I used this.
You need fhe following:
Numeric is not possible. See the official documentation: https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Maybe you can split the logic: