Backup MySQL data into CSV with PHP [Part-3]

In the previous article we discuss how to “Backup MySQL data into CSV” in terminal. In this post we will discuss how to create csv file with PHP and force it to download. We will be pulling data from MySQL database. 1st of all we will need connection to database, for that we will use this script. Change the necessary details and save it as conn.php. Now lets start with the csv.

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
<?php
require_once ‘conn.php’;

$sql = $dbh->prepare(“SELECT u.name, u.email, u.phone, u.city, s.filename FROM_UNIXTIME(s.sub_time, ‘%M %D %Y – %l:%i %p’) from users u join submissions s on u.uid=s.uid where s.uid != ” and s.filename != ” order by s.sub_time desc”);
$sql->execute();
$row = $sql->rowCount();

if ($row > 0){

$columns = array(‘NAME’,’EMAIL’,’PHONE’,’CITY’,’FILENAME’,’TIME’);

$filep = fopen(‘php://output’, ‘w’);
if ($filep) {
$file = uniqid().’_’.time().’.csv’;
header(“Content-Type: text/csv”);
header(“Content-Disposition: attachment; filename=$file”);
header(“Pragma: no-cache”);
header(“Expires: 0”);
fputcsv($filep, $columns);

while ($rows = $sql->fetch(MYSQL_NUM)) {
fputcsv($filep, $rows);
}
die;
}
}
?>
[/cc]
The script is quite straight forward and simple to use/follow. Little bit explanation: we are joining two tables here and then checking if any record found with rowCount function, if so we proceed to create csv. The array store the headers which will be column headers in the csv file. Giving a unique name to the file we will download and then using PHP fputcsv function to write data, quite easy and painless.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-2: Backup MySQL data into CSV

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

Backup MySQL data into CSV [Part-2]

In my last article i showed you how to “Setup web server on Amazon AMI or CentOS“, to continue that article further i will discuss how to backup MySQL data into CSV format. So let’s start, login into your machine through terminal and login to MySQL server and choose database by command ‘use databasename‘.  Let’s dump a table to csv:

SELECT * INTO OUTFILE ‘/var/www/html/users.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ FROM users;

The above command will output all the columns and data into a csv file which will be saved in /var/www/html, change this according to your need.

Now let’s say we have multiple tables and they are connected with each other through keys. For example user table with votes table.

SELECT ‘Name’, ‘Email’, ‘Time’,’Phone’,’City’ UNION SELECT u.name, u.email, FROM_UNIXTIME(v.vote_time, ‘%M %D %Y – %l:%i %p’) AS timestamp, u.phone, u.city INTO OUTFILE ‘/var/www/html/users_votes.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ from users u join votes v on u.uid=v.uid;

We are joining two tables here on uid, which is user id. Name,Time,Phone,Email,City will be the column headers in CSV for each column selected from database. vote_time i assume is stored in db as unix timestamp. That is quite straight and simple, nothing fancy there.

Of course you can join many tables to output your desire data. That’s it for now. In the next article i will show you how to save/download data in CSV format/file using PHP and MySQL with fputcsv function. Stay tune.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-3: Backup MySQL data into CSV with PHP

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

How to insert csv file data into MySQL database

MySQL have this load data function, where we can insert data to a table from external files like csv, txt etc.
The process is quite simple, 1st lets login to mysql via terminal/console.

mysql -u root -p

Enter your MySQL password. Change root to any username(MySQL users) you would wish to login to MySQL.

mysql>

Now lets run the command.

mysql> LOAD DATA LOCAL INFILE ‘/var/www/site/site_users.csv’
INTO TABLE site_users FIELDS
TERMINATED BY ‘;’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES;

You will see this if command ran successfully:

Query OK, 370 rows affected (0.00 sec)
Records: 370  Deleted: 0  Skipped: 0  Warnings: 0

In the 1st line, we load a file from local dir and that is the reason we used ‘LOCAL’.
2nd line will insert the data into site_users table.
In the 3rd line we tell the command to break the fields by ; . You can check your csv file in txt format that whether its ; or comma(,).
4th line means that data is enclosed with the double quotes.
And lines are terminated by \n.
Last line will ignore the 1st line, which usually represents the data headers.

One thing i would like to mention here, make sure that the table fields and csv columns sequence matches.
Like name, address, phone are in the table, csv will also be name, address, phone. Which means data will be inserted into the right field.
And yeh column name can be changed, that does not matter like in table first_name and in csv name. What i mean to say here is that the sequence of fields need to match that right data goes to the right field.

That’s it.