有效的MySQL查询不返回结果

I have a query in MySQL (thanks to user Ross Smith II) that I have tested in console, (returned 1 days 10 hours 34 minutes a few hours ago) I am relatively sure my PHP is correct to, although it's possible I'm overlooking something, or there is a condition somewhere with this query that I just cannot do what I'm trying to do. Would really appreciate some insight. Please note the timestamp at the top will be a variable selected from another table, but for the purpose of this query I've put a time in the same format. I've taken out all variables and tried raw data, still works in SQL query, but I cannot get the PHP to echo it. I am receiving Fatal error: Call to a member function fetch_assoc() on a non-object. Thanks again.

我在MySQL中有一个查询(感谢用户Ross Smith II),我已在控制台中测试过,(几小时前返回1天10小时34分钟)我相对确定我的PHP是正确的,尽管我可能是忽略某些东西,或者在这个查询的某个地方有一个条件我不能做我正在尝试做的事情。非常感谢一些见解。请注意,顶部的时间戳将是从另一个表中选择的变量,但出于此查询的目的,我将时间放在相同的格式中。我已经取出所有变量并尝试了原始数据,仍然可以在SQL查询中工作,但我无法让PHP回应它。我收到致命错误:在非对象上调用成员函数fetch_assoc()。再次感谢。

$aquery = ("SET @start = \"2013-01-19 07:56:22\";
        SET @end = NOW();

        SELECT
        CONCAT(
            FLOOR(
                (
                    UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                        (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                            IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                            IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
                    )
                ) / 86400
            ),
            ' days ',
            TIME_FORMAT(
            SEC_TO_TIME(
                (
                    UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                        (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                            IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                            IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
                    )
                ) % 86400
            ),
            '%H hours %i minutes'
            )
        ) AS duration");
        $aresult = $mysqli->query($aquery);
        // while( $row = $result->fetch_assoc() ) {
        // Edited typo above, does not fix.
        while ( $row = $aresult->fetch_assoc() ) {
        echo ($row['duration']);
        }

4 个解决方案

#1

You cannot call query with multiple queries like that. Query each of them individually (and remove the ;'s) or use multi_query.

您无法使用多个查询来调用查询。单独查询每个(并删除;'s)或使用multi_query。

$aresult = $mysqli->multi_query($aquery);

$mysqli->query("SET @start = \"2013-01-19 07:56:22\"");
$mysqli->query("SET @end = NOW()");
$result = $mysqli->query("SELECT
    CONCAT(
        FLOOR(
            (
                UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                    (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                        IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                        IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
                )
            ) / 86400
        ),
        ' days ',
        TIME_FORMAT(
        SEC_TO_TIME(
            (
                UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                    (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                        IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                        IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
                )
            ) % 86400
        ),
        '%H hours %i minutes'
        )
    ) AS duration");

#2

$aresult = $mysqli->query($aquery);
while( $row = $result->fetch_assoc() ) {
        echo ($row['duration']);
}

This looks like a typo. Shouldn't you be using $aresult->fetch_assoc()?

这看起来像一个错字。你不应该使用$ aresult-> fetch_assoc()吗?

#3

You have multiple queries, which you can not execute diretly like the way you're trying. I'm using PDO so I'm not positive if this will do what you want. But give it a shot.

您有多个查询,这些查询无法像您尝试的那样直接执行。我正在使用PDO,所以如果这样做你想要的话,我不肯定。但试一试。

$mysqli->autocommit(FALSE);
$mysqli->query("SET @start = '2013-01-19 07:56:22'");
$mysqli->query("SET @end = NOW()");
/*$aresult = */$mysqli->query("SELECT
    CONCAT(
        FLOOR(
            (
                UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                    (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                        IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                        IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
                )
            ) / 86400
        ),
        ' days ',
        TIME_FORMAT(
        SEC_TO_TIME(
            (
                UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                    (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                        IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                        IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
                )
            ) % 86400
        ),
        '%H hours %i minutes'
        )
    ) AS duration");
$mysqli->commit();

#4

After researching the mysqli_multi_query and taking into account inhan and ring0's answers, and thanks to Ross Smith II, here is the solution I have come up with. If anyone needs a PHP script to calculate elapsed time from a timestamp ($timeApproved here) and the current time (could be replaced with any other timestamp, replace @end = NOW() with your timestamp...) here it is. It will return the amount of time in 0 days 0 hours 0 minutes format. Thanks so much for the help.

在研究了mysqli_multi_query并考虑到了inhan和ring0的答案之后,感谢Ross Smith II,这是我提出的解决方案。如果有人需要一个PHP脚本来计算从时间戳(这里是$ timeApproved)和当前时间(可以用任何其他时间戳替换,用你的时间戳替换@end = NOW())的经过时间,这里就是。它将返回0天0小时0分钟格式的时间量。非常感谢帮忙。

HERE IS THE UPDATED CODE

这是更新的代码

$link = mysqli_connect("localhost", "username", "password", "database");
$query = "SET @start = \"".$timeApproved."\";"; 
$query .= "SET @end = NOW();";
$query .= "SELECT
CONCAT(
    FLOOR(
        (
            UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                    IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                    IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
            )
        ) / 86400
    ),
    ' days ',
    TIME_FORMAT(
    SEC_TO_TIME(
        (
            UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start) - 86400 * (
                (FLOOR(DATEDIFF(@end, @start) / 7) * 2) +
                    IF(WEEKDAY(@end) > 4, WEEKDAY(@end) - 4, 0) +
                    IF(WEEKDAY(@end) < WEEKDAY(@start), 2, 0)
            )
        ) % 86400
    ),
    '%H hours %i minutes'
    )
) AS duration;"; 

if (mysqli_multi_query($link, $query)) {
do {

if ($result = mysqli_store_result($link)) {
    while ($row = mysqli_fetch_array($result)) 

{
echo $row['duration'];
}

// mysqli_free_result($result); // I will only have one result.
}   
} while (mysqli_next_result($link));
}

#1