How to add results to the resultset of a MySQL stored procedure?

Question

I'm trying to rewrite my code for searching of keywords in text from PHP to MySQL stored procedure because PHP has too low memory_limit and I'm on a shared hosting so I can't change the memory limit.

What I need to code is this (aho-corasick algorithm):

for every char ch in text do
     do some transitions
     if there is a result for a given state add it to the output (output is position of word in text and keyword)

I don't want anybody to write the procedure but I would like to know if it is possible to append results as I wrote in pseudocode above.

Note: I read the document: http://www.peregrinesalon.com/wp-content/uploads/2009/03/mysql-stored-procedures.pdf and for cycle is easy to program, conditions are easy too, transitions between states may be slow but it seems still possible.

Thanks for an answer!

Answer 1

In a SP you can build your result set in a temp table and manipulate it however you want before you exit. Then the calling procedure can select out what it wants from there. Once the MySQL session closes the temp table will be cleared as well.

(EDIT) http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

CREATE PROCEDURE procedure1
BEGIN
-- create 'results' table
    CREATE TEMPORARY TABLE OUT_TEMP( val0 varchar(20), val1 int);

    DECLARE done INT DEFAULT 0;
    DECLARE a CHAR(16);
    DECLARE b INT;
    DECLARE cur1 CURSOR FOR SELECT val0, val1 FROM <another table>;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

--open table to read from
    OPEN cur1;

    read_loop: LOOP
    FETCH cur1 INTO a, b;
    IF done THEN
        LEAVE read_loop;
    END IF;
    IF <some condition> THEN
        INSERT INTO OUT_TEMP VALUES (a,b);
    ELSE
  -- insert something else
        INSERT INTO OUT_TEMP VALUES (a,b + 10);
    END IF;
END LOOP;

CLOSE cur1;

-- output results 
SELECT * FROM OUT_TEMP;
DROP TEMPORARY TABLE OUT_TEMP;
END