CODEWITHSUNDEEP
phpmysqlpdo
Andreas BB
Andreas BB

Reputation: 51

Check PDO query for result and then reuse the data

I am new to PDO, and am in the process of upgrading an application from mysql_query to PDO. This is surely a stupid question - but I hope someone can help me wrap my head around it.

I need to see if a PDO query has any data: - if it doesn't, throw an error - if it does, retrieve that data

I could do this easily with mysql_num_rows, but that's deprecated as we all know.

The issue is that once I've checked if there is any data, I can no longer retrieve it.

The check runs fine, but then when trying to retrieve the actual result, it's empty. I can of course execute the query again after the check - but I'd rather avoid having to run a query twice.

try
{
    $result = $pdo2->prepare("SELECT first_name FROM users WHERE email = :email;");
    $result->bindValue(':email', $email);
    $result->execute();
    $data = $result->fetchAll();
}
catch (PDOException $e)
{
        $error = 'Error fetching user: ' . $e->getMessage();
        echo $error;
        exit();
}

if (!$data) {
    echo "No data!";
} else {
    echo "Data found!";
}           

$row = $result->fetch();
    echo "First name: " . $row['first_name'];

How can I solve this? I tried to assign $result to another variable ($test = $result), and then run the data check on the $test variable instead - but even so, the $result variable STILL doesn't return any data after running the check (see the commented lines):

try
{
    $result = $pdo2->prepare("SELECT first_name FROM users WHERE email = :email;");
    $result->bindValue(':email', $email);
    $result->execute();
    $test = $result; // Duplicating the variable
    $data = $test->fetchAll(); // Running the check on the duplicated variable
}
catch (PDOException $e)
{
        $error = 'Error fetching user: ' . $e->getMessage();
        echo $error;
        exit();
}

if (!$data) {
    echo "No data!";
} else {
    echo "Data found!";
}           

$row = $result->fetch(); // Still doesn't return the result!
    echo "First name: " . $row['first_name'];

This is really doing my head in... I think there's a simple solution somewhere, I just can't see it. Please help!

Upvotes: 1

Views: 1296

Answers (3)

Nigel Ren
Nigel Ren

Reputation: 57121

You can always fetch individual rows and for the first row, check if the data is returned and process if not. Then enter a do...while() loop which processes the data and then reads the next row at the end of the loop...

try
{
    $result = $pdo2->prepare("SELECT first_name FROM users WHERE email = :email;");
    $result->bindValue(':email', $email);
    $result->execute();
    $row = $result->fetch();  // Fetch first row of data

    if (!$row) {
        echo "No data!";
    } else {
        echo "Data found!";

        do { 
            echo "First name: " . $row['first_name'];
        }
        while ($row = $result->fetch());
    }           
}
catch (PDOException $e)
{
        $error = 'Error fetching user: ' . $e->getMessage();
        echo $error;
        exit();
}

Upvotes: 0

Barmar
Barmar

Reputation: 781068

$result->fetch() only fetches rows that haven't already been fetched. Since you fetched everything with $result->fetchAll(), there's nothing left.

If you want the first row, you can use:

$row = data[0];

If you want to process all the rows, use:

foreach ($data as $row)

Instead of fetching everything, you can use the rowCount() method.

if (!$result->rowCount()) {
    echo "No data";
} else {
    echo "Data found!";
}

There are caveats regarding the use of rowCount() with SELECT queries in PDO, but I think it generally works with MySQL.

Upvotes: 2

Professor Abronsius
Professor Abronsius

Reputation: 33813

As you are using a try/catch block you can raise your own exceptions as well as catch those thrown by PDO - so you could do something like this:

try{

    $sql='SELECT first_name FROM users WHERE email = :email;';
    $stmt = $pdo2->prepare( $sql );

    if( !$stmt )throw new Exception('Failed to prepare sql statement');
    $result=$stmt->execute( array( ':email' => $email ) );

    if( !$result )throw new Exception('Failed to get any results');
    $rows = $stmt->rowCount();

    if( $rows == 0 )throw new Exception('Empty recordset');

    while( $rs=$stmt->fetch( PDO::FETCH_OBJ ) ){
        echo $rs->firstname;
    }

}catch ( PDOException $e ){
    exit( 'Error fetching user: ' . $e->getMessage() );
}

Upvotes: 0

Related Questions