我有一个表(MySql),其中一列是-3和3之间的"值"。我要做的是:
- 从表中拉一个随机行,如果它的"值"是一个正数(1到3,在我的情况下-负值将用于其他东西,在这里不相关)
- 如果"值"为3,则只显示该行
- 如果"Value"为2,则显示该行,再加上"Value"为1的随机行。
- 如果"Value"为1,则显示该行,再加上"Value"为2的另一行,或者再加上"Value"为1的另两行。
或者,换一种方式说,我需要列出的行直到它们的总价值为3——通过一行值为3,两行值为1和2(或2和1),或三行各值为1来完成。
我有:
$query = "SELECT * FROM $usertable WHERE Value>0 ORDER BY RAND() LIMIT 1"; //from all positive Values, get one at random
$query2 = "SELECT * FROM $usertable WHERE Value BETWEEN 1 AND 2 ORDER BY RAND() LIMIT 1"; //from all Values between 1 and 2, get one at random
$query3 = "SELECT * FROM $usertable WHERE Value=1 ORDER BY RAND() LIMIT 1"; //from all Values equal to 1, get one at random
$result = mysql_query($query);
$result2 = mysql_query($query2);
$result3 = mysql_query($query3);
while($row = mysql_fetch_array($result))
{
echo $row['A'] . " " . $row['B'] . " " . $row['C'] . " " . $row['Value'];
echo "<br>";
}
while($row['Value']<3)
{
while($row2 = mysql_fetch_array($result2))
{
echo $row2['A'] . " " . $row2['B'] . " " . $row2['C'] . " " . $row2['Value'];
echo "<br>";
}
}
while($row['Value'] + $row2['Value']<3)
{
while($row3 = mysql_fetch_array($result3))
{
echo $row3['A'] . " " . $row3['B'] . " " . $row3['C'] . " " . $row3['Value'];
echo "<br>";
}
}
我试着把while语句按不同的顺序排列,我一直得到意想不到的T_WHILE错误。
我现在使用的方式,代码将运行并超时,并出现以下错误:"致命错误:最大执行时间超过30秒"而且它不做我需要它做的事情(如果第一个"值"是3,它仍然会拉出结果)。
谁能指出我做错了什么?一定有比我现在做的更好的方法来完成这个场景,但我只是没有看到它。谁能给我指个路吗?谢谢!您可以在SQL中执行此操作。窍门是处理2 + 2的情况。你必须忽略第二个
下面通过定义一个@sum
变量来处理这个问题。适当地增加value
,并进行一次扭转。如果值缺少3,则忽略该行:
select t.*,
@sum := if(@prevsum := @sum < 3 and @sum + value > 3, @sum, @sum + value) as thesum
from (select *
from t
where value > 0
order by RAND()
) t cross join
(select @sum := 0, @prevsum := 0) const
having (@sum - value) <= 3 and (@prevsum <> @sum)
这维护了@prevsum
,因此它知道在"2 + 2"或"1 + 1 + 2"的情况下忽略最后一行。
你的while($row['Value'] < 3)
循环永远不会结束,因为你永远不会改变$row
。
我建议这样做:
$first = mysql_fetch_assoc(mysql_query("select * from `".$usertable."` where `Value`>0 order by rand() limit 1"));
$result = array($first);
switch($first['Value']) {
case 1:
$val = rand(1,2);
$rows = 3-$val; // 2 if $val is 1, 1 if $val is 2
$q = mysql_query("select * from `".$usertable."` where `Value`=".$val." order by rand() limit ".$rows);
while($r = mysql_fetch_assoc($q)) $result[] = $r;
break;
case 2:
$result[] = mysql_fetch_assoc(mysql_query("select * from `".$usertable."` where `Value`=1 order by rand() limit 1"));
break;
// case 3 does nothing special
}
// result rows are in $result[] array.
你的mysql_query在错误的地方。
你应该意识到,查询是在mysql_query被调用的地方执行的,因此你在你的while循环一遍又一遍地遍历同一行…
这就是为什么你得到最大的执行时间…
编辑
$query = "SELECT * FROM $usertable WHERE Value>0 ORDER BY RAND() LIMIT 1"; //from all positive Values, get one at random
$query2 = "SELECT * FROM $usertable WHERE Value BETWEEN 1 AND 2 ORDER BY RAND() LIMIT 1"; //from all Values between 1 and 2, get one at random
$query3 = "SELECT * FROM $usertable WHERE Value=1 ORDER BY RAND() LIMIT 1"; //from all Values equal to 1, get one at random
$result = mysql_query($query);
$row = mysql_fetch_array($result);
echo $row['A'] . " " . $row['B'] . " " . $row['C'] . " " . $row['Value'];
echo "<br>";
if ($row['Value'] < 3)
{
switch($row['Value'])
{
case 2: //If the "Value" is 2, display that row, plus another random row with a "Value" of 1. - thats your query3
$result3 = mysql_query($query3);
$row3 = mysql_fetch_array($result3);
echo $row3['A'] . " " . $row3['B'] . " " . $row3['C'] . " " . $row3['Value'];
break;
case 1: //If the "Value" is 1, display that row, plus either one more row with a "Value" of 2, or 2 more rows each with a "Value" of 1. - your query2
$result2 = mysql_query($query2);
$row2 = mysql_fetch_array($result2);
echo $row2['A'] . " " . $row2['B'] . " " . $row2['C'] . " " . $row2['Value'];
if($row2['Value'] == 1)
{ //select another row with value of 1 - your query3
$result3 = mysql_query($query3);
$row3 = mysql_fetch_array($result3);
echo $row3['A'] . " " . $row3['B'] . " " . $row3['C'] . " " . $row3['Value'];
}
break;
}
}
请注意,这段代码可以多次选择一行—您没有提到,行应该是唯一的。如果你需要唯一的行,你需要改变sql代码,不选择所有已经选择的行(即。