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

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 ūüėČ

Mysql split string in query

I would like to share, How to split string in MySQL query. While we design poor database design, we are forced to construct complex MySQL queries. Luckily MySQL has wonderful string functions. So we are going to use few of those functions to solve the issue.

Task :¬†¬†Split ‚Äú1000_33‚ÄĚ string by underscore ( _ ) in mysql query and get first part of string.

We usually do this in php using explode function like below.

1
2
3
4
<?php
$arr = explode("_","1000_33"); // splitting string by underscore( _ ).
$arr[0]; // getting first part of string
?>

The same thing we are going do in Mysql Query here.

Solution :

1
2
3
4
5
SELECT CASE LOCATE( '_', '1000_33' )
WHEN '0'
THEN '1000_33'
ELSE SUBSTRING_INDEX( '1000_33', '_', 1 )
END AS first_part_string

Explanation :

LOCATE function is used to figure out is the string has underscore ( _ ) or not. Suppose string doesn’t has underscore, It will return 0 else it will return position of underscore.
So,if string doesn’t have underscore means it will the query will return full string.

SUBSTRING_INDEX is used to split string. LOCATE function ensures string has underscore. So  SUBSTRING_INDEX function will split the first part of string

SUBSTRING_INDEX( ¬†‚Äô1000_33‚Ä≤, ¬†‚Äô_’, 1 )

 

  • Parameter 1 : String
  • Parameter 2 : Search String (Underscore)
  • Parameter 3 : Position of String

Hope this helps you ūüôā

How to create a stored procedure in MySQL

Introduction to SQL Stored Procedures

Summary: in this tutorial, you will learn what a stored procedure is, its advantages and disadvantages.

Definition of stored procedure

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.

A stored procedure which calls itself is known as recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it very well. You should check your version of MySQL database before implementing recursive stored procedures.

Stored Procedure in MySQL

MySQL is known as the most popular open source RDBMS which is widely used by both community and enterprise. However during the first decade of its existence, it did not support stored procedure, trigger, event…etc. Since MySQL version 5.0, those features have been added to MySQL database engine to make it become flexible and powerful.

Before starting the tutorial series about stored procedure, you should have MySQL version 5.x+ installed in your computer or server.

Stored Procedures Advantages

  • Stored procedures help increase the performance of application using them. Once created, stored procedures are compiled and stored in the database catalog. A stored procedure typically runs faster than uncompiled SQL statements that are sent from external applications.
  • Stored procedures reduce the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL statements, the application only has to send the stored procedure‚Äôs name with parameters.
  • Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so developers don‚Äôt have to develop functions that are already supported in stored procedure in those applications.
  • Stored procedures are secured. Database administrator can grant appropriate permission to application that access stored procedures in database catalog without giving any permission on the underlying database tables.

Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before deciding using store procedures.

Stored Procedures Disadvantages

  • Stored procedures make the database server high load in both memory and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations which is not well-designed for database server.
  • Stored procedures consist of declarative SQL so it is very difficult to write a procedure with complexity of business logic like other languages in application layer such as Java, C#, C++‚Ķ
  • Stored procedures are difficult to debug. Only few database mangement system supports debugging stored procedures. There are some workarounds on this problem but it still not easy enough.
  • Store procedures are not easy to develop and maintain. Developing and maintaining stored procedure are usually required specialized skill set that not all application developers possess. This may introduce problems in both application development and maintain phases.

Stored procedures have their own advantages and disadvantages. When you develop applications, you should decide whether to use stored procedure or not based on the requirements. In the following tutorials, we will show you how to leverage stored procedures in your database programming tasks with a couple of practical examples.

Getting Started with MySQL Stored Procedures

Summary: in this tutorial, you will learn how to write the first MySQL stored procedure using CREATE PROCEDURE statement. We will show you how to call a store procedure using CALL statement.

Writing the first MySQL stored procedure

We are going to develop a simple stored procedure named GetAllProducts() to help you get familiar with syntax. The function of this stored procedure is to retrieve all products from the products table.

First launch the mysql client tool and type the following commands:

12

3

4

5

6

DELIMITER //CREATE PROCEDURE GetAllProducts()

BEGIN

SELECT *  FROM products;

END //

DELIMITER ;

mysql-stored-procedure-command-linepic

