-
How to import / upload heavy database on localhost Xampp.
- download the sql file you want to upload in the xampp phpmyadmin. eg olddb.sql
- create a database in localhost/phpmyadmin. eg mydb
- Go to xampp cpanel and click on Shell button on right.
- The CMD for sql window will open
- write the command over there and done. command is mysql -u username -p database_name < “/path/file.sql”
# mysql -u root -p mydb < "C:\olddb.sql"
-
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 workSimple 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(); -
#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.
