PDO and associative arrays
- From: Gordon <gordon.mcvey@xxxxxxxxxxxx>
- Date: Mon, 30 Nov 2009 02:48:12 -0800 (PST)
I am in the process of refactoring our CMS. The design uses an object
oriented approach with all available data types in the CMS (folders,
articles, images, surveys etc) descending from an abstract CmsItem
class.
In the previous iteration of the CMS you would update item properties
by setting their values in the object you wanted to update, then
calling updateItem () on the object. This approach worked fairly
well, but it had the big drawback that if the update failed for
whatever reason, the object's internal state would no longer be
consistant with what was in teh database.
I wanted to update the CMS and include an updateItem method that would
only update the object's internal state if the database update was
successful. My plan was to pass an associative array into the method,
have the database update with it and then if the update was
successful, merge the new values with the current ones. Basically,
I'd pass in some props as an array, array_merge the new props with the
current ones and store the result in a local var, use the var with a
PDO prepared statement to update the database, and if this was
successful, overwrite the object's properties with the local variable
so that its state was up to date.
However, I have hit a snag. It seems that PDO will accept an array
that contains an array with the fields to be updated, and only those
fields. If there are fields that should be ignored in the mix then
PDO will throw an error and the update will fail.
Eventually I came up with a solution, but it's not a tidy one. Here's
the code for it:
public function updateItem (array $newData)
{
$newProps = array ();
$newProps ['usr_id_modify'] = self::$user -> userProps ['id'];
$newProps ['itm_parent'] = array_key_exists ('itm_parent',
$newData)?
$newData ['itm_parent']:
$this -> itemProps ['itm_parent'];
$newProps ['itm_sort'] = array_key_exists ('itm_sort', $newData)?
$newData ['itm_sort']:
$this -> itemProps ['itm_sort'];
$newProps ['itm_publish'] = array_key_exists ('itm_publish',
$newData)?
intval ($newData ['itm_publish']):
intval ($this -> itemProps ['itm_publish']);
$newProps ['itm_title'] = array_key_exists ('itm_title', $newData)?
$newData ['itm_title']:
$this -> itemProps ['itm_title'];
$newProps ['itm_path'] = array_key_exists ('itm_path', $newData)?
$newData ['itm_path']:
$this -> itemProps ['itm_path'];
$newProps ['itm_summary'] = array_key_exists ('itm_summary',
$newData)?
$newData ['itm_summary']:
$this -> itemProps ['itm_summary'];
$newProps ['itm_notes'] = array_key_exists ('itm_notes', $newData)?
$newData ['itm_notes']:
$this -> itemProps ['itm_notes'];
$newProps ['itm_keywords'] = array_key_exists ('itm_keywords',
$newData)?
$newData ['itm_keywords']:
$this -> itemProps ['itm_keywords'];
$newProps ['site_id'] = array_key_exists ('site_id', $newData)?
$newData ['site_id']:
$this -> itemProps ['site_id'];
$newProps ['itm_archive'] = array_key_exists ('itm_archive',
$newData)?
intval ($newData ['itm_archive']):
intval ($this -> itemProps ['itm_archive']);
$newProps ['itm_visible'] = array_key_exists ('itm_visible',
$newData)?
intval ($newData ['itm_visible']):
intval ($this -> itemProps ['itm_visible']);
$newProps ['itm_id'] = $this -> itemProps ['itm_id'];
$query = 'UPDATE cms_items
SET itm_date_modify = NOW (),
usr_id_modify = :usr_id_modify,
itm_parent = :itm_parent,
itm_sort = :itm_sort,
itm_publish = :itm_publish,
itm_title = :itm_title,
itm_path = :itm_path,
itm_summary = :itm_summary,
itm_notes = :itm_notes,
itm_keywords = :itm_keywords,
site_id = :site_id,
itm_archive = :itm_archive,
itm_visible = :itm_visible
WHERE itm_id = (
SELECT cms_items.itm_id
FROM cms_items
LEFT JOIN cms_locks
ON cms_items.itm_id = cms_locks.itm_id
WHERE cms_items.itm_id = :itm_id
AND (
cms_locks.usr_id = :usr_id_modify
OR cms_locks.usr_id IS NULL
)
);';
$prepared = self::$db -> prep ($query);
if ($success = ($prepared -> tell ($newProps)) == 1)
{
$this -> itemProps = array_merge ($this -> itemProps, $newProps);
}
return ($success);
}
self::$user is an object representing the currently logged in user.
self::$db is a Database object. Database descends from PDO and adds a
couple of extra methods. For example, Database::prep is a method that
behaves like PDO::prepare, but caches the prepared statement. If you
feed the same SQL string into it more than once then the subsequent
calls will return the previously created prepared statement instead of
recreating it.
PDOStatement also got extended, with a tell() method which basically
does an execute() and returns a count of affected rows. It does some
other stuff too but that's not important to the context of this
problem.
As you can see from the code above, the problem I've run into is that
the only way I can pass in a valid associative array to the tell (and
therefore the execute) method is by manually building the array, one
element at a time. This is ugly, inelegant, and prone to bugs caused
by typos. I really want to avoid it at all costs.
As the refactoring is at an early state at the minute, there is a lot
of state for which no updating is implemented yet. For example, the
article object will have its own updateitem that updates its own table
before calling parent::updateitem to trigger the updating of the
generic CmsItem fields. However, there are also fields that are
computed rather than fetched from a database, and these will always be
problematic. If they get passed into PDO they will trigger the error
and cause the update to fail.
Basically, what I need is either a more intelligent way of building
the associative array, or a way of getting PDO to simply ignore fields
that aren't relevant to the query it is currently executing. The
latter would probably be easier, assuming that it is possible. How
can I do this? If you can't get PDO to ignore these fields, can you
suggest a better way of building the array up than manually?
.
- Follow-Ups:
- Re: PDO and associative arrays
- From: Gordon
- Re: PDO and associative arrays
- Prev by Date: Re: print variable inside function
- Next by Date: Re: sample code online...with a few flaws.
- Previous by thread: sample code online...with a few flaws.
- Next by thread: Re: PDO and associative arrays
- Index(es):
Relevant Pages
|