Let’s examine the stored procedure in a greater detail:

  • The first command is DELIMITER //, which is not related to the stored procedure syntax. The DELIMITER statement changes the standard delimiter which is semicolon (;) to another. In this case, the delimiter is changed from the semicolon(;) to double-slashes //. Why do we have to change the delimiter? because we want to pass the¬† stored procedure to the server as a whole instead of lettingmysql¬†interprets each statement at a time when we type.¬† Following the END keyword, we use delimiter // to indicate the end of the stored procedure. The last command (DELIMITER;)¬†changes the delimiter back to the semicolon (;).
  • The CREATE PROCEDURE statement is used to create a new stored procedure. You can specify the name of stored procedure after the CREATE PROCEDURE statement. In this case, the name of the stored procedure is¬†GetAllProducts.¬†Do not forget the parenthesis after the name of the store procedure or you will get an error message.
  • Everything inside a pair of keyword BEGIN and END is called stored procedure‚Äôs body. You can put the declarative SQL code inside the stored procedure‚Äôs body to handle business logic. In the store procedure we used simple SQL SELECT statement to query data from the¬†products¬†table.

It’s kind of tedious to write the store procedure in mysql client tool especially when the stored procedure is complex. Most of the GUI tool for MySQL allows you to create new stored procedures using intuitive interface. For example, in MySQL Workbench, you can create a new stored procedure as follows:

create-mysql-stored-procedure-mysql-workbench-step-1

 

create-mysql-stored-procedure-mysql-workbench-step-2

 

Enter the stored procedure code and click the Apply button

create-mysql-stored-procedure-mysql-workbench-step-3

You can review the code before MySQL stores it in the database. Click Apply button if everything is good.

create-mysql-stored-procedure-mysql-workbench-step-4

MySQL compiles and puts the stored procedure in the database catalog; click the Finish button.

create-mysql-stored-procedure-mysql-workbench-step-5

You can see a new stored procedure created under Routines of the classicmodelsdatabase

We have created a new stored procedure. Now it’s time to learn how to call or invoke it.

Calling stored procedures

In order to invoke a stored procedure, you use the following SQL command:

?

1 CALL STORED_PROCEDURE_NAME()

You use CALL statement to call a stored procedure e.g., to invoke the GetAllProducts stored procedure, you use the following command:

?

1 CALL GetAllProducts();

If you execute the command above, you will get all products in the products table.

mysql-stored-procedure

 

In this tutorial, you have learned how to write a simple stored procedure using CREATE PROCEDURE statement and invoke it using the CALL statement.

References: http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

http://www.brainbell.com/tutorials/MySQL/Using_Stored_Procedures.htm

http://mysqlstoredprocedure.com/index.php?option=com_content&view=article&id=60:wrapper-procedure-to-load-specific-data&catid=34:select&Itemid=27

 

Call SQL Stored Procedure with Parameters through PHP

Hi, Lets consider a simple scenario like Username & Password of a basic form should call a stored procedure by passing this textboxes(username & password) values and then output is dispalyed.

Stored Procedure:

CREATE procedure sp_Employee1
(
@username VARCHAR(200),
@password VARCHAR(200)
)
AS
begin
if (select COUNT(*) from Employees where UserID=@username) > 0
select ‘success’ as statusmsg
else
select ‘failure’ as statusmsg
end

Description: This sp will check username from employee data and if it is valid then display success message, else failure message.

PHP File:

<html>
<form method=”post” action=”testsql2.php? run=1″>
<input type=”text” id=”un” name=”un”value=””>
<input type=”password” id=”ps” name=”ps” value=””>
<input type=”submit” value=”Submit” onsubmit=””></form>
</html>
<?php
header(“Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0”);
header(“Pragma: no-cache”);
$v1 = isset($_REQUEST[‘run’]) ? $_REQUEST[‘run’] : 0 ;
if($v1==1)
{

$a = $_POST[“un”];
$b = $_POST[“ps”];
}

$serverName = “serverName\instanceName”;

$connectionInfo = array(
‘Database’ => ‘Database’,
‘UID’ => ‘userid’,
‘PWD’ => ‘pwd’);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
echo “Connection established.<br />”;
}else{
echo “Connection could not be established.<br />”;
die( print_r( sqlsrv_errors(), true));
}

$query = “sp_Employee1 ‘$a’,’$b'”;
$params = array();
$result1 = sqlsrv_query($conn, $query, $params);
while ($result2 = sqlsrv_fetch_array($result1)){

echo $result2[statusmsg].”<br/>”;
}

Output: Show Success/Failure message based on your input(if emp username existed in database, it show success else failure).

Enjoy ūüôā