mysql - 查询在 phpmyadmin 中完美运行，但在使用 php 获取时仅返回 true/1

Question

我的这个查询完全符合我在 phpmyadmin 中的要求，它返回我网站的访问次数。例如:visitorCount:2204

set @last_uid = 0;
set @last_tm = '00:00';
set @last_vid = 0;

SELECT MAX(
     CASE WHEN @last_uid = visitorid AND cast( @last_tm as datetime) >= subtime( timestamp, '01:00' )
     THEN 
          if( (@last_tm := timestamp ), @last_vid, @last_vid)
     ELSE 
          if( (@last_uid := visitorid) + (@last_vid := @last_vid+1) +(@last_tm := timestamp ),@last_vid, @last_vid )
     END 
) as visitorCount

FROM visits v
ORDER BY timestamp DESC, visitorid

当我尝试从 PHP 执行此查询但它不起作用时，我已经使用常规 php mysql 连接和 cakephp $this->Model->query(); 进行了测试；没有任何成功，我从查询中得到的只是“1”或 true。怎么了？

$query = "set @last_uid = 0;
                    set @last_tm = '00:00';
                    set @last_vid = 0;

                    SELECT MAX(
                         CASE WHEN @last_uid = visitorid AND cast( @last_tm as datetime) >= subtime( timestamp, '01:00' )
                         THEN 
                              if( (@last_tm := timestamp ), @last_vid, @last_vid)
                         ELSE 
                              if( (@last_uid := visitorid) + (@last_vid := @last_vid+1) +(@last_tm := timestamp ),@last_vid, @last_vid )
                         END 
                    )

                    FROM visits v
                    ORDER BY timestamp DESC, visitorid";

        $con = mysql_connect("host", "user", "pass");
        if (!$con) {
            die(mysql_error());
        }
        mysql_select_db("dbname", $con);
        $result = mysql_query($query);
        print_r(mysql_fetch_row($result));

给我错误信息:

mysql_fetch_row() expects parameter 1 to be resource, boolean given

Answer 1

我不确定是不是因为你正在执行多条语句，但尝试将它们组合成一个，

SELECT MAX(CASE WHEN @last_uid = visitorid AND cast( @last_tm as datetime) >= subtime( timestamp, '01:00' )
                THEN if((@last_tm := timestamp), @last_vid, @last_vid)
                ELSE if((@last_uid := visitorid) + (@last_vid := @last_vid+1) + (@last_tm := timestamp), @last_vid, @last_vid )
            END)
FROM    visits v,
        (SELECT @last_uid := 0, @last_tm := '00:00', @last_vid := 0;) x
ORDER   BY timestamp DESC, visitorid