Disable ONLY_FULL_GROUP_BY

Disable ONLY_FULL_GROUP_BY

 

Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin

– Open phpmyadmin & select localhost

– Click on menu Variables & scroll down for sql mode

– Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save

 

 

 

 

To keep your current mysql settings and

disable ONLY_FULL_GROUP_BY

I suggest to visit your phpmyadmin or whatever client you are using and type:

SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')  copy_me

 

next copy result to your my.ini file.

For mint:

sudo nano /etc/mysql/my.cnf

For ubuntu 16 and up:

sudo nano /etc/mysql/my.cnf

For ubuntu 14-16:

/etc/mysql/mysql.conf.d/mysqld.cnf

 

 

 

Codeigniter – How to Join City Country State by concatenate two fields in Sql

Codeigniter – How to Join City Country State by concatenate two fields in Sql $this->db->select(” CONCAT(c.name,’-‘,co.code) as id,CONCAT(c.name,’ ,’,s.name,’ ,’,co.name) as text”,FALSE); $this->db->from(“city c”); $this->db->join(‘state s’, ‘s.id=c.state_id’, ‘left’); $this->db->join(‘countries co’, ‘co.id=s.country_id’, ‘left’); $this->db->where(‘c.status’,’Y’); $this->db->order_by(‘c.id’, ‘ASC’); $query = $this->db->get();

Update Two Column Field By Splitting The VAlue from One Column in SQL.

Update Two Column Field By Splitting The VAlue from One Column in SQL.

Here the split will be done depending on space in name. 
Here the split will be done depending on space in name. 
For Single name the lastname field will be entered as NULL.
If The Name (Like: Aathav Sharma) Field has Two words seperated by space,
then the firstname will be the first word (Aathav) 
and the other word will be the last name (Sharma).

#For updating Particular RowsSQL 

QUERY::
UPDATE user u2 
set  
u2.first_name = IF( 
                     LOCATE(' ', `name`) > 0,                        
                     SUBSTRING(`name`, 1, LOCATE(' ', `name`) - 1),                        `name`                    
                 ),     
u2.last_name  = IF(
                     LOCATE(' ', `name`) > 0,  
                     SUBSTRING(`name`, LOCATE(' ', `name`) + 1),                             NULL 
                  )

WHERE  u2.id <26;

#For Updating All Rows::

SQL QUERY::

UPDATE 
user u2  
set  
u2.first_name = IF( 
                    LOCATE(' ', `name`) > 0,
                    SUBSTRING(`name`, 1, LOCATE(' ', `name`) - 1),                          `name`                    
                  ),     
u2.last_name  = IF(
                   LOCATE(' ', `name`) > 0,  
                   SUBSTRING(`name`, LOCATE(' ', `name`) + 1),
                   NULL 
                  )

WHERE 1;