PHP和MySQL中相同查询的不同SQL结果 [英] Different SQL result with same query in PHP and MySQL

问题描述

快速搜索后，我还没有找到我的问题的解决方案，所以我发布了一个新的.

After a quick search, I haven't find solution of my problem so I post a new one.

所以我必须从 Web 界面(使用 PHP)在 MySQL 数据库中创建视图.

So I have to create view in a MySQL database from a Web interface (using PHP).

我使用 PEAR 框架连接 MySQL(5.0.26)

I use a PEAR framework for the connection with MySQL(5.0.26)

我有 SQL 请求:

CREATE VIEW test AS SELECT cswc.code_de_la_copie, cswc.service_de_reference, cswc.libelle_de_la_copie, cswc.direction_de_securite_logique
FROM  pressi_copiesServiceWithCibles cswc LEFT OUTER JOIN pressi_servicesReferenceWithCibles srwc ON cswc.service_de_reference = srwc.code_du_service
WHERE cswc.cible is null
  AND (srwc.cible LIKE '%£DOMAIN£%' OR srwc.cible LIKE '%$DOMAIN$%');

当我直接在mon本地MySQL数据库上执行这个请求时，得到了470行的结果.

When I execute this request directly on mon local MySQL Database, I obtain a result with 470 lines.

然而，当我在我的PHP代码中执行这个请求时，我得到了不同的结果(我有386行)，我不知道为什么！

However, when I execute this request in my PHP code, I have a different result (I have 386 line), and I don't know why !

$values['request'] = "SELECT cswc.code_de_la_copie, cswc.service_de_reference, cswc.libelle_de_la_copie, cswc.direction_de_securite_logique
FROM  pressi_copiesServiceWithCibles cswc LEFT OUTER JOIN pressi_servicesReferenceWithCibles srwc ON cswc.service_de_reference = srwc.code_du_service
WHERE cswc.cible is null
  AND (srwc.cible LIKE '%£DOMAIN£%' OR srwc.cible LIKE '%$DOMAIN$%');";

$baseView = "test";

$sqlView = 'CREATE VIEW '.$baseView.' AS '.$values['request'];
$res =& $this->mdb2->query($sqlView);
if (PEAR::isError($res)) {
   return false;
}

此外，在此之前我已经创建了 6 个视图，没有任何问题(在 PHP 和 MySQL 中的结果相同)

Moreover, I have already create 6 views before this one without any problem (same result in PHP and in MySQL)

感谢您的帮助

推荐答案

请注意，您与数据库的连接还有一个 CHARSET 和一个 COLLATION 用于您的任何字符串值包括在您的查询中.尽管您的查询在这两种情况下看起来都一样，但从 MySQL 服务器的角度来看，它一定不是.

Note that your connection to the database also has a CHARSET and a COLLATION for any string values you include in your query. Although your query looks the same to you in both situations, it must not from the MySQL servers point of view.

也许客户端 CHARSET(和/或 COLLATION)在通过 PHP 连接时与通过 MySQL 控制台连接时不同.

Maybe the client CHARSET (and/or COLLATION) differ when you connect via PHP from when you connect via MySQL console.

请参阅 MySQL 手册以了解有关客户端字符集和整理.

为了比较，你可以使用这个查询:

For comparison, you can use this query:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

这篇关于PHP和MySQL中相同查询的不同SQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！