I'm working through project 1 in the CS50 Web Programming course. I have created a search form which is just one text box in the middle of the page where the user can type in the ISBN number, the title or the author of any of the books in the database. My search query looks like this now:

query = request.form.get("search")
search = "%" + query.split(" ")[0] + "%"

result = db.execute("SELECT * FROM books WHERE isbn LIKE :search OR title LIKE :search OR author LIKE :search", {"search": search})

This takes the first word of the search and returns the matching results from the database.

If I search for "Dark Legend", the book called "Dark Legend" is 9th on the list and it should be the top result.

I'm struggling to understand how to implement a multi-word search here, so that the user can type in any number of words and get the best results at the top.

Can somebody help, please?

My books table structure:

isbn    character varying   
title   character varying   
author  character varying   
year    integer

You might consider using different sqls to create multiple result sets, then returning a combination of the results. The first query could look for an exact match (=) on the query string, and subsequent queries could look for wild card (LIKE and %) matches for the first word in the query string or even for each word in the query string.

Another option might be experimenting with your sql's 'UNION'. In this specific example, find an "exact match" first, something like SELECT * from books where isbn = :query or title = :query or author = :query...... Then UNION with the "LIKE" query(ies).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .