- Is this how to overrun?
- Is this homebrew variant of True Strike too strong to be a cantrip?
- As DM, how do I deal with PCs that expect everything in the game to be relevant to the story?
- How to plan/run interesting sessions where the PCs have extensive resources?
- Change text message forwarding from a device other than iPhone?
- Disable ECDSA SSH Host keys
- Install Mojave 10.14.2 beta on a different partition
- Command + Tab not working as intended
- MacOS turn off sync with iPhone for a while
- Isn't the Exponential Time Hypothesis incompatible with Baker, Gill and Solovay?
- Can't open checkout select element on mobile
- How to list all network sites on one page
- BuddyPress - Search members by name and also by username
- Display custom image field in user profile
- WP-Buddypress Search Filter
- Woocommerce merging cart and checkout page together
- Modals using loops and ACF
- How to generate a widget form from an array with accordions/groups and save each value to array?
- Disable 'Add New' in custom post that is made from CPT UI
- WP.org site lost all formatting when I attempted to migrate to Bluehost
MySQL -> Loop through a table, running a stored procedure on each entry
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:
(LENGTH(pageText) - LENGTH (REPLACE (pageText, "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?
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 can2018-10-21 23:42:38
close cursor1; is missing
OPEN - CLOSE are going together for cursors2018-10-22 00:03:49