Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Tuesday, 4 October 2016

convert column name into lowercase in mysql and php

Helllo

convert column name into lowercase in mysql and php

some time we want all table column name in lower case then we can change to manually but here
example you can paste this query after run query. you can change you column name in to lower case


convert column name into lowercase in mysql and php example start.

<?php
$c1 = mysql_connect("localhost","root","");// Connection




$db1 = mysql_select_db("INFORMATION_SCHEMA");



$get_column = mysql_query("SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='data_base_name' AND `TABLE_NAME`='table_name'");

while($row = mysql_fetch_assoc($get_column)){


$old_name = $row['COLUMN_NAME'];
$new_name = strtolower($row['COLUMN_NAME']);
$datatype= $row['DATA_TYPE'];
$size = $row['CHARACTER_MAXIMUM_LENGTH'];



if($row['DATA_TYPE'] !="varchar" && $row['DATA_TYPE'] !="text"){
$query =  "ALTER TABLE mstusers CHANGE $old_name $new_name $datatype".";<br/>";
}else{

$query =  "ALTER TABLE mstusers CHANGE $old_name $new_name $datatype ($size)".";<br/>";
}
echo $query;


}

// Query paste in your  phpmyadmin



?>

Please setup below code replace table_name with you table name and database_name replace with you
database name. also setup up your database connection.

After check all query for convert column name into lowercase in mysql and php.

i think it work for you.

convert column name into lowercase in mysql and php

Tuesday, 10 May 2016

Truncate all tables in a MySQL database in one command

Truncate all tables in a MySQL database in one command

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('databasename1','databasename2');
 
if Cannot delete or update a parent row: a foreign key constraint fails
 
That happens if there are tables with foreign keys references to the table you are trying to drop/truncate.
Before truncating tables All you need to do is:
SET FOREIGN_KEY_CHECKS=0;
Truncate your tables and change it  back to 
SET FOREIGN_KEY_CHECKS=1; 

user this php code
<?php


mysql_connect("localhost","root",'');

mysql_select_db("restaurant");

$truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('restaurant')");


while($truncateRow=mysql_fetch_assoc($truncate)){

mysql_query($truncateRow['tables_query']);


}


?>
  

Sunday, 6 December 2015

How to Compare two strings using stored procedure in Mysql


 Compare two strings using stored procedure in Mysql
  1. Only need one (1) equals sign to evaluate
 DECLARE @tempo VARCHAR(20)
    SET @tempo = 'test'

IF @tempo = 'test'
  SELECT 'yes'
ELSE
  SELECT 'no'

return yes of this output

DECLARE @tempo VARCHAR(20)
    SET @tempo = 'test1'

IF @tempo = 'test'
  SELECT 'yes'
ELSE
  SELECT 'no'

return no in this output


you can also use interger  as value

set below type code for integer


 SET @temp = 1

IF @temp = 1
  SELECT 'yes'
ELSE
  SELECT 'no'
 
  return yes
 
 


IF @temp = 2
  SELECT 'yes'
ELSE
  SELECT 'no'
 
  return no


Split String with comma Loop in Mysql store Proceture Example

Hello

Here Very Usefull for Split String with comma Loop in Mysql store Proceture Example

Split String with comma Loop in Mysql store proceture
this fully example how to use

Loop In Mysql

and split string in mysql

begin
  
   
   
    SET @InitCounter := 1;
      SET @Param := "11,22,33,44,";
    
 
  
  
      
    myLoop: loop 
   
 SET @NextCounter := LOCATE(',',@Param);

 SET @SUBSTR := SUBSTRING(@Param,@InitCounter,@NextCounter);

 SET @Param := REPLACE(@Param,@SUBSTR,'');

 SET @SUBSTR:= REPLACE(@SUBSTR,',','');

 SET @ParamLength := LENGTH (@Param);


 SELECT @SUBSTR;




                      
        if  
       
             @ParamLength = 0
        then
            leave myLoop;             
        end if;
       
    end loop myLoop;                   
   
END

Monday, 16 November 2015

how to remove index and unique constraint in mysql

Hello

here Example of remove how to remove index and unique constraint in mysql

first check table how many constraint  in table after you can remove.

here the query for show index in table

SHOW INDEX FROM Your-table-Name

after remove all index name display in column

here the query

ALTER TABLE Your-table-Name DROP INDEX Your-field-unique-constraint -name;

Please check this