1

I tried to update into my database by using save(). As my primary key is an auto increment id, it creates a new row when I tried to use save().

I tried to use save($game_id, $newData) for testing and I got error message.

Below is my code :

Table add_game

id | game_id | user_id | ign  | acc_id
1  | 3       | 1       | ignA | accA
2  | 3       | 1       | ignB | accB
3  | 3       | 1       | ignC | accC

Model

protected $table = 'add_game';
    protected $allowedFields = [
        'user_id',
        'game_id',
        'ign',
        'acc_id'
    ];

Controller

$newData = [
                    'user_id' => session()->get('user_id'),
                    'game_id' => $game_id,
                    'ign' => $this->request->getVar('ign'),
                    'acc_id' => $this->request->getVar('acc_id')
                ];
    
                $model->save($newData); // currently it keeps creating new record
                $model->save($newData); // return me error message

How do I update my existing records? I want to update them base on my user_id as well as game_id. Thanks in advance guys!

Vickel
  • 7,879
  • 6
  • 35
  • 56
Dr3am3rz
  • 523
  • 1
  • 13
  • 41
  • At the very least, you need to post what the error actually is and this table's actual schema (not example rows, how the table is actually defined) for it to be possible to help you. – parttimeturtle Sep 01 '20 at 22:40

3 Answers3

0

in codeigniter 4 save() method can do insert and update depend on data you provide

in your table if u want to update to add_game table u just add the id of data you want to update

$newData = [
           'id' => 1 //add data id here
           'user_id' => session()->get('user_id'),
           'game_id' => $game_id,
           'ign' => $this->request->getVar('ign'),
           'acc_id' => $this->request->getVar('acc_id')
           ];
    
$model->save($newData);
nadim achmad
  • 96
  • 4
  • Hi Nadim, yes I know about the ID thingy but the thing is because I do not know which ID so my update condition is based on user id and game id. – Dr3am3rz Sep 03 '20 at 04:19
0

you need to have a primary key defined to make save() work for updating. In your example this primary key should/must be id:

save()

This is a wrapper around the insert() and update() methods that handle inserting or updating the record automatically, based on whether it finds an array key matching the $primaryKey value:

as you don't mention the id, obviously a new record is inserted and not updated.

in order to update, you need to supply that unique $primaryKey:

// Performs an update on id=3, since the primary key, 'id', is found.
$newData = [
    'id'       => 3,
    'user_id' => session()->get('user_id'),
    'game_id' => $game_id,
    'ign' => $this->request->getVar('ign'),
    'acc_id' => $this->request->getVar('acc_id')
];
$model->save($newData);

no need to repeat last line ($model->save($newData);), that's always calling for trouble/error

from the CI 4.x docs: Saving Data

an important read to understand the underlying SQL query you find here: INSERT ON DUPLICATE KEY UPDATE and https://mariadb.com/kb/en/insert-on-duplicate-key-update/ among many others

Vickel
  • 7,879
  • 6
  • 35
  • 56
  • Hi Vickel, I understand that I need to have a primary key defined. I have to update based on the user_id and game_id in order to update accordingly, so I can't define a primary key. Is there a way to do it using save()? Or I can only use update()? – Dr3am3rz Sep 03 '20 at 04:27
  • 1
    id in table add_game seems to be unique, change it to primary key in your phpMyAdmin of cPanel. Also save() is used for insert (new row) or update(existing row), if you are only updating, never inserting, use update() obviously – Vickel Sep 03 '20 at 13:24
  • Just curious and off topic, since most application only either insert() or update() so in what kind of scenario can we use save()? I can't really think of any. – Dr3am3rz Sep 04 '20 at 06:08
  • Imagine you have a new user and edit user form. This form could be the same. When you fill out the data with name email, etc. and set email as unique index, then it will either insert a new user, if email doesn't exist or update the user with the matching email. If you read through the links in my answer, there you find the underlying sql queries – Vickel Sep 04 '20 at 11:31
  • I see. Noted on that, still can't really visualize though but will look at those examples. Thank you! – Dr3am3rz Sep 05 '20 at 02:03
  • Any CMS (content management system) would use insert on duplicate update. The admin creates (inserts) the user, the user updates his data (except the data of the primary key, which could be an ID or an email or anything else unique, of course) – Vickel Sep 05 '20 at 10:51
  • no primary key necessarily, with a whereIn clause you reduce the data set to the one(s) you want to update (if more than one row matches the where clause, all those rows get updated): see last example of update() here: https://codeigniter.com/user_guide/models/model.html?highlight=flexible%20solution#saving-data – Vickel Sep 05 '20 at 18:02
  • or you switch to update method from query builder class: https://codeigniter.com/user_guide/database/query_builder.html?highlight=update#updating-data. Anyway, in my answer I tried to respond to your original question, did that work out? – Vickel Sep 05 '20 at 18:04
  • I don't understand what does the set active do though. I tried to use that method previously but it returns me an error "There is no data to {0}." I tried to do it another way which I am not sure whether is this the right way to do it but it works. $userCond = $model->where('user_id', session()->get('user_id'))->where('game_id', $game_id)->first(); and then $model->update($userCond, $newData); – Dr3am3rz Sep 05 '20 at 18:07
  • 1
    that's the query builder classic way, perfect to update. Use it. It wouldn't "insert on duplicate update", which was your original question about save(), but if you only want to update, it's the classic way to go. – Vickel Sep 05 '20 at 18:12
0

I solve this problem for me too.. If u have custome 'id' name, u must define it with:

$primaryKey ="custom_id"
$useAutoIncrement = false

and you must include your custom_id in AllowedField :

$allowedFelds = [
"custom_id",  // you must include it
"foo",
"bar"
]
aang lee
  • 1
  • 1