使用 PDO 匹配数据库中的行时出现问题 [英] Having issue with matching rows in the database using PDO

问题描述

我希望获得行数以检查相同的电子邮件是否已经在数据库中.我尝试了几种机制，但没有成功.当我直接在数据库中运行查询时，它给了我行数，但通过 PDO 执行它给了我 0.

I am looking to get row count to check if same email is already in database or not. i have tried couple of mechanism but no success. when i run my query directly in the database it gives me the row count but via PDO execute it gives me 0.

我已经使用 fetchAll 方法手动计数，甚至使用了也不起作用的 rowCount 方法

i have used fetchAll method to manually count, even used rowCount method that also not working

  $sql = 'SELECT count(*) FROM inbox WHERE uid = "'.$email_number.'" AND from_email = "'.$email_f.'"'; 
  $result = $link->prepare($sql); 
  $result->execute(); 
  $number_of_rows = $result->fetchColumn(); 

问题在于 $email_f，它包含 html

issue is with this $email_f, it contains html

SELECT count(*) FROM inbox WHERE uid = "6961" 
AND from_email = "abc Offers <[e-mail]>"

这是我从 $sql 打印的查询，当我直接在 phpmyadmin 中在数据库中执行它时，它工作正常.给我 3，但通过执行我得到 0.

this is the query which i have printed from $sql and when i execute it in database directly in phpmyadmin, it works fine. give me count of 3 but via execute i get 0.

推荐答案

数据导致的问题

输入数据(或数据库)中很可能存在一些已转换或不可打印的字符.例如，可能有换行符或特殊编码的符号，或者某些字符(例如 < 和 > 转换为 HTML 实体).因此，包含 <[e-mail]> 的查询永远不会匹配文本 <[e-mail]>.

Problems caused by the data

Most likely there are some converted or non-printable characters in the input data (or database). For example, there could be a linefeed character or a peculiarly encoded symbol, or some characters such as < and > converted into HTML entities. As a result, the query contains <[e-mail]> will never match a text <[e-mail]>.

问题是，这只是一个猜测，没有人能告诉你真正的问题是什么，因为它是你的数据库，你的输入数据，并且只有您可以找到问题.

The problem is, this is only a guess, and nobody can tell you what the actual issue is, because it is your database, your input data and only you can find the issue.

我写了一篇文章，解释了如何调试您的 PDO 问题.

I wrote an article that explains how to debug your PDO issues.

要调试特定问题，您需要

To debug a particular issue, you need

• 确保为 PDO 和 PHP 启用完整的错误报告.它真的很有帮助，偶尔会向您展示排版错误、拼写错误等
• 仔细检查数据库中的数据和输入以找出差异.bin2hex() 函数会有所帮助，显示数据库和输入中所有不可打印和转换的字符.

• make sure the full error reporting is enabled for both PDO and PHP. It really helps, showing you occasional typographic errors, spelling errors and the such
• scrutinize both the data in the database and the input to find the difference. bin2hex() function would help, revealing all non-printable and converted characters, in both database and the input.

另一个常见问题是当您有多个数据库并连接到没有请求数据的错误数据库时.这个问题类似于这个，所以就按照同样的套路，只检查数据行，而不是表列表.

Another frequent issue is when you have several databases and connect to the wrong one that doesn't have the data requested. This issue is similar to this one, so just follow the same routine, only checking not the list of tables but the data rows.

附带说明，但仍然非常重要:您准备好的声明是货物崇拜代码什么都不保护.这是必须的方式:

On a side note, but very important nevertheless: your prepared statement is a cargo cult code that protects nothing. Here is how it must be:

$sql = 'SELECT count(*) FROM inbox WHERE uid = ? AND from_email = ?'; 
$result = $link->prepare($sql); 
$result->execute([$email_number,$email_f]); 
$number_of_rows = $result->fetchColumn(); 

这篇关于使用 PDO 匹配数据库中的行时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！