FuelPHP Forums

Mike

$data = array(
'country_name' => $country_name, 
'country_code' => $country_code);
DB::insert('countries', array_keys($data))
->values(array_values($data))
->execute();

Is this the way you should insert data?
Is there an ActiveRecord way to do this?
Is there a way to do an update_on_duplicate? For example if you have a countries table and you want to update say once a month. Countries can be updated if exist, can be new thus inserted and can be deleted. The deletion must be done by a find_all() and loop and compair and delete when needed. But what about an update_on_duplicate?

Ihrefelder

Hey Mike,
there's an Activerecord-Way to insert / update your data.

// For Example 
$country = new Model_Country; 

$country->name = 'New Zealand'; 
$country->postcode = 12345; 

$country->save();

This Way is much faster than your one. You can update this record on same way.

// For Example 
// Imagine New Zealand got id = 1 in your table
$country = Model_Country::find(1); 

// Ah, gnarf postcode was wrong, so let's fix it 
$country->postcode = 23478; 
$country->save();

Indeed, for update_on_duplicate you will have to handle your own way.
How should MySQL know, if an entry is duplicated? Maybe, you could handle this with "on duplicate key"
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Mike

Thx Ihrefelder, the AR way is much faster. I should have told more about the context. Two columns, country_code and country_name, country_code is unique. Thus there is no column id. Do I have to:

Model_Country::$primary_key = 'country_code';

This will only effect the query and set to default (id) after or do I have to set after the query again?

Model_Country::$primary_key = 'id';

MySQL will see it already exists (due to unique) and will update if an on duplicate key is given. That was what I ment. The input is not one record but an array of 250 items. Thus I have to loop? Or is there a way of inserting an array? What I'm trying to accomplish is to have if possible one query to speed things up. Do I have to use Model_Country::query($sql); to do this? This way I end up using a query sting. Do I need to Db:escape(); or is this done automatically? Sorry, still lots of questions. I would have set up the Model, Db and ActiveRecord guides if I exactly new how everything is working for Fuel.

Phil Sturgeon

Does this work for you?

Model_Country::find_by_country_code('USA');

Mike

Thx Phil, I already use that. You still have to loop and make 250 queries. For now fine. Will look on this later when I know more about ActiveRecord Model DB.

snrp

so you want to make one long string of "insert...; insert...;insert...;" and pass it to some database function to do the query, is that it ? You can use the DB class for that (the DB class is taken from kohana, so kohana documentation is valid): http://kohanaframework.org/guide/database/query/prepared

Mike

This is what I want to do:

$countries = array(
    array(
        'country_code' => 'nl',
        'country_name' => 'Netherlands'
    ),
    array(
        'country_code' => 'us',
        'country_name' => 'United States'
    ),
    // etc. 250 countries
);

$country = new Model_Country;
$country->data_array = $countries;
$country->save;

I know you can do something like this:

foreach ($countries as $data)
{  
    $country = new Model_Country;
    $country->country_code = $data['country_code'];
    $country->country_name = $data['country_name '];
    $country->save;
}

But is there a way just passing an array (or object) with table column names as key and let AR do the rest (foreach)? One step further, check if exists and do update if exists... I know that ON DUPLICATE KEY UPDATE is a MySQL part (and only works if country_code is UNIQUE in this case) thus not implemented in AR. But what is THE way to do this using AC? Like this?

foreach ($countries as $data)
{  
    $country = Model_Country::find_by_country_code($data['country_code']);

    if ($country)
    { 
        $country->country_name = $data['country_name '];
        $country->save;
    }
    else
    { 
        $country = new Model_Country;
        $country->country_code = $data['country_code'];
        $country->country_name = $data['country_name'];
        $country->save;
    }
}

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion