Problem with matching rows in database-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Problem with matching rows in database

P粉852114752 2023-08-25 20:51:42

522

I want to get the row count to check if the same email already exists in the database. I tried several mechanisms without success. When I run the query directly in the database it gives the number of rows but executing it through PDO gives 0.

I have used the fetchAll method to count manually and even using the rowCount method does not work

$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 is with this $email_f, which contains html

SELECT count(*) FROM inbox WHERE uid = "6961" AND from_email = "abc Offers "

This is the query I printed from $sql and when I execute it directly in the database in phpmyadmin it works fine. I'm given the number 3, but by executing I get 0.

P粉852114752

reply all (1)

P粉8795174032023-08-26 12:59:05 1 floor

First, you have to accept the fact that if your query doesn't find any rows, that meansthere is no match,even if you can swear that the data is correcteh>. When a query returns no rows, it means that there are no rows that match the criteria. So you have 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 that your query actually runs without errors, as "no results" may mean that there is an error in the query. See these answers for details:pdoandmysqli.

Problems caused by the situation

Check your conditions. There are mutually exclusive conditions, such asWHERE 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 the data in the table matches your query. Still, there are some pitfalls:

Problems caused by data

First, if a variable is involved, make sure itexistsand actually contains some value.

Then check the value itself. There may be some converted or unprintable characters in the input data (or database). Such as line breaks or specially encoded symbols, or certain characters that are converted to HTML entities, such asand>. Therefore, a query containing will never match the text . For a quick check, you can use therawurlencode()function, which converts all non-Latin characters into codes, thus making them visible.

The problem is, this is just a guess and no one can tell you what the actual problem is because it isyourdatabase,yourinput data and only you can find the problem.

I wrote an article explaininghow to debug PDO issues.

To debug a specific issue you need

Make sure that both PDO and PHP have full error reporting enabled. It's really helpful and can show you the occasional typographical errors, spelling errors, etc.
Double check the data and input in the database for discrepancies. Theurlencode()function can be helpful, displaying all non-printable and convertible characters in the database and input.

Problems caused by connection credentials

Another common problem is when you have multiple databases and connect to the wrong database that does not contain the requested data. This question is similar tothis question, so just follow the same routine and only check the data rows instead of the table list.