Having Issue With Matching Rows in the Database Using Pdo

Having issue with matching rows in the database using PDO

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. When the query returns no rows, then there are no rows to match the condition. So you have to find out - why. But first of all you need to make sure that your query is all right:

Problems caused by SQL errors

First of all you need to make sure that your query actually runs without errors as "no result" could mean an error in the query.

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.

---

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

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 <abc@abcs.com> will never match a text <abc@abcs.com>.

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.

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 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.

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

An irrelevant but important note

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

Duplicate rows in 2 different databases using PDO

fetchAll() returns an array containing all of the result set rows. You need to iterate over each row and insert it individually. For example:

...
$sql = "SELECT * FROM `calls` WHERE `id`=':id'";
$query = $this->staging->prepare($sql);
$query->execute($array);
$results = $query->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
    try {
        $sql = "INSERT INTO `calls` (`id`,`sip_id`,`extension`,`caller_id`,`stage`,`status`,`survey_id`,`start`,`answer`,`hangup`,`end`) VALUES ('?','?','?','?','?','?','?','?','?','?','?')";
        $query = $this->production->prepare($sql);
        $query->execute($row);
    }
    catch(PDOException $e) {
        $this->informer("FATAL","There was a problem");
    }
}
...

You can also use the statement: while($result = $query->fetch(PDO::FETCH_ASSOC)) instead of fetchAll() to iterate over the results without having to store them in memory.

One thing to consider is what you want to do if an exception is encountered. Since you're inserting many times, you might consider using PDO::beginTransation() at the beginning, PDO::commit() if no exceptions occur, and PDO::rollBack() to cancel any changes if an exception does occur. This way you can be sure that everything transfers or nothing does.

How to check if a row exist in the database using PDO?

You can just check the return value directly.

$stmt = $conn->prepare('SELECT * FROM table WHERE ID=?');
$stmt->bindParam(1, $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if( ! $row)
{
    echo 'nothing found';
}

/*
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // Same here
if( ! $rows)
{
    echo 'nothing found';
}
*/

If you are asking about checking without fetching then simply have MySQL return a 1 (or use the COUNT() command).

$sql = 'SELECT 1 from table WHERE id = ? LIMIT 1';
//$sql = 'SELECT COUNT(*) from table WHERE param = ?'; // for checking >1 records
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $_GET['id'], PDO::PARAM_INT);
$stmt->execute();

if($stmt->fetchColumn()) echo 'found';

Can't return correct row count from database using php pdo

you should replace

$rowCount = $query->rowCount();

with

$rowCount = $query->fetchColumn();

also, take a look at FOUND_ROWS()

there is no sense in calling fetch_num_rows 2 times, it is enough to do:

$numArticles = $rowCount = $article->fetch_num_rows();

Why does this PDO statement silently fail?

TL;DR

1. Always have set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION in your PDO connection code. It will let the database tell you what the actual problem is, be it with query, server, database or whatever.
2. Always replace every PHP variable in the SQL query with a question mark, and execute the query using prepared statement. It will help to avoid syntax errors of all sorts.

Explanation

Sometimes your PDO code produces an error like Call to a member function execute() or similar. Or even without any error but the query doesn't work all the same. It means that your query failed to execute.

Every time a query fails, MySQL has an error message that explains the reason. Unfortunately, by default such errors are not transferred to PHP, and all you have is a silence or a cryptic error message mentioned above. Hence it is very important to configure PHP and PDO to report you MySQL errors. And once you get the error message, it will be a no-brainer to fix the issue.

In order to get the detailed information about the problem, either put the following line in your code right after connect

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

(where $dbh is the name of your PDO instance variable) or - better - add this parameter as a connection option. After that all database errors will be translated into PDO exceptions which, if left alone, would act just as regular PHP errors.

After getting the error message, you have to read and comprehend it. It sounds too obvious, but learners often overlook the meaning of the error message. Yet most of time it explains the problem pretty straightforward:

• Say, if it says that a particular table doesn't exist, you have to check spelling, typos, letter case. Also you have to make sure that your PHP script connects to a correct database
• Or, if it says there is an error in the SQL syntax, then you have to examine your SQL. And the problem spot is right before the query part cited in the error message.

You have to also trust the error message. If it says that number of tokens doesn't match the number of bound variables then it is so. Same goes for absent tables or columns. Given the choice, whether it's your own mistake or the error message is wrong, always stick to the former. Again it sounds condescending, but hundreds of questions on this very site prove this advice extremely useful.

---

Note that in order to see PDO errors, you have to be able to see PHP errors in general. To do so, you have to configure PHP depends on the site environment:

• on a development server it is very handy to have errors right on the screen, for which displaying errors have to be turned on:

    error_reporting(E_ALL);
    ini_set('display_errors',1);
  

• while on a live site, all errors have to be logged, but never shown to the client. For this, configure PHP this way:

    error_reporting(E_ALL);
    ini_set('display_errors', 0);
    ini_set('log_errors', 1);
  

Note that error_reporting should be set to E_ALL all the time.

Also note that despite the common delusion, no try-catch have to be used for the error reporting. PHP will report you PDO errors already, and in a way better form. An uncaught exception is very good for development, yet if you want to show a customized error page, still don't use try catch for this, but just set a custom error handler. In a nutshell, you don't have to treat PDO errors as something special but regard them as any other error in your code.

P.S.

Sometimes there is no error but no results either. Then it means, there is no data to match your criteria. So you have to admit this fact, even if you can swear the data and the criteria are all right. They are not. You have to check them again. I've short answer that would help you to pinpoint the matching issue, Having issue with matching rows in the database using PDO. Just follow this instruction, and the linked tutorial step by step and either have your problem solved or have an answerable question for Stack Overflow.

I dont know how to search for a matching row with pdo

I have several suggestions to you.

1. First of all, add a unique index to your users table, like this:
   
   ALTER TABLE users ADD UNIQUE(username)
   
   After this you won't need to worry about name duplication: mySQL will throw an error (but please be careful and handle errors properly).
2. Use the WHERE clause as suggested in a comment. So you won't need to loop through a large number of rows to find one single row.
3. Use parameterized queries and prepared statements. As you're using PDO (which is, I believe, one of the best things you could do with PHP and MySQL), this shouldn't be a hard thing to do. So you'll be secure from (most of the) SQL injections.
4. Use PDO->fetch() and PDO->fetchAll(). Queries in loops are a really bad thing to do because this slows down your whole website.

---

Related Topics