StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Thank for your help so far. The idea of Tom Haws proved to be correct. The variables for a SELECT INTO statement have to be user-defined. I edited my code to use cursors, and user-defined variables as followed:</p> <pre class="lang-sql prettyprint-override"><code>delimiter // CREATE PROCEDURE relationTable () BEGIN DECLARE articlehits int; DECLARE looparticles int DEFAULT TRUE; DECLARE loopwords int DEFAULT TRUE; DECLARE done INT DEFAULT FALSE; DECLARE keywordcursor CURSOR FOR SELECT keyword, id FROM keywords; DECLARE articlecursor CURSOR FOR SELECT id FROM articles; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN articlecursor; WHILE (looparticles) DO FETCH articlecursor INTO @articleid; IF done THEN SET looparticles = FALSE; ELSE SET articlehits = 0; OPEN keywordcursor; WHILE (loopwords) DO FETCH keywordcursor INTO @word, @wordid; IF (articlehits < 5) AND NOT done THEN IF (0 < (SELECT COUNT(id) FROM articles WHERE id=@articleid AND CONCAT(title, " ", text) REGEXP @word)) THEN INSERT INTO keyword_article_rel (id, meldungID, wordID) VALUES(NULL, @articleid, @wordid); SET articlehits = articlehits + 1; END IF; ELSE SET loopwords = FALSE; CLOSE keywordcursor; SET done = FALSE; END IF; END WHILE; END IF; END WHILE; CLOSE articlecursor; END; // delimiter ; </code></pre> <p>And now I get an other error that I really can't explain: </p> <blockquote> <p>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN articlecursor; W' at line 6</p> </blockquote> <p>This error confuses me because it can't have a problem with the handler. The handler is declared as in the <a href="http://dev.mysql.com/doc/refman/5.0/en/cursors.html" rel="nofollow">example</a> of the mysql-documentation. Could the problem be that I can't create <strong>two</strong> cursors like this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload