Export MySQL Data in CSV File Through External URL

As you know, many of your clients need MySQL data stored on a daily, weekly or monthly basis from you. But if there is no option to share this data through web application or any other medium, then it will be your task to provide this data as per clients requirement in specific time. Now as a developer you will access PHPMyAdmin and export the selected table data or will use any other application to export data from MySQL server, and every time it will be a time consuming task. So just to reduce this time and to make it easy for you and your client, I tried to write one small script in PHP, which will export any table data by just passing database table name through URL query string. You can share this URL with clients so that they can export data. Considering security point of view, it may be a risk but you can decide on this as per your requirements.

The PHP script is as follows:

<?php 
// set execution time limit if there is huge data in database set_time_limit (180); 
define('DB_HOST', ''); 
define('DB_USER', ''); 
define('DB_PASS', ''); 
define('DB_NAME', ''); 
$table_name = trim($_GET['tbl']); 
// connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASS); $data = ""; 
$errmsg = "Please wait..."; 
// temporary folder path to store the file $path = sys_get_temp_dir(); 
// export file name $file = '/data.csv'; 
if 
(!$link) 
{ 
$errmsg = "Invalid Server Information";
 } 
else 
{ 
// connect to mysql database $db_select = mysql_select_db(DB_NAME, $link); 
if (!$db_select) 
{ 
$errmsg = "Invalid Database Information"; 
} 
else 
{ 
if 
($table_name == "") 
{ 
$errmsg = "Invalid URL"; 
} 
else 
{ 
$sql = "SELECT * FROM 
$table_name"; 
// select query $res = mysql_query($sql); 
if 
($res) 
{ 
$cnt_fld = mysql_num_rows($res); 
if 
($cnt_fld > 0) 
{ 
for($i=0;$i<mysql_num_fields($res);$i++) 
{ 
// get field names from selected table $data .= ucfirst(mysql_field_name($res,$i)).','; 
} 
$data = substr($data,0,-1); 
$data .= "\n"; 
// fetch data from selected table while($row = mysql_fetch_assoc($res)) 
{ 
for($j=0;$j<mysql_num_fields($res);$j++) 
{ 
$data .= '"'.html_entity_decode(strip_tags(str_replace (",","",$row[mysql_field_name($res,$j)]))).'"'.','; 
} 
$data = substr($data,0,-1); 
$data .= "\n"; 
} 
} 
else 
{ $errmsg = "Data not found"; 
} 
// create csv file on server and write data in it 
// open the file $hd = fopen($path.$file,"w+"); 
if 
(!$hd) 
{ 
echo "Error: Cannot open file ($file)"; exit; 
} 
// write data in file if (fwrite($hd, $data) === FALSE) 
{ 
echo "Error: Cannot write to file ($file)"; exit; 
} 
else 
{ 
// headers to download csv file header("Content-type: octet/stream"); header("Content-disposition: attachment; filename=".basename($path.$file)); 
header("Content-Length: ".filesize($path.$file)); readfile($path.$file); exit; 
} 
} 
else 
{ 
$errmsg = "Data not found"; 
} 
}
 } 
} 
echo "<br />".$errmsg."<br />"; 
?>

Just copy the above script, add your MySQL server information in the script and upload file on live server.

Now, call this page from web browser and pass table name through query string to “tbl” variable. So your external URL will be something like this:

URL: http://www.example.com/script.php?tbl=tablename

For security reasons you can encode and decode the tablename in some format like base64 or any other encryption script. Also don’t share this URL with any unknown person.

You can customize this code as per your requirements.

This entry was posted in Database, MySQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply