Mysql

export mysql database – mysql dump

When you have access to the command line and you have the export large database, you will not be able to export from phpmyadmin.

Hence mysql provide you handy method to export the database from the command line. Below are various options, you can use while exporting the database.

–databases – This allows you to specify the databases that you want to backup. You can also specify certain tables that you want to backup. If you want to do a full backup of all of the databases, then leave out this option
–add-drop-database – This will insert a DROP DATABASE statement before each CREATE DATABASE statement. This is useful if you need to import the data to an existing MySQL instance where you want to overwrite the existing data. You can also use this to import your backup onto a new MySQL instance, and it will create the databases and tables for you.
–triggers – this will include the triggers for each dumped table
–routines – this will include the stored routines (procedures and functions) from the dumped databases
–events – this will include any events from the dumped databases
–set-gtid-purged=OFF – since I am using replication on this database (it is the master), I like to include this in case I want to create a new slave using the data that I have dumped. This option enables control over global transaction identifiers (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.
–user – The MySQL user name you want to use
–password – Again, you can add the actual value of the password (ex. –password=mypassword), but it is less secure than typing in the password manually. This is useful for when you want to put the backup in a script, in cron or in Windows Task Scheduler.
–single-transaction – Since I am using InnoDB tables, I will want to use this option.
–no-create-db – don’t create database
–no-create-info – don’t create table structure
–extended-insert=FALSE => Each row as seperate insert querry

Syntax : mysqldump –triggers –routines –databases databasename –user=root –password > database_export_file_name.sql
Example : mysqldump –no-create-db –triggers –extended-insert=FALSE –routines –databases mydatabase –user=root –password >mydatabase.sql

how to get sql query with data binding from facade DB::table in laravel 5

We can get the SQL query from DB sql query in Laravel 5

$dataQuery = DB::table(‘sometable’)
->select(‘sometable.column1′, ‘sometable.column2′, ‘sometable.column3′, ‘sometable.column4′, ‘sometable.column5′);

$dataQuery->toSql(); // this will give you the actual query but this will not give the result with actual data values.

Now What ??

To get the Data binded with the sql statements , you should use the below function

$dataQuery->getBindings(); // This will return an array of data binded to the sql statement.

Now to get the final sql statement, you can use the below function and pass $dataQuery->toSql() and $dataQuery->getBindings() to the functions written below , which you can define it in your helper class or common functions class file.

function getSqlWithBinding($sql,$bindDataArr){
foreach($bindDataArr as $binding)
{
$value = is_numeric($binding) ? $binding : “‘”.$binding.”‘”;
$sql = preg_replace(‘/\?/’, $value, $sql, 1);
}
return $sql;
}

$sqlwithData = getSqlWithBinding($dataQuery->toSql(),$dataQuery->getBindings());
or something like below if you have defined getSqlWithBinding function in common class.

$sql = CommonFunctionClass::getSqlWithBinding($dataQuery->toSql(),$dataQuery->getBindings());

select multiple column from multiple table in cakePHP

$markers = $this->Post->find(‘all’, array( ‘joins’ => array(
array(
‘table’ => ‘cp_user’,
‘alias’ => ‘users’,
‘type’ => ‘left’,
‘foreignKey’ => false,
‘conditions’=> array(‘Post.postauthor = users.id’)
)
),
‘conditions’=>’Post.poststatus = 1’,
‘fields’=>array(‘Post.postid’,
‘Post.postdate’,
‘Post.posttitle’,
‘Post.postmessage’,
‘Post.poststatus’,
‘Post.commentstatus’,
‘Post.commenttotal’,
‘Users.id’,
‘Users.firstname’
)
)
);

$this->set(‘cp_post’,$markers);

This will generate the below query in mysql
—————————————————-
SELECT `Post`.`postid`, `Post`.`postdate`, `Post`.`posttitle`, `Post`.`postmessage`, `Post`.`poststatus`, `Post`.`commentstatus`, `Post`.`commenttotal`, `Users`.`id`, `Users`.`firstname` FROM `cp_posts` AS `Post` left JOIN cp_user AS `users` ON (`Post`.`postauthor` = `users`.`id`) WHERE `Post`.`poststatus` = 1

How to fetch multi-lingual content from database

The basic purpose of this post is to guide you all about fetching multilingual content from database.
I mean to say that, if you have stored japanese text in your database, So in order to fetch the japanese content , you need to set the character encoding to UTF-8 format, while communication with the database for fetching the result.

In Case of MYSQLi

query(“SELECT ColumnName FROM tableName “)) {
printf(“Select returned %d rows.\n”, $result->num_rows);

/* free result set */
$result->close();
}
$mysqli->close();
?>

In case you are using multiple database

If you don’t pass in “true” to mysqli_connect() in the example below, $link1 and $link2 will have the same resource id# and both database connections will end up being set to utf-8 charsets.

How to import data into mysql db from sql file

Open your shell prompt

navigate to the mysql directory (default is /var/lib/mysql)
cd /pathToYourMysqlDirectory/

then type the below command

$ mysql -u mysqlUserName -p Password -h HostName DatabaseName < /loactionOfYourSqlFile/yourSqlFileName.sql

Where
mysqlUserName = Mysql User Name
Password = Mysql User Password
HostName = Mysql Host Name (This could be local host or some remote IP where you want to import the database)
DatabaseName = Mysql Database Name
/loactionOfYourSqlFile/yourSqlFileName.sql = This is the location of your sql file

Note : This is work only in Linux system

Thanks