即使查询有效，mysqli_fetch_array 也会出错；将总出口作为 NULL 值运行 [英] mysqli_fetch_array error even when query is valid; running total exports as NULL values

问题描述

我有以下查询数据库时有效的查询:

I have the following query which is valid when querying the database:

编辑 3: 根据 Joe Swindell 的建议，可能是查询中的 SET @runningTotal := 0 行.要解决的问题是找到一种方法将下面的复杂查询转换为 JSON 字符串，这也将保存 NumPosts 部分.NumPosts 是一个运行总数，它依赖于 SET 行；删除 set 行会删除 mysqli_fetch_array 错误，但会导致所有 NumPost 值都为 NULL.

EDIT 3: Per Joe Swindell's advice, it could be that the SET @runningTotal := 0 line in the query. The issue to address is finding a way to convert the complex query below into a JSON string which will also save the the NumPosts part. NumPosts is a running total which is dependent on the SET line; removing the set line removes the mysqli_fetch_array error, but results in NULL for all NumPost values.

EDIT 2:我已经回显了下面的查询并将其粘贴回 MySQL 以确认它正在工作，并且它实际上是有效的.

EDIT 2: I have echoed the query below and pasted back into MySQL to confirm that it is working, and it is in fact valid.

SET @runningTotal := 0;
SELECT T.ForDate,  (@runningTotal := @runningTotal + T.DayProgress) as NumPosts
FROM
(

    SELECT DATE(  completed_courses.complete_date ) AS ForDate, COUNT( * ) AS DayProgress
    FROM users
    INNER JOIN completed_courses ON users.user_id = completed_courses.user_id
    INNER JOIN wg_courses ON wg_courses.c_id = completed_courses.c_id
    INNER JOIN workgroup ON wg_courses.wg_id = workgroup.g_id
    WHERE users.course_group LIKE  '4h%' AND completed_courses.complete_date > 0

    GROUP BY DATE(completed_courses.complete_date)
) T
ORDER BY T.ForDate

以上输出:

ForDate     NumPosts
2014-07-29  950
2014-07-30  3063
2014-07-31  3669
2014-08-01  4584
2014-08-02  5088
...

检查连接并确保一切匹配，这是输出:警告:mysqli_fetch_array() 期望参数 1 是 mysqli_result，给定的对象"

Checked the connection and made sure everything matches, and this is the output: "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given"

PHP 代码:

$data = array();
$i = 0;
while($row = mysqli_fetch_array($con, $result)) {

    $i += 1;
    array_push($data, array($i) + $row);
}

稍后，代码被编码为 JSON 以在 Google Charts 中使用:

Later, the code is encoded into JSON for use in Google Charts:

var data = <?php echo json_encode($data) ?>

此外，将查询结果导出为 SQL、CSV、Excel 文件等会导致所有 NumPosts 值为 NULL:

In addition, exporting the query results as SQL, CSV, Excel files, etc. results in all NumPosts values as NULL:

ForDate     NumPosts
2014-07-29  NULL
2014-07-30  NULL
2014-07-31  NULL
2014-08-01  NULL
2014-08-02  NULL
...

值没有正确保存或获取的原因可能是什么?

What could be the reason the values are not saving or fetching properly?

推荐答案

http://php.net/manual/en/mysqli-result.fetch-array.php

您错误地使用了mysqli_fetch_array().

如果这是您尝试进行查询的地方，则需要进行大量重组.如果您已经进行了查询并将其存储在 $result 中:

If that is where you are attempting to make your query, you need to do a lot of restructuring. If you've already made your query and stored it in say $result:

你会使用

mysqli_fetch_array($result, MYSQLI_ASSOC);

建立连接和查询之后

您的整个代码可能如下所示:

Your entire code might look something like this:

$query = "SELECT Whatever whatever from something;";
$result = mysqli_query($con, $query);       

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

    $i += 1;
    array_push($data, array($i) + $row);
}

就目前而言，您有多个查询.运行 SET @runningTotal := 0; 的单个查询然后将您的查询变量重置为查询的其余部分并运行它，然后您将获得结果.

As it stands you have multi query. Run a single query of SET @runningTotal := 0; THEN reset your query variable to the rest of the query and run that, you will then get results.

这篇关于即使查询有效，mysqli_fetch_array 也会出错；将总出口作为 NULL 值运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！