-2

Using this query, i will append the userid in table but it will insert in continuous i want to separated by , plz suggest change in query 

 DB::table('table_user_create_activity')
               ->where(['activity_id'=>$query_get_activity->activity_id])
              ->update(['accepted_join_id'=>DB::raw('CONCAT(ifnull(accepted_join_id,""),'.$get_user_id.')')]);
user11708206
  • 5
  • 5
  • Please make your questions clearer, what are the steps in detail? Also some sample data would be helpful to understand where you are coming from and what the goal ist. Thanks! – frankfurt-laravel Jul 11 '19 at 10:32
  • @frankfurt-laravel actually i want to append value in table column like1,2,3 but my query will stored like123 plz suggest change in query. – user11708206 Jul 11 '19 at 10:45
  • 1
    *" i will append the userid in table but it will insert in continuous i want to separated by ,"* Good read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Raymond Nijland Jul 11 '19 at 11:17

3 Answers3

0

The problem is in your DB::raw SQL statement. You are using MySQL concat, but that method does not add a separator. For adding a separator use concat_ws (ws = with separator) and then the first argument is your separator. 

->update(['accepted_join_id'=>DB::raw("CONCAT_WS(',', ifnull(accepted_join_id,''),".$get_user_id.')')])

A sidenote, your query is open for sql injections because of $get_user_id is added in the DB::raw. To close the sql injection change it to the following.

->update(['accepted_join_id'=>DB::raw("CONCAT_WS(',', ifnull(accepted_join_id,''),?)", [$get_user_id])])

This way we escape the user id before adding it in the query.

Oliver Nybroe
  • 1,828
  • 22
  • 30
  • SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (`activity_id` = ?)' at line 1 (SQL: update `table_user_create_activity` set `accepted_join_id` = CONCAT_WS( where (`activity_id` = 7)) – user11708206 Jul 11 '19 at 11:07
  • Btw your query is open for sql injections as `$get_user_id` is not escaped. – Oliver Nybroe Jul 11 '19 at 11:18
  • hello but it save like this format ,2,2 i want save like 2,2. When column contain null value it first save , then value like ,2,2 – user11708206 Jul 11 '19 at 11:21
  • Try to remove the `ifnull` check, as `concat_ws` ignores null values. – Oliver Nybroe Jul 11 '19 at 11:26
0

Hope This solves Your Problem

DB::table('table_user_create_activity')
    ->where([ 'activity_id' => $query_get_activity->activity_id ])
    ->update([
        'accepted_join_id' => DB::raw("CONCAT(ifnull(accepted_join_id,''), ',' , $get_user_id)")
    ]);
Malkhazi Dartsmelidze
  • 4,783
  • 4
  • 16
  • 40
  • SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (`activity_id` = ?)' at line 1 (SQL: update `table_user_create_activity` set `accepted_join_id` = CONCAT(ifnull(accepted_join_id,""), where (`activity_id` = 7)) – user11708206 Jul 11 '19 at 11:09
0

DB::table('table_user_create_activity')->where(['activity_id'=>$query_get_activity->activity_id])->update(['accepted_join_id'=>DB::raw('CONCAT(ifnull(NULL,accepted_join_id),'.$get_user_id.')')]);

Nilesh Saidane
  • 1
  • 1