Encountered a problem matching rows in the database.

Question

I want to get the row count to check if the same email already exists in the database. I tried several methods without success. When I run the query directly in the database it returns me the number of rows, but via PDO's execute method it returns 0.

I tried counting manually using the fetchAll method and even used the rowCount method but neither worked.

$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();

The problem lies in the $email_f variable, which contains HTML code.

SELECT count(*) FROM inbox WHERE uid = "6961"
AND from_email = "abc Offers <abc@abcs.com>"

This is the query statement I printed out from $sql, when I execute it directly in phpMyAdmin it works fine and returns 3 row count, but via the execute method it returns 0.

Answer 1

First of all, you have to accept the fact that if your query does not find any rows, it means there is no match, even though you can swear that the data is correct. When a query returns no rows, no rows match the criteria. So you need to find out why. But first, you need to make sure your query is correct:

Problems Caused by SQL Errors

First, you need to make sure there are actually no errors in the query, as "no results" may mean There is an error in the query. Please refer to the following answers for details: pdo and mysqli.

Problems caused by conditions

Check your conditions. There are some mutually exclusive conditions, such as WHERE col=1 AND col=2. It never returns any rows. Try simplifying the condition until it starts returning some rows, then refine the condition to get the results you want.

---

But okay, there are no errors, the conditions are correct, and you could swear there is data in the table that matches your query. However, there are still some pitfalls:

Issues caused by data

First, where variables are involved, make sure the variable exists and actually contains some value.

Then check the value itself. There may be some converted or unprintable characters in the input data (or database). For example, line breaks or specially encoded symbols, or certain characters such as < and > are converted to HTML entities. As a result, querying for text containing <abc@abcs.com> will never match the text <abc@abcs.com>. You can do a quick check using the rawurlencode() function, which will convert all non-Latin characters into codes, making them visible.

The problem is that this is just a guess, no one can tell you what the actual problem is, because it is your database, your input data, and only you can find the problem.

< <

I wrote an article explaining how to debug your PDO problems.

To debug a specific issue, you need:

• Make sure full error reporting is enabled for PDO and PHP. This is really helpful and can show you the occasional typographical error, spelling mistake, etc.
• Carefully check the data in the database and the input data to find differences. The urlencode() function will help, it can display all non-printing and converted characters in the database and input data.

Problems caused by connection credentials

Another common problem is when you have multiple databases and connect to the wrong database, which does not have the requested data. This question is similar to the previous one, so just follow the same steps, except instead of checking the list of tables, you check the data rows.

Issues caused by character set/encoding

This is a rare case, but just to make sure, follow this good answer