• laravel,  php,  phpmyadmin

    Laravel join sql query syntax with where between group by

    Its a query to join two tables and get the details in between given date format ,according to month (month wise) and of specific city .

    sample work

    Simple SQL query

    the query in simple SQL to be added in any language .

    select count(*) as count , city , MONTH(`abctable`.`created_at`) as mm, YEAR(`abctable`.`created_at`)as yy from `abctable` join `xyztable` on `abctable`.`entity_id` = `xyztable`.`parent_id` where `abctable`.`created_at` between '2019-04-24 00:00:00' and '2019-09-12 12:12:12' AND `xyztable`.`city`='Pune' GROUP BY YEAR(`abctable`.`created_at`),MONTH(`abctable`.`created_at`)

    Laravel SQL query

    the sql query in laravel controller form data.

     $users = DB::table('abctable')
            ->select(DB::raw('count(*) as count, MONTH(abctable.created_at) as MM, YEAR(abctable.created_at) as YY'))
            ->join('xyztable', 'n3d_sales_flat_order_grid.entity_id', '=', 'xyztable.parent_id')
            ->whereBetween('abctable.created_at',[new Carbon($fdate),new Carbon($gdate)])
            ->where('xyztable.city','like',$city)
            ->groupBy(DB::raw('MONTH(abctable.created_at)'),DB::raw('YEAR(abctable.created_at)'))
            ->get();
  • php,  phpmyadmin

    #1067 – Invalid default value for ‘time’ – solution

    when altering the database table error displays – 1067 – Invalid default value for ‘time’

    Solution is : There might be a datetime field with Null and default value 0000-00-00 00:00:00.

    change the datetime field Null uncheck and default value None

    change the date time field default value to none and uncheck NULL

    Thus you can alter the database now for other field add, remove and further operations.