MYSQL Boolean search using table data in same statement

I am looking for the most efficient way to do the following.

I have "table1" with the following columns, ID SavedSearch Price NewLowerPrice

what i need to do is select id and price from table 1, and flag new lower price if the boolean search against another table has a new lower min price for every saved search by the user. here is some code I am using, but i am stuck as to how to use table1's saved search in the following boolean search

drop temporary table if exists tmp;

CREATE TEMPORARY TABLE tmp(
ID INT,
Param2 Varchar(300),
LowestPrice decimal(10,2),
LowerPrice bit,
PRIMARY KEY(ID)) ENGINE=MEMORY;

INSERT INTO tmp
SELECT id, Param2, LowestPrice, 0
FROM saved_searches
where user = 28;


UPDATE tmp
set LowerPrice = 1
WHERE (
SELECT MIN(price)
FROM store_sales 
WHERE MATCH (description) AGAINST (concat('\'', tmp.Param2, '\'') IN BOOLEAN MODE)) > 0;

it errors out in the update saying "incorrect arguments to AGAINST." thanks in advance!

Logically, you're doing something that should make sense, but unfortunately, MySQL's AGAINST() function doesn't allow you to reference a column in your search string. The argument must be a fixed string, or else a query parameter.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html says:

The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

You'll have to do this the hard way:

1. Query for all distinct search params:

   SELECT DISTINCT Param2 FROM saved_searches;
   

2. For each Param2 value, query the min price:

   SELECT MIN(price)
   FROM store_sales 
   WHERE MATCH (description) AGAINST (:param2) IN BOOLEAN MODE)) > 0;
   

3. Update your tmp table when that min price is lower than the currently recorded lowest price for the respective param2 search term.

   UPDATE tmp
   set LowerPrice = 1
   WHERE LowestPrice > :minPrice Param2 = :param2
   

I'm using named parameters in the above examples, the manner you would do with PHP's PDO. But in the stored procedure language, you can use only positional parameters with the ? placeholder.

It can be tricky to code stored procedures that loop over the results of a query and run inner queries inside the loop. Here's a good blog that shows how to do this: Nesting MySQL Cursor Loops by Roland Bouman.