title: Looping Over Result Sets in MySQL
body: <p>I am trying to write a stored procedure in MySQL which will perform a somewhat simple select query, and then loop over the results in order to decide whether to perform additional queries, data transformations, or discard the data altogether.  Effectively, I want to implement this:</p>
  
  <pre><code>$result = mysql_query("SELECT something FROM somewhere WHERE some stuff");
  while ($row = mysql_fetch_assoc($result)) {
      // check values of certain fields, decide to perform more queries, or not
      // tack it all into the returning result set
  }
  </code></pre>
  
  <p>Only, I want it only in MySQL, so it can be called as a procedure.  I know that for triggers, there is the <code>FOR EACH ROW ...</code> syntax, but I can't find mention of anything like this for use outside of the <code>CREATE TRIGGER ...</code> syntax.  I have read through some of the looping mechanisms in MySQL, but so far all I can imagine is that I would be implementing something like this:</p>
  
  <pre><code>SET @S = 1;
  LOOP
      SELECT * FROM somewhere WHERE some_conditions LIMIT @S, 1
      -- IF NO RESULTS THEN
      LEAVE
      -- DO SOMETHING
      SET @S = @S + 1;
  END LOOP
  </code></pre>
  
  <p>Although even this is somewhat hazy in my mind. </p>
  
  <p>For reference, though I don't think it's necessarily relevant, the initial query will be joining four tables together to form a model of hierarchal permissions, and then based on how high up the chain a specific permission is, it will retrieve additional information about the children to which that permission should be inherited.</p>