export MySQL database to csv using php
 |
| mysql export to csv |
Database:
our database name is codenair..it's contain some tables ..i use php and html select ,option method to display all tables that's contains in codenair..
Now create and copy the below codes for different's files to export database table to csv..
1. connect.php
<?php
$host='localhost';
$username='root';
$password='';
$dbname='codenair';
$conn=new mysqli($host,$username,$password,$dbname);
if($conn->connect_error){
echo ("Connection Failed:".$conn->connect_error);
}
?>
2. index.php
create index page to show all tables from selected database and export button..
<?php
include('connect.php');
//show all tables from selected database
$result = mysqli_query($conn,"SHOW TABLES FROM $dbname");
?>
<html>
<head>
<title></title>
<style>
select[type=text]{
padding:5px 0px 5px 5px;
border-radius:5px;
cursor:pointer;
font-family:verdana;
}
input[type=submit]{
background:black;
color:white;
padding:6px 8px;
font-weight:bold;
cursor:pointer;
border-radius:5px;
}
</style>
</head>
<form method="POST" action="export.php">
<table>
<tr>
<td><b><?php echo $dbname;?></b> Database Table List's</td>
<td>
<select type="text" name="table">
<?php
while ($row = $result->fetch_array()) {
?>
<option value="<?php echo $row[0];?>"><?php echo $row[0];?></option>
<?php
}?>
</select>
</td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="export" value="Export Table"/></td>
</tr>
</table>
</form>
3. export.php
Create export file to generate csv file from database table and force browser to download..
<?php
include('connect.php');
$table=$_POST['table'];
//force browser to download file
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename="'.$table.'".csv');
//select table to export rows
$result=mysqli_query($conn,"select * from $table");
$rows = $result->fetch_assoc();
if ($rows){
getcsv(array_keys($rows));
}
while($rows){
getcsv($rows);
$rows = $result->fetch_assoc();
}
// get total number of fields present in the table
function getcsv($totalfields)
{
$separate = '';
// do the action for all field names as field name
foreach ($totalfields as $field_name)
{
if (preg_match('/\\r|\\n|,|"/', $field_name))
{
$field_name = '' . str_replace(',',' ', $field_name) . '';
}
echo $separate . $field_name;
//separate column with the comma
$separate = ',';
}
//make new row and line
echo "\r\n";
}
?>
That's it friends how to export mysql database table to csv using php..if you like this post please share with your friends..Thank You..
No comments:
Post a Comment
Thank You for Your Comment
Note: Only a member of this blog may post a comment.