MySQL -> Loop through a table, running a stored procedure on each entry

2018-10-21 22:50:46

I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books.

I figured out how to get the word count for each word using:

SELECT SUM

(

ROUND

(

(LENGTH(pageText) - LENGTH (REPLACE (pageText, "Word", "")))

/LENGTH("Word")

)

) FROM pages WHERE bookID = id;

Which works wonderfully for counting the words. BUT it requires me to go through each book, and get each word out, and run it through that function (I have it saved as a Stored Procedure.)

I have a table that contains each word, with no duplicates.

My question: is there a way I can do some kind of "for each" loop on the Words table using my stored procedure?

ie. pass the stored procedure a book ID and a word and record the result. Doing EVERY word, for EVERY book. Thus saving me a LOT of manual time... Is this something I should even be doing from the DB side? Should I attempt it with PHP instead?

Hone

  • Create a second procedure that uses two nested cursors.

    Cursors in stored procedures allow you to do a very non-SQL-like thing: iterate through a result set one row at a time, putting the selected column values into variables and doing things with them.

    They are easily misused, since SQL, being declarative rather than procedural, should usually not need "for each"-type operations, but in this case, it seems like a valid application.

    Once you get the hang of them, cursors are easy, but they do require a structured approach in their supporting code that isn't always intuitive.

    I recently provided some fairly standard "boilerplate" code for working with a cursor to call a stored procedure in an answer on Stack Overflow, and I'll borrow very heavily from that answer, below.

    Using a cursor requires some standard boilerplate code to surround it.

    You SELECT the values you want to pass, from wherever you're getting them (which could be a temporary table, base table, or view, and can

    2018-10-21 23:42:38
  • close cursor1; is missing

    OPEN - CLOSE are going together for cursors

    2018-10-22 00:03:49