4

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.

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

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

issue is with this $email_f, it contains html

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

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.

Improve this question
4
  • what error are you getting? Commented Sep 17, 2019 at 22:21
  • i am getting no data in fetch when executing the PDO->execute; but if i run my printed query in phpmyadmin it gives me the output of 3 rows. Commented Sep 17, 2019 at 22:25
  • Okay, I'll post an answer which is given as an example in PHP.net site. Let's see how it works. Commented Sep 17, 2019 at 22:26
  • Do you have other queries in this same application that are returning the data as expected in your php file? Also, when you tried running the query directly in phpMyAdmin, did you copy and paste the output from the $sql variable or did you re-type it manually? If the contents of $email_f is expected to be HTML, is it possible that you have an encoding issue? Commented Sep 18, 2019 at 4:10

1 Answer 1

Reset to default
7

First of all, you have to embrace the fact: if your query found no rows, it means there is no match, even if you can swear the data is all right. So you need to investigate, why there is no match.

Problems caused by SQL errors

Make sure that your query actually runs without errors, as "no result" could mean an error in the query. Refer to these answers for the details: pdo and mysqli.

In case the error says "no such table/database", see the "connection credentials" case below.

Problems caused by the condition

Check your conditions. There are mutual exclusive conditions, such as WHERE col=1 AND col=2. It will never return any rows. Try to simplify the condition until it starts returning some rows, and then refine the conditions to get the desired result. Note that with prepared statements, you cannot bind an arbitrary query part, but only a distinct data literal. Table and column names also cannot be bound.

But all right, there are no errors, all conditions are correct, and you can swear the table contains data to match your query. Still, there are some pitfalls:

Problems caused by the data

First of all, in case a variable involved, make sure it exists and actually contains some value.

Then check the value itself. There could be some converted or non-printable characters in the input data (or database). For example a stray space or a linefeed character; or a peculiarly encoded symbol; or some characters such as < and > converted into HTML entities. As a result, the query contains <[email protected]> will never match a text &lt;[email protected]&gt;. For a quick check you can use rawurlencode() function, it will convert all non-latin characters into codes, thus making them visible.

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.

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

To debug a particular issue, you need

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

Problems caused by the connection credentials

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.

Problems caused by character set/encoding

It's a rare case, but just to to be sure, follow the checklist from this great answer

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i have sorted out the issue myself and the issue was same what you explained above, i was converting some characters such as < and > into HTML entities. i removed that part and it worked. regarding the code i understand the way you have mentioned, it should be like that. i did changed it to my way because i wanted to print the query with values to test it directly in database as well.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.