How to import/export database using MySql command line

Sometimes when you have large databases, phpmyadmin or similar tool might fall when it comes to exporting or importing.
The easiest (and fastest) way is to use command line.

To export use this command:
mysqldump -u USER -p PASSWORD DATABASE > filename.sql

To import database from dump file (in this case called filename.sql) use:
mysql -u username -p password database_name < filename.sql

If you are on Windows you will need to open CMD and go to directory where mysql.exe is installed. If you are using WAMP server then this is usually located in:
C:\wamp\bin\mysql\mysql5.1.36\bin (*note the version of mysql might be different)

So you will:
cd C:\wamp\bin\mysql\mysql5.1.36\bin

and then execute one of the above commands.

Screenshot:

cmdimportdb

Advertisements

php-mysql-Export to excel sheet

<?php
$host = ‘localhost’;
$user = ‘root’;
$pass = ”;
$db = ‘testexport’;
$table = ‘testdata’;
$file = ‘export’;

$filename = $file.”_”.date(“Y-m-d”);

$link = mysql_connect($host, $user, $pass) or die(“Can not connect.” . mysql_error());
mysql_select_db($db) or die(“Can not connect.”);

$result = mysql_query(“SHOW COLUMNS FROM “.$table.””);
$i = 0;$csv_output=”;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row[‘Field’].”, “;
$i++;
}
}
$csv_output .= “\n”;

$values = mysql_query(“SELECT * FROM “.$table.””);
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].”, “;
}
$csv_output .= “\n”;
}

$filename = $file.”_”.date(“Y-m-d_H-i”,time());
header(“Content-type: application/vnd.ms-excel”);
header(“Content-disposition: csv” . date(“Y-m-d”) . “.csv”);
header( “Content-disposition: filename=”.$filename.”.csv”);
print $csv_output;
exit;
?>

very simple right 😉

Split a string like 2|3 to 2 and 3 separately

Lets consider a table name called sample1 in our database.

in that table we have a field called designation which stored value like 1|3 and 2|3 etc like that..here one value defines designation and another one as role..say 1 as designation and 3 as role..so next designation also belongs to same role..

now we need split these values in to two and store it in different fields of a table..

Eg: SELECT designation FROM sample1;

output: split

Now we need to split these values to two different fields and store separately like below image:

splitted

 

To get in above format..use this code..

SELECT SUBSTRING_INDEX(designation,’|’,1) AS degID ,SUBSTRING(designation,LOCATE(‘|’,designation)+1, LENGTH(designation) ) AS RoleID  FROM sample1

Very simple..right 😉