A loop until in the stored procedure

I am trying to write a procedure that will fire the same select query till the number of results are more than 0. If the "interval 2 hour " returns 0 records then the "interval 4 hour" criterion should be used and if there are still no records fetched, then lastupdate > current_date() should be used in the where clause.

These are the 2 basic queries used in the procedure.

select sql_calc_found_rows id from sometable where lastupdate > date_sub(now(), interval 2 hour) limit 10;

select found_rows();
+--------------+
| found_rows() |
+--------------+
|           41 | 
+--------------+

Is the following procedure correct? Is it the right way to write a SP? And how do I use the results in the PHP code?

delimiter $$
create procedure mytest3()
begin 

  declare myvar int;

 select sql_calc_found_rows id 
   from sometable 
  where lastupdate > date_sub(now(), interval 2 hour) 
  limit 10;

 select found_rows() into myvar;

if (myvar > 0) 
  then select 'Found in 2 hours';
else
  select sql_calc_found_rows id 
    from sometable 
   where lastupdate > date_sub(now(), interval 4 hour) 
   limit 10;

select found_rows() into myvar;

if (myvar > 0) 
  then select 'Found in 4 hours';
e开发者_如何学Pythonlse 
  select sql_calc_found_rows id 
    from sometable 
   where lastupdate > current_date() 
   limit 10;
end if;
end if; 
end$$ 

It occurs to me that while you've asked for a loop in both the title and body of your text, what you're really trying to do is get the list of "rows modified in the last X hours", with the smallest X which returns some (non-empty) set of rows...

Here's one way of achieving that:

delimiter $$
create procedure recently_modified_rows()
begin 

  declare tablelastupdate int; -- how many hours ago table was last updated.
  declare showperiod datetime; -- what time interval to show
  declare showtext  text;      -- text describing time interval

  select hour(timediff(now(), max(lastupdate))) into tablelastupdate
    from sometable;

  if(tablelastupdate < 2)
     set showperiod = time_sub(now(), interval 2 hours);
     set showtext = "modified in the last 2 hours";
  elseif (tablelastupdate < 4)
     set showperiod = time_sub(now(), interval 4 hours);
     set showtext = "modified in the last 4 hours";
  else
     set showperiod = current_date();
     set showtext = "modified today";
  end if

  select sql_calc_found_rows id, 
         showtext description
     from sometable 
     where lastupdate > showperiod 
     limit 10;

end$$

and to call it from php:

$query = mysql_query("call recently_modified_rows()") or die( mysql_error() );
$numrows = mysql_numrows($query);

if ($numrows != 0)
{
    /* print out what time interval we used */
    $description = mysql_result($query, 0, 'description');
    echo "Found $numrows rows $description";

    /* print out the rows */
    while ($row = mysql_fetch_array($query)) 
    {
       echo "Id: {$row['id']}";
    }

}
else 
{
    /* happens only if there were no records modified in any of the three versions */
    echo "no records were modified in the last 2 hours, 4 hours, or today";
}

Depending on your situation, it may be better to set up a trigger (http://dev.mysql.com/doc/refman/5.0/en/triggers.html) on your database. This lets you do any necessary work/recalculations only when relevant data changes, instead of making your server unnecessarily busy by polling continually even when nothing in the database has changed.

This is my first attempt at an answer posting on StackOverflow. Hope it's helpful..

I've written a test procedure below using my local MySQL database. It is set to run for 10 full seconds and return an empty resultset if there are no records. If a row is inserted within the ten seconds it's running, it will exit the loop and return the new results.

The Sleep function is there to prevent the procedure from eating too much CPU while it's running by only running the Select Count(*) once per second. You can set this to any interval you want.

While this procedure works well, I do have to agree with natevw on using Triggers instead.

 DELIMITER $$

 CREATE PROCEDURE sp_TestQueryResultsTimeout()
 BEGIN


   DECLARE v_interval, ct, v_time INT;

   /* This will keep track of how much time has passed*/
   SET v_interval = 0;

   /* This is used for comparing the rowcount*/
   SET ct = 0;

   /* This is used to keep the procedure from returning the Sleep functions results'
      This could also be used to keep a more accurate count of the amount of Sleep time has passed by adding the results of the sleep function to it on every iteration*/
   SET v_time = 0;

   /* This while statement should run for slightly longer than ten seconds
      The amount of extra time will begin to add up depending on how long the select count (*) takes
      and how many iterations you want to make'*/

   WHILE v_interval < 10 DO

   /*Get the count from your table*/
       SET ct = (SELECT count(*) FROM tbUsers);

     /*If the count is greater than 0, exit the while by satisfying the condition*/
   if (ct > 0) then
    SET v_interval = 10;
   else
    /*If the count is less than 0, sleep for 1 second*/
         SET v_time = (SELECT SLEEP(1));
   end if;

       /*Increment*/
       SET v_interval = v_interval + 1;
   END WHILE;

   SELECT * FROM tbUsers;

 END$$

 DELIMITER ;

From MySQL docs:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END

PHP pages will be triggered by users, so that you have to save the result into the database until someone opens that page, which displays the result.

Instead of using an infinite loop within your database, you could use a PHP cronjob.