PDO Prepare with bindParam doesn't work

Question

PHP 5.4.7 in xampp installation, MySQL 5.5.27 - I have struggled all day to get various UPDATE statements to work, to no avail. Have read through and tried examples from http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers, and have given up on one UPDATE (found an alternative solution) but am going nuts wondering what is wrong with THIS one, below. Btw all my other PDO FETCHALL queries work fine ...

function updateThisMemberInterests($PDOdbObject, $memberId, $interests)
{
try
{
    $intId = 0;
    $upInt = $connectionObject->prepare("UPDATE `member_interest` SET (`interest_id`) VALUES (:intId)");
    $upInt->bindParam(':intId', $intId, PDO::PARAM_INT);
    foreach($interests as $intId)
    {
        $upInt->execute();
    }
    $affected_rows = $upInt->rowCount();
    return $affected_rows;
}
catch (PDOException $e)
{
    echo "There was a problem connecting to this database.";
    $e->getMessage();
}
}

My database setup function specifies: PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

but I'm not getting anything except my echoed message in the Catch.

Must have interpreted the tutorial's instructions wrongly - can someone tell me what it is?

I'm pretty sure that rowCount() only applies to the most recent execute(), so calling it after your loop won't give you correct information. — Michael Berkowski
Anyway, your issue is that your function accepts $PDOdbObject, but you call $connectionObject in the function body. Turn on error_reporting and display_errors, and you'll see call to a member function prepare() on a non object. — Michael Berkowski
about rowCount() - noted, thanks. about $PDOdbObject, that was its name in my data layer file. In my many attempts to figure out what what happening I moved the whole function into the calling file - so my mistake, I posted this function with the caling var name. In reality they are both $PDOdbObject in the data layer file. So it's not that ... — swissphp
You need to echo the exception message too, right now you're just throwing away its output. That should tell you something useful... echo $e->getMessge(); — Michael Berkowski
Michael - thanks for the error_reporting info! D-uh! Have a new IDE and it wasn't set up properly yet. This makes finding the problem much easier :D. — swissphp

swissphp swissphp · Accepted Answer · 2013-05-18T11:05:13

ok - thanks to the error-handling being switched on, i found several problems, specifically that i was mixing INSERT and UPDATE syntax. in the end i solved the problem with a transaction that deletes existing interests and inserts the new ones. this works well.

here is my code:

function updateThisMemberInterests($PDOdbObject, $memberId, $interests)
{
if ($interests != 0)
{
    try
    {
        //begin transaction
        $PDOdbObject->beginTransaction();

        $delInts = "DELETE FROM `member_interest` WHERE `member_id` = $memberId";
        $PDOdbObject->exec($delInts);

        $intId = 0;
        $upInt = $PDOdbObject->prepare(  "INSERT INTO member_interest (`member_id`,`interest_id`) VALUES ($memberId, :interest_id)" );
        $upInt->bindParam(':interest_id', $intId, PDO::PARAM_INT);
        foreach($interests as $intId)
        {
            $upInt->execute();
        }
        //commit
        $PDOdbObject->commit();
        $affected_rows = $upInt->rowCount();
        return $affected_rows;
    }
    catch (PDOException $e)
    {
        echo "There was a problem - rolling back this transaction.";
        //rollback transaction
        $PDOdbObject->rollBack();
        echo $e->getMessage();
    }
}//more than zero interests changed
}

PDO Prepare with bindParam doesn't work

1 Answers