get the Next Auto Increment number in Mysql

$query = mysql_query("SELECT MAX(id) FROM `table`");
$results = mysql_fetch_array($query);
echo $results["Auto_increment"];

Export Database Data to .XLS in PHP


$DB_Server = "localhost";        //your MySQL Server
$DB_Username = "username";         //your MySQL User Name
$DB_Password = "password";        //your MySQL Password
$DB_DBName = "databasename";            //your MySQL Database Name


 $DB_TBLName = "users";           //your MySQL Table Name
$ttype = $DB_TBLName;

 $sql = "Select * from $DB_TBLName"; 

$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
    or die("Couldn't connect to MySQL:
" . mysql_error() . "
" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
    or die("Couldn't select database:
" . mysql_error(). "
" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
    or die("Couldn't execute query:
" . mysql_error(). "
" . mysql_errno());


    while($row = mysql_fetch_row($result))
    {
           $data = $row;

    }


function cleanData(&$str) {
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"'))
$str = '"' . str_replace('"', '""', $str) . '"';
}

# filename for download $filename = "website_data_" . date('Ymd') . ".xls";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$flag = false;
foreach($data as $row) {
if(!$flag) { # display field/column names as first row echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\n";
}
exit;


Restoring from a Database Backup (with GZip Compression)

So you’ve got a backup of your database (either using the method above, or some other way), and something has gone wrong and you need to restore, or you’re migrating it to a new server. You could use one of the other tools mentioned before, but in the example of phpMyAdmin, what if your database backup file is bigger than the allowed upload size? Well luckily, the command-line doesn’t mind.
The command to restore is very similar to the one for backing up. Firstly, without GZip compression:

1. cat db_backup.sql | mysql -u mysqluser -p mysqldatabase  

cat db_backup.sql | mysql -u mysqluser -p mysqldatabase

We use the “cat” command to output the contents of the backup script, and pipe its contents into the mysql program. As you can see, the mysql program takes the same options as the mysqldump one does in section two.
Now if the script was GZip compressed, we can’t just output its contents into mysql, as it will be compressed data instead of a nice SQL script. So we do the following:

1. gunzip < db_backup.sql.gz | mysql -u mysqluser -p mysqldatabase  

gunzip < db_backup.sql.gz | mysql -u mysqluser -p mysqldatabase

See, it's very familiar, just switched around a bit.

mysql - Reset Root User Privileges

1. Find the my.ini file (my.cnf for linux). On windows, the my.ini file is typically in your install directory: c:\Program Files\MySQL\MySQL Server 5.1\my.ini
OR if your using xamp then it will be C:\xampp\mysql\bin\my.ini
Open the file with notepad (or any text editor) and add the following entry under the [mysqld] section:

skip-grant-tables

Then, save the file and restart the MySQL service.

If you have Phpmyadmin then select the mysql databese and open the user table. In that you will find the all mysql users.

Then you need to edit the root row and update all the radio buttons with Y.



If you dont have Phpmyadmin then
mysql > use mysql;
Delete the old root user like
mysql > delete from user where user='root';

Create root user again (WITH RIGHTS!)

mysql > INSERT INTO user (Host, User, Password, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv) VALUES ('localhost', 'root', PASSWORD('thepass'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

OR
you can update the above instead of re creating the user account.


After that remove the skip-grant-tables from the my.ini file and restart the Mysql. thats it.

Backup Mysql Database From Commandline

 Backing up your database is something you should do often. Like most things, there are a lot of ways to do this, but using the command-line is one of the best. Why? Because it helps you get around potential problems like execution timeouts for tools like phpMyAdmin, and potential network dropouts from using a local administration tool like MySQL Workbench.



mysqldump -h localhost -u username -p database_name > backup_db.sql

take a backup in compress mode:

mysqldump -u username -h localhost -p database_name | gzip -9 > backup_db.sql.gz

(OR) 

mysqldump -u username -p database_name | gzip -c backup.sql.gz 

Now, to explain what’s going on here! The “mysqldump” program is a tool for creating database backups. The parameters being used are:

• “-u” switch means you’re going to specify a username to connect with, which must follow, like “-u mysqluser” above
• “-p” switch means you’re either going to immediately specify the password to use (with no space), or it’ll prompt you for one
• The final parameter used in the example above is the name of the database to backup (of course!)

If you ran the command above, you would’ve seen the contents of your database go whizzing by on the screen. That’s good, because we know that part works (actually connecting to the database), but it’s also bad, because… where did it go? Answer: nowhere! It scrolled past, and that was it. Now we need to capture it and put it in a file.
To place the contents of the output into a file, for back-up purposes, we need to use what’s called a redirection.


 

Mysql : Get Auto increment value

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_name = 'club_photogallery'

Left Joins

The left join is a mechanism used to join tables before we add other conditions such as WHERE

SELECT
FROM
LEFT JOIN
ON Table1.column = Table2.column

Mysql: Exporting and Importing Dat

mysql> --local-infile=1

'''Exporting Data'''

SELECT
FROM
INTO OUTFILE '/tmp/vworks/sample.txt'

'''Importing data'''


LOAD DATA INFILE 'file_name.txt'
INTO TABLE tbl_name (field1, field2...etc)

Mysql : Get UnMatched Records from table1

SELECT * FROM table1 t1 WHERE t1.id NOT IN(select t2.t1_id FROM table2 t2)