search

Home  >  Q&A  >  body text

Database row matching problem challenges

<p>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 returns me the number of rows, but execute via PDO returns me 0. </p> <p>I have used the fetchAll method to count manually, and even used the rowCount method, but with no success either. </p> <pre class="brush:php;toolbar:false;">$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();</pre> <p>The problem is with $email_f, which contains HTML code. </p> <pre class="brush:php;toolbar:false;">SELECT count(*) FROM inbox WHERE uid = "6961" AND from_email = "abc Offers <abc@abcs.com>"</pre> <p>This is the query statement I printed out from $sql, when I execute it directly in phpmyadmin it works fine and returns me 3 rows, but via execute it returns me 0. </p>
P粉078945182P粉078945182470 days ago504

reply all(1)I'll reply

  • P粉826429907

    P粉8264299072023-08-14 15:05:22

    First of all, you have to accept the fact that if your query doesn't find any rows, that means there is no match, even if you can swear that the data is correct. When a query returns no rows, no rows 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 your query actually has no errors, as "no results" may mean there is an error in the query. For details, refer to these answers: 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:

    Problems caused by data

    First, where a variable is involved, make sure it 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, newline characters or specially encoded symbols, or some characters such as < and > are converted into HTML entities. The result is that a query containing <abc@abcs.com> will never match the text <abc@abcs.com>. For a quick check, you can use the rawurlencode() function, which will convert all non-Latin characters into codes, thus making them visible.

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

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

    To debug a specific problem, you need:

    • Make sure that both PDO and PHP have full error reporting enabled. This is really helpful and can show you the occasional typos, spelling mistakes, etc.
    • Carefully check the data and input in the database to find differences. The urlencode() function will help, it will display all non-printable and converted 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, it doesn't have the requested data. This question is similar to this question, so just follow the same steps to check, except instead of checking a list of tables you have rows of data.

    Issues caused by character set/encoding

    This is a rare case, but just to be sure, follow this good answer's checklist.

    reply
    0
  • Cancelreply