MySQL与JOIN对抗

Question

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.

Answer 1

It's a long time ago I used this.

You need fhe following:

• myisam
• char, varchar or text datatype
• full text index

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:

• Do a full text search in your textual image tags
• Do the joins with the image tags id from above