-2

I have the php query below where I want to count how many times the id exist in a column

$empCount = DB::table('m_employee')->count('group_id')->where('group_id',$idHolder);

VALUES

//tblname is m_employee; column name is group_id and my search variable is $idHolder

Error message is:

{"message":"Call to a member function where() on integer",.......

enter image description here

For this sample, when the value of my $idHolder is 1, the result of $empCount should be 13, if it's 2, result is 3. And after that, how can I use AND operator from this query :

DB::table('emp')->where('key',  $emp)->update([
   'id' => $id,
]);

Like where('key', $emp) AND ('monthyear', '06/2018'), basing the date from the cost_date column above (extracting only the month and year) .I'm having hard time on where to insert it.

  • @user3783243 Updated. Apologies for not explaining well, what I'm referring with `id` means my `code` for a specific group name. –  Jun 19 '18 at 02:36
  • Can you provide some sample data and what the output should be? – user3783243 Jun 19 '18 at 02:42
  • @user3783243 Please see update. Thanks. –  Jun 19 '18 at 02:48
  • You should use a `group by` and a `count` of each `id`. I'm not familiar with how that is done with larvel though. In regular SQL it'd be `select count(*), group_id from table where cost_date = ? group by group_id` (...assuming I'm reading what you want correctly) – user3783243 Jun 19 '18 at 02:50
  • @downvoter Mind pointing some notes on where I need to improve? –  Jun 19 '18 at 03:20

1 Answers1

1

You can simply use the query below for the count

$empCount = DB::table('m_employee')->where('group_id',$idHolder)->count();

For the AND operator, you can simply chain the where clauses to simulate this

DB::table('emp')->where('key', $emp)->where(DB::raw("(DATE_FORMAT(cost_date,'%m-%Y'))"), '=', '06-2018')->update([ 'id' => $id, ]);

undrftd
  • 331
  • 2
  • 10
  • Thanks for solving the count which works well. And can I extract a date that matches for my given month and year? Please see my sample table above. So if my date is 06/2018, it should match to `2018-06-18` column. Thanks, –  Jun 19 '18 at 02:53
  • Updated my answer above, you can try it. – undrftd Jun 19 '18 at 03:02
  • MMM, there's an error saying `unxpected ','` I try to do: `DB::table('emp')->whereRaw("(DATE_FORMAT(monthyear,'%m/%Y'))"), '=', '06/2018')->update([ 'id' => $id, ]);` Thanks. –  Jun 19 '18 at 03:25
  • I think it's not the problem, coz that works well on my other queries. So I try, `->whereRaw("(DATE_FORMAT(cost_date,'%m-%Y'))=",'06/2018')...`, error is `Syntax error or access violation: 1064 You have an error in your SQL syntax;....` –  Jun 19 '18 at 03:32
  • If you use `%m-%Y`, you should use `06-2018` in the following parameter – undrftd Jun 19 '18 at 03:34
  • The same error. Please take a look with my revisions, `...->whereRaw("(DATE_FORMAT(cost_date,'%Y-%m'))",'=', '2018-06')...` This is the line it is pointing `'(DATE_FORMAT(cost_date,'%Y-%m'))'` –  Jun 19 '18 at 03:46
  • Maybe it's not working because I'm using Eloquent, that line is the only one that is giving an error. –  Jun 19 '18 at 05:05
  • It say's unknown column, but as I checked, it's already the correct column name. –  Jun 19 '18 at 05:17
  • Updated my answer above, there were extra parentheses that is causing the error – undrftd Jun 19 '18 at 05:26
  • I have already changed that earlier, it gets back to my original error `Syntax error or access violation`. I have somewhat found [this](https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent) but I'm still trying to understand it. –  Jun 19 '18 at 05:31
  • Have you also tried fixing the parentheses of the where clause with the DB::raw? `where(DB::raw("(DATE_FORMAT(cost_date,'%m-%Y'))"), '=', '06-2018')` – undrftd Jun 19 '18 at 05:38
  • That beat it! Finally :) –  Jun 19 '18 at 05:43
  • 1
    Way to go! I'll update my answer above as a reference for others :) – undrftd Jun 19 '18 at 05:44