How do I know if an ORM save() method generated an update or an insert query?

Question

The problem:

PHP Code (no framework)

if ($this->uid)
        {
            $query = sprintf('UPDATE %sUSER SET USERNAME = "%s", ' .
                'PASSWORD = "%s", EMAIL_ADDR = "%s", IS_ACTIVE = %d ' .
                'WHERE USER_ID = %d',
                DB_TBL_PREFIX,
                mysql_real_escape_string($this->username, $GLOBALS['DB']),
                mysql_real_escape_string($this->password, $GLOBALS['DB']),
                mysql_real_escape_string($this->emailAddr, $GLOBALS['DB']),
                $this->isActive,
                $this->userId);
            mysql_query($query, $GLOBALS['DB']);
        }
        else
        {
            $query = sprintf('INSERT INTO %sUSER (USERNAME, PASSWORD, ' .
                'EMAIL_ADDR, IS_ACTIVE) VALUES ("%s", "%s", "%s", %d)',
                DB_TBL_PREFIX,
                mysql_real_escape_string($this->username, $GLOBALS['DB']),
                mysql_real_escape_string($this->password, $GLOBALS['DB']),
                mysql_real_escape_string($this->emailAddr, $GLOBALS['DB']),
                $this->isActive);
            mysql_query($query, $GLOBALS['DB']);

            $this->uid = mysql_insert_id($GLOBALS['DB']);
        }

Kohana Code (using ORM)

public function save()
    {
        $query = ORM::factory('user', $this->user->uid);

        if ($this->user->uid) :
            $query->username = $this->user->username;
            $query->password = $this->user->password;
            $query->email_addr = $this->user->emailAddr;
            $query->is_active = $this->user->isActive;
        else:
               //????????

    }

Will I go about creating the same mysql_insert_id when I don't even know if it was an insert or an update the ORM performed? Or is there another way to know the id of the last insert operation?

Answer 1

The Kohana ORM lib exposes two properties that you can use to determine the status of the record: $loaded and $saved

$loaded is set to TRUE if the record is present in the database, and $saved is set to TRUE if the record is present in the database and has no unsaved changes

Example for updating a record

// Assume I have 1 user in my database with id 3
$user = ORM::factory('user', 3);

// These will return TRUE
$user->loaded; // TRUE
$user->saved;  // TRUE

// However if we change one of the records attributes
$user->name = 'John';

// Then the changes have not been saved, so this returns FALSE
$user->saved;  // FALSE

// But the user was loaded from the database, so this returns TRUE
$user->loaded; // TRUE

// If I now save the changes
$user->save();

// Both will now return TRUE
$user->loaded; // TRUE
$user->saved;  // TRUE

Example for creating a record

// If I create a new user
$user = ORM::factory('user');
$user->name = 'Jack';

// The user has neither been loaded from the database, nor have the changes to it been saved
// So both variables will return FALSE
$user->loaded; // FALSE
$user->saved;  // FALSE

// If I now save the user, the changes have been saved and the record is present in the db
// So both variables return TRUE
$user->loaded; // TRUE
$user->saved;  // TRUE

Answer 2

I've never worked with Kohana, but if it works like some other ORM (I've used Doctrine a bit), it should set the "id" automatically for you when is has done an insert :

• if the "id" field is set in what you are trying to save, it should update the record
• else, it should insert a new record, and set the "id" field with the last inserted id returned by the database.

So you should not have to care about knowing if the ORM did an insert or an update : you will get the id eitherway (if insert, the ORM will set it from the DB ; if update, you'll know it before).

For more informations, you might take a look at Creating New Records and Adding Related Records in One-to-Many Relationships, which states (quoting) :

The save() method sets the primary key of the object (usually id) to the last_insert_id.

The given example looks like this :

// create a new page record
$page = ORM::factory('page');
$page->title = "Test Page";
$page->content = "This is a test page";
$page->save();

// create a new keyword record for the page that was just created
$keyword = ORM::factory('keyword');
$keyword->name = "testing";
$keyword->page_id = $page->id;  // $page->id has the last insert id from the above save
$keyword->save();

The "id" property of $page has been set from the DB automatically : you don't have to care about that.