SQL返回一个值，但它不会出现在Array [英] SQL Returns a Value but it doesn't appear in the Array

问题描述

我有以下的MySQL code：

  SELECT COUNT（*）的结果，
（SELECT COUNT（*）FROM tresults WHERE Q61 ='营销'）AS Q1，
（SELECT COUNT（*）FROM tresults WHERE Q61 =帐户）AS Q2，
（SELECT COUNT（*）FROM tresults WHERE Q61 ='资本'）AS Q3，
从tresults
 

的输出，其中（内的MySQL）如下：

 结果Q1，Q2，Q3
1327 12 6 8
 

然后我用下面的PHP code放置在一个数组中这样的信息：

  $结果= $ mysqli-＆GT;查询（$查询）;而（$行= $ result-＆GT; FETCH_ASSOC（））{
    $部门= $行;
}
 

什么是奇怪的，当我的var_dump或print_r的$部门的输出如下所示是：

 阵列（[结果] =＆GT; 1327 [Q1] =＆GT; 12 [Q2] =＆GT; 0 Q3] =→8）
 

正如你所看到的，如果我运行查询只是在MySQL中那么Q2 = 6，当它运行的输出，并通过在PHP中传递到一个数组，而随后Q2 = 0？！？！？！？！

现在，我想知道，如果它有什么做的撇号（帐户**'** S'），你可以看到它不是一个普通/正常撇号。

难道这是导致该问题或者是我的PHP code。该SQL结果放入数组？

任何及所有帮助AP preciated。

解决方案

你有没有尝试过没有，而？我还消除Q1（变化 AS Q1 的AS q1Count 复制到。IMO这是不好的做法使用COUNT别名相匹配的现有列名。

如果不工作，我会开始考虑编码不匹配。两件事情要考虑：

1. 什么是由MySQL使用的编码？


2. 什么是PHP中的MySQLi使用的编码？

$查询= $ mysqli-＆GT;查询（SELECT COUNT（*）AS q1Count结果中的WHERE Q1 ='人民'）
$结果= $查询 - ＆GT; FETCH_ASSOC（）;
$数= $结果['q1Count'];


要确定MySQL表编码： SELECT SCHEMA_NAME，DEFAULT_CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.SCHEMATA 和 SHOW TABLE STATUS

要设置PHP的MySQLi编码： $ mysqli-＆GT; set_charset（编码这里）

I have the following MySQL code:

SELECT COUNT(*) as Results, 
(SELECT COUNT(*) FROM tresults WHERE q61 = 'Marketing') AS q1,
(SELECT COUNT(*) FROM tresults WHERE q61 = 'Account’s') AS q2,
(SELECT COUNT(*) FROM tresults WHERE q61 = 'Capital') AS q3,
FROM tresults

The output of which (within MySQL) is as follows:

Results    q1    Q2    Q3 
1327       12    6     8

I then use the following PHP code to place this information in an array:

$result = $mysqli->query($query);

while($row = $result->fetch_assoc()){
    $department = $row;
}

What is odd is when I var_dump or print_r $department the output is as follows:

Array ( [Results] => 1327 [q1] => 12 [q2] => 0 [q3] => 8 )

As you can see, if I run the query just in MySQL then q2 = 6 whereas when it is run the output and passed through to an array within PHP then q2 = 0?!?!?!?!

Now, I am wondering if it has something to do with the apostrophe ('Account**’**s'), you can see it is not an ordinary/ normal apostrophe.

Could this be causing the issue or is it my PHP code that places the SQL results into the array?

Any and all help appreciated.

解决方案

Have you tried it without the while? I'd also eliminate the duplication of q1 (change AS q1 to AS q1Count. IMO it's bad practice to use a COUNT alias that matches an existing column name.

If that doesn't work, I'd start considering an encoding mismatch. Two things to consider:

1. What is the encoding used by MySQL?
2. What is the encoding used by PHP's MySQLi?

$query = $mysqli->query("SELECT COUNT(*) AS q1Count FROM results WHERE q1 = 'People’s'")
$result = $query->fetch_assoc();
$count = $result['q1Count'];

To determine MySQL table encoding: SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA and SHOW TABLE STATUS

To set PHP MySQLi encoding: $mysqli->set_charset("encoding here")

这篇关于SQL返回一个值，但它不会出现在Array的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！