Populating a list of Related Items

[Pawn]

Hi guys. I'm creating a simple "Item Details" page for my book site that barfs up data on whatever ID it gets in the querystring. My SQL problem relates to creating a list of "Related Items" ordered by relevance.

 

To populate my list, I threw a query to get anything that matched any of a few criteria.

 

SELECT * FROM books WHERE author = $item['author'] OR subject = $item['subject'] OR binding = $item['binding']

 

Simply put, what I'd like to do is order the results by how many criteria they match. I don't know if this is a feasible method, but it gives you an idea of what I'd like to achieve.

 

I'd be extremely grateful for any help.

[fenway]

Well, you can use SUM() and IF() to roll-your-own counter.

[Pawn]

This seems to work, but it's ugly and grossly inefficient. Can anyone suggest a better way? I'm not familiar with using SUM() in this context.

 

-- Item Details

SELECT b.*, a.first_name, a.last_name, a.initials, c.title AS category, s.title AS subject, t.title AS type
FROM books AS b
JOIN authors AS a ON a.author_id = b.main_author_id
JOIN categories AS c ON b.category_id = c.category_id
JOIN subjects AS s ON b.subject_id = s.subject_id
JOIN types AS t ON b.type_id = t.type_id
WHERE object_id=".$id;

-- Related items

SELECT b.title, a.last_name AS author, 
IF( b.category_id =$book['category_id'], 1, 0 )
+ IF( b.subject_id =$book['subject_id'], 1, 0 )
+ IF( b.type_id =$book['type_id'], 1, 0 )
+ IF( b.main_author_id =$book['main_author_id'], 1, 0 )
+ IF( b.binding =$book['binding'], 1, 0 ) AS priority
FROM books AS b
JOIN authors AS a ON b.main_author_id = a.author_id
WHERE ( object_id <> $book['object_id'] )
AND ( category_id =$book['category_id']
OR subject_id =$book['subject_id']
OR type_id =$book['type_id']
OR main_author_id = $book['main_author_id']
OR binding = $book['binding'] )
GROUP BY b.title ORDER BY priority LIMIT 0 , 5

[fenway]

Well, a FULLTEXT search with return relevance, but if you want any control over it, you have to do it yourself -- or use a third-party solution like sphinx.

[Pawn]

Thanks - I feel sort of dumb. Of course my site is going to need an internal search, and it makes sense to set that up first and use it here. Is Sphinx what you'd recommend?

[fenway]

Thanks - I feel sort of dumb. Of course my site is going to need an internal search, and it makes sense to set that up first and use it here. Is Sphinx what you'd recommend?

If you can suffer through the setup, then yes.... otherwise, weighting it like you've written may be inefficient but appropriate for small workloads.