Search mysql using multiple variable from multiple tables

Question

I'm using php with mysql

Hello I have a couple of tables with information about books.

One table is like this

id, bookTitle, BookText

The other is like this

id, bookid, amountofsales

I would like to search the text of the book, and the book title from one table, but also be able to include amountofsales from the other table to give the results a possible bias.

So for instance if book 1 had 50,000 sales, and matched the search "oil" but book 2 had 23,000 sales but also matched "oil" book 1 would be slightly more relevant.

However this should only give a small bias because if one matches oil more then that should still be the top result.

Any ideas?

Answer 1

You can relate two tuples from two tables by outlining the MySQL query as follows:

SELECT B.bookTitle, B.bookText, S.amountOfSales FROM book B, sales S WHERE S.id = B.id ORDER BY S.amountOfSale DESC;

This will verify that the id's match (meaning that the different table is relevant, as the id's are the same), and pull all the relevant data. Then you just have to interpret the data.

Be careful with wild-card searches though, they can pull up a lot of unrelated data, say for instance a book about foil may pull up before oil.

Answer 2

You would have to do some joining of your tables:

$searchQuery = "oil";

$sql = "SELECT a.bookTitle, a.BookText, b.amountofsales FROM table1 a, table2 b WHERE a.id = b.bookid AND (a.bookTitle LIKE '%".$searchQuery."%' OR a.BookText LIKE '%".$searchQuery."%') SORT BY b.amountofsales DESC";

$result = mysql_query($sql);
while( $obj = mysql_fetch_object($result) )
{
  echo $obj->bookTitle." has been sold ".obj->amountofsales." times and is relevant to your search";
}

Please have in mind that I haven't tested the code above, but it might give you an idea.