Insert New Row If Not Update

Question

When my user login on in the activity's model. If the user has logged on before it updates that row belonging to the user id. Otherwise should Insert row.

Problem: Currently it just updates the same row as previous. It should insert a new row if user id has not logged on before.

How can I fix problem on model.

<?php 

class Model_account_activity extends CI_Model {

  public function updateActivity($key, $data) {
      $data['user_id'] = $this->session->userdata('user_id');

      if (isset($data['user_id'])) {
         $user_id = $data['user_id'];
      } else {
         $user_id = 0;
      }

      $query = $this->db->query("UPDATE `" . $this->db->dbprefix . "user_activity` SET 
         `user_id` = '" . (int)$user_id . "', 
         `key` = " . $this->db->escape($key) . ", 
         `data` = " . $this->db->escape(serialize($data)) . ",
         `date_added` = NOW()
      ");

      if ($query == FALSE) {
        $this->addActivity();
        return true;
      } else {
        return false;
      }
   }

    public function addActivity($key, $data) {
      $data['user_id'] = $this->session->userdata('user_id');

      if (isset($data['user_id'])) {
         $user_id = $data['user_id'];
      } else {
         $user_id = 0;
      }

      $this->db->query("INSERT INTO `" . $this->db->dbprefix . "user_activity` SET 
         `user_id` = '" . (int)$user_id . "', 
         `key` = " . $this->db->escape($key) . ", 
         `data` = " . $this->db->escape(serialize($data)) . ",
         `date_added` = NOW()
      ");
   }
}

it seems to me like your UPDATE query will run regardless. The conditional should be before the query. Can you provide more information? — CodeGodie, Oct 21 '14 at 02:08
I also see when you call the addActivity function you are missing the parameters. Can you provide more information? — CodeGodie, Oct 21 '14 at 02:14
If you are using MySQL, you could use [`ON DUPLICATE KEY UPDATE`](http://stackoverflow.com/a/6853256/1980659). That would shorten your code. — ForguesR, Oct 21 '14 at 02:34
@Diego Camacho If `user id` does not exist in that table, it should insert a new row or else it would be a update that is what I am trying to achive — , Oct 21 '14 at 02:51

MH2K9 · Accepted Answer · 2014-10-21T03:22:53.953

0

Get result from DB by unique ID or primary key then check the query result. If result count is 0 then can insert else update. Example...

$this->db->where('column_name', $user_id); //Unique column or primary_key .It may be $user_id
$query = $this->db->get('table_name');
if($query->num_rows == 0)
{
    //Insert query
}
else
{
    //Update query
}

edited Oct 21 '14 at 03:22

answered Oct 21 '14 at 03:09

MH2K9

11,951
7
32
49

You should use unique `ID`. Is `user_id` unique or `primary_key` ? – MH2K9 Oct 21 '14 at 03:21
activity_id is what primary key is called – Oct 21 '14 at 03:27
Check your update `query`. You didn't add `where` condition in your update `query`. As result all `rows` updated. – MH2K9 Oct 21 '14 at 03:30
`@Please Wait` I was able to get it working from your advice. – Oct 21 '14 at 03:39

score 0 · Answer 2 · answered Oct 21 '14 at 03:38

Here is how i was able to get it working thanks to @Please Wait all working now.

<?php 

class Model_account_activity extends CI_Model {

  public function activity($key, $data) {
   $user_id = $this->session->userdata('user_id');

   $this->db->where('user_id', $user_id);
   $query = $this->db->get('user_activity');

   $data['user_id'] = $this->session->userdata('user_id');

   if (isset($data['user_id'])) {
      $user_id = $data['user_id'];
   } else {
      $user_id = 0;
   }

   if($query->num_rows() == FALSE) {

      $data = array(
        'user_id' => $user_id,
        'key' => $key,
        'data' => serialize($data),
        'date_added' => mdate('%Y-%m-%d %H:%i:%s', now())

      );
      $this->db->insert_id();
      $this->db->insert('user_activity', $data);

   } else {

      $data = array(
        'user_id' => $user_id,
        'key' => $key,
        'data' => serialize($data),
        'date_added' => mdate('%Y-%m-%d %H:%i:%s', now())

      );
      $this->db->where('user_id', $user_id);
      $this->db->update('user_activity', $data);
   }
  }
}

Insert New Row If Not Update

2 Answers2