export mysql database table to csv using php - Web Development and Web Design Codes

Latest

Saturday, April 21, 2018

export mysql database table to csv using php

export MySQL database to csv using php

mysql export to csv
mysql export to csv
Hi Friends in this lesson we are going to learn how to export mysql database table to csv using php.. using this code you can download any table from your database in csv format..if you don't know how to export mysql data to csv?? please follow the below step to create mysql export to csv script using php..

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