Multiple Checks For Matching Row In Db Without Having To Query Everytime

Hi folks,

I have a large amount of data coming from an XML file. The parsing of the XML file is done and produces hundreds or thousands of arrays of data with may or may not need to be written to the DB. The condition for them being written to the DB is based on whether they are already present in the DB.

For example, I would have the fields in the DB like this: -

id (PK)

product name

product category

product price

Aside from the PK, the other 3 fields will be populated with variables pulled from the XML.

The way I have written it right now is by using findByAttributes on the appropriate model to check to see if an active record is returned. If not, I know it does not exist and can create a new model to populate the DB with. However, two thoughts I am having here are: -

  1. Is there anyway to create an dataset from the DB that will be held in memory for the duration of the script, and will of course be recreated if/when a new record is sent to the DB?

  2. If the above is not possible and given that the DB has a unique key for the fields to be populated by the XML variables above (and this is also represented in the model by way of the UniqueAttributesValidator extension), would it simply be less taxing on the DB to just attempt to save a new model of the parsed attributes each time rather than attempt to first model by findbyAttributes then save if not found? That seems to make sense to me.

Any advice would be greatly appreciated!



Assuming that you would have a second class that creates or updates the CActiveRecord product objects, you could hold a variable within that first class that represents all product objects. This could either be an array of product-objects (created by $this->_products = Product::model()->findAll()) or an array of name, category and price. You could then refer to that variable when searching (foreach($this->_products as $product)) and determine whether it exists and update or create a new record. If you use a function to do so for that in the first class, you could there add some logic that updates the $_products when needed (by adding the newly created records or populating the whole object-set again).

Hi Emile,

Thanks for your input.

I think the array scenario might be the quickest and easiest to develop.

The XML parser is itself a class, and has variables and arrays within it already. So essentially I could create a multidimensional array that looking something like this: -

$this->DBproducts = array(product1 = array(name => 'name1', category => 'category1', price => 'price1'), array(name => 'name2', category => 'category2', price => 'price2'), array(name => 'name3', category => 'category3', price => 'price3'))

Then, once the parser has parsed $this->productName, $this->productCategory and $this->productPrice, I can do something like this

foreach($this->DBproducts as $DBproduct) {

if ($DBproduct['name'] == $this->productName && $DBproduct['category'] == $this->productCategory && $DBproduct['price'] == $this->productPrice) {

update record where conditions match




create new model and save to DB



Does that look about right?

I would say so; don’t forget to update your array in your else-block.

Will do mate - I’ve already coded that functionality.

Thanks again, much appreciated!