Re: DB_DataObject producing incorrect SQL

On 11/12/06, Michael Henry <michaelh@mlci.gov.to> wrote:

Hi all, I've just recently started using PHP so it is entirely possible that I have missed something obvious but I am finding that DB_DataObject is producing an incorrect "where" clause when I try to perform an update. I'm using DB_DataObject to update a table (which happens to be my Translations2 table. The Translations2 code is not involved here however.) My code is almost identical to example 33-1 in the DB_DataObject::update() documentation:

    $do = DB_DataObject::factory ('mlci.translations');
    if (PEAR::isError($do)) {
        throw new Exception('Unable to retrieve translations');
    }

    // The lang and string_id fields uniquely identify a row
    $do->lang = $lang;
    $do->string_id = $stringID;
    $count = $do->find(true);

    // ASSERT: $count == 1
    if ($count == 1) {
        $original = clone($do);
        $do->translation = $value;
        $do->update($original);

        if (PEAR::isError($do)) {
            throw new Exception("Failed to update translation<BR>" .
                "Lang: {$lang} String ID: {$stringID} Value: {$value}<BR>" .
                $do->getMessage());
        } else {
            $updates .= "Lang: {$lang} String ID: {$stringID} Value:
    {$value}\n";
        }
    } else {
        throw new Exception('Translation not found in the database');
    }

Obviously $lang, $stringID, and $value were defined before this block; their values are 'to', 209, and 'asdfasdf', respectively. The one difference between my code and the example is that I am using find() instead of get(). This may be related to the problem but from what I can tell the behaviour should be the same. The resulting SQL to perform the update is incorrect:

Yes, this is your problem. find() runs the query and gets ready for you to call fetch(). get() gets a record (it implicitly calls find() and fetch()). You've only done a find but no fetch so you don't have a record in your dataobject. If you want to update multiple record, what you want to do here is clone the DO before you call find(), then use that one to update but use the WHEREADD_ONLY type of update. If what you want to do is update a specific record then you should call fetch() before calling update(). You also don't need to clone the DO in this case.

    UPDATE mlci.translations
    SET translation = 'asdfasdf'
    WHERE ( mlci.translations.id = 104 )
      AND ( mlci.translations.string_id = 209 )
      AND ( mlci.translations.page = 'add_org' )
      AND ( mlci.translations.lang = 'to' )
      AND ( mlci.translations.translation = 'asdfasdf' )

It is using the value of $do->translation in the where clause instead of $original->translation. (To be honest I would expect the package to realise that "id" is the primary key and not bother with any of the other conditions but that is neither here nor there.) If anyone can shed some light on what I'm doing wrong I'd be very grateful.

-- Justin Patrin