Increase count value for keyword when searched - Database error

0
votes

UPDATED

I have a function that takes keywords from search queries and returns relevant results to the user. I'm trying to update the count column in my keywords table with a value increase by 1 for each keyword when that keyword is searched. However, I'm getting:

Unknown column '0' in 'field list'

UPDATE keywords SET 0 = Array WHERE id = '|'

Part of my search function:

$query = $this->db->select('*')->from('keywords')->WHERE('keyword', $term)->get();

        // array used to save individual strings to DB with their ranking & ids
        $tempArray = array();

        if($query->num_rows() > 0){
            foreach($query->result() as $row){

                $entry_ids = json_decode($row->entry_ids);
                $initial_count = $row->count;

                foreach($entry_ids as $id){
                    $item = explode("|", $id);

                    if(!$this->search_array($item[0], $tempArray)){
                        $search['count'] = $initial_count++;
                        $search['keyword'] = $term;
                        $search['id'] = $item[0];
                        $search['ranking'] = $item[1];

                        array_push($tempArray, $search);

                        $this->db->where('id', $id);
                        $this->db->update('keywords', $tempArray);
1
phpsqlcodeignitersearchcount

1 Answers

1
votes

The last if doesn't seem correct. This is what I would have done : 

if(!$this->search_array($item[0], $tempArray))
{
      $updatedData = array(
         "count"   => $row->count + 1, 
         "keyword" => $term,   //As I see it, this is unnecessary
         "id"      => $item[0],
         "ranking" => $item[1]
      );
      //I suppose that count, keyword, id and rankings are columns name in your table named keywords

      $this->db->where('keyword', $term);
      $this->db->update('keywords', $updatedData);
}