gpt4 book ai didi

php - 一个循环直到在存储过程中

转载 作者:行者123 更新时间:2023-11-30 23:57:37 25 4
gpt4 key购买 nike

我正在尝试编写一个程序,它将触发相同的选择查询,直到结果数大于 0。如果“间隔 2 小时”返回 0 条记录,则应使用“间隔 4 小时”标准,如果仍然没有获取记录,那么lastupdate > current_date() 应该用在 where 子句中。

这些是该过程中使用的 2 个基本查询。

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

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

以下程序是否正确?写SP是正确的方法吗?以及如何在 PHP 代码中使用结果?

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';
else
select sql_calc_found_rows id
from sometable
where lastupdate > current_date()
limit 10;
end if;
end if;
end$$

最佳答案

我突然想到,虽然您要求在文本的标题和正文中使用循环,但您真正想要做的是获取“最近 X 小时内修改的行”列表,其中返回一些(非空)行集的最小 X ...

这是实现该目标的一种方法:

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$$

并从 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";
}

关于php - 一个循环直到在存储过程中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1641967/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com