Technical Details and PHP Developer

updating two tables in a single query in mysql UPDATE  tb1 , tb2  SET  tb1.name= 'new name', tb2.name = 'tb2 name'   WHERE   tb1.id = '1'  AND  tb2.id = '5'

delete record from two table in same time in mysql  it is useful when we create two related table in mysql     // with inner join  DELETE tb1, tb2 FROM tb1 inner JOIN tb2  WHERE tb1.id=tb2.tb1id and tb1.id=1  // with left or Right Join  DELETE tb1, tb2 FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.tb1id  WHERE tb1.id=1

group_concat in mysql example CITY_ID CITY_NAME STATE_NAME 1 Bhopal MP 2 Indore MP 3 Delhi DELHI 4 Allahabad UP SELECT group_concat(concat(`city_id`,'@@', `city_name`,'@@', `state_name`),'###') as city FROM `city` Result 1@@Bhopal@@MP###,2@@Indore@@MP###,3@@Delhi@@DELHI###,4@@Allahabad@@UP###

1 . create query for change all tables to innodb  ENGINE in   mysql SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands FROM information_schema.tables WHERE table_schema = 'database_name' ORDER BY table_name DESC; 2 . create query for  TRUNCATE TABLE  all database table SELECT CONCAT('TRUNCATE TABLE ', table_name, ';') FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'database_name'

1.  export in mysql in txt file  SELECT * FROM  MyTable INTO OUTFILE  'C:\\FileName.txt'  2.   export in mysql in csv file  SELECT * FROM table1  INTO OUTFILE 'c:///mytable.csv' FIELDS ESCAPED BY '""' TERMINATED  BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\r\n'  3. .  export in mysql in excel file

<?php  MYISAM Not Support Transaction  /* CREATE TABLE IF NOT EXISTS `table1` (   `id` int(11) NOT NULL auto_increment,   `name` varchar(250) NOT NULL,   `address` varchar(250) NOT NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; */ $host  =  'localhost' ;  $user  =  'rajeev' ;  $password  =  'mypass' ; $db  =  'demo' ;  $con  =  mysql_connect ( $host ,  $user ,  $password ); mysql_select_db (  $db ); mysql_query ( "SET AUTOCOMMIT=0" ); mysql_query ( "START TRANSACTION" );     $query  =  "INSERT INTO table1 (name,address) values ('name','bhopal')" ;   $result  =  mysql_query ( $query );    if( $result ){ ...

create new user in mysql database set user permissions mysql delete user permissions in mysql rajeev =User Name mypass = Password demo = database name   // create user CREATE USER ' rajeev '@'localhost' IDENTIFIED BY  ' mypass ' ;     // set permission  in demo database  GRANT SELECT, INSERT, DELETE ON   demo .* TO rajeev @'localhost' IDENTIFIED BY ' mypass ';  // set all permission in demo database GRANT ALL ON demo .* to ' rajeev '@'localhost';  // remove access permission from demo database REVOKE select, UPDATE, DELETE ON demo .*  FROM ' rajeev '@'localhost';   // remove all permission  REVOKE ALL PRIVILEGES, GRANT OPTION FROM ' rajeev '@'localhost';  // select user detail  from database  select * from mysql.user where User=' rajeev ';

gender = column name table = table name SELECT * , CASE   gender when 'm' then 'male'               when 'f' then 'Female'   else gender END as ty FROM  Table SELECT * , CASE  when   id > 5   then 'male'               when   id < 5   then 'Female'   else gender  END as ty FROM  Table

how to get top 3 records in sql   , or   how to get top 3 (RECENT ) record from mysql query : SELECT  * FROM TABL_NAME ORDER BY ID DESC LIMIT 0,3  how to update multiple rows in mysql with one query with different values UPDATE `tbname` SET `column_name` = CASE  `id` WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' END

id c1 c2 c3 1 10 20 30 2 15 50 24 SELECT 'C1' as colname, c1 as colval FROM `tablename` UNION ALL SELECT 'C2' as colname , c2 AS colval FROM `tablename` UNION ALL SELECT 'C3' as colname , c3 AS colval FROM `tablename` C1 10 c1 15 c2 20 c2 50 c3 30 c3 24 display column values as rows wise union operation in mysql 1. NUMBER OF COLUMN IS SAME  2. DATA TYPE IS SAME BUT NOT NECESSARY IN MYSQL  SELECT *   FROM `table1`   UNION   SELECT *  FROM `table2`  LIMIT 0 , 30

INSERT INTO TABLE2 SELECT * FROM TABLE1 INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 copy table one database to another database CREATE TABLE database2.`tablename2` SELECT * FROM database1.`tablename1` NOTE: it must you have database1 , database2 and tablename1 Create similar table in mysql CREATE TABLE new_table_name LIKE tablename2; rename table in mysql RENAME TABLE table1 TO table2 ;

1.  count() 2. max(); 3. in() ; 4. min(); 5. avg(); 6. sum(); 7.  date() 8. date different ()

LEFT JOIN Operation IN MYSQL 1. Select All row from Table Left (Table 1) even if there are no matches in the right table (table2). 2. select All Row from Right Table (Table2) who is match in left Table ; SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.colName=Table2.colName RIGHT JOIN Operation IN MYSQL 1. Select All row from RIGHT Table (Table 2) even if there are no matches in the left table (table1). 2. select All Row from left Table (Table1) who is match in RIGHT Table ; SELECT * FROM Table1 RIGHT JOIN Table2 ON Table1.colName=Table2.colName FULL JOIN Operation IN MYSQL 1. Select All row from Both Table(Table1 , Table2) SELECT * FROM Table1 FULL JOIN Table2 ON Table1.colName=Table2.colName

unique  is key and its always unique,    that means no repeat  , unique is just like primary key but unique  may be NULL and primary key is not NULL how to create unique key in mysql table CREATE TABLE  tablename ( id  int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), UNIQUE (id) ) how to create unique key in mysql with multiple columns create unique key with combination of multiple column alter table  tbName  add unique index( column1 ,  column2 ,  column3 ,.....)

how to change string into date in mysql //how to change string into date in mysql  str_to_date(string_data, format)   Example :       select *,floor(datediff('".date("Y-m-d")."',str_to_date('dob','%d-%m-%Y'))/365) as agediff from tablename       dob = column name  and varchar type