php export sql to csv code example
Example 1: download csv php mysql
CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`username` varchar(80) NOT NULL,
`name` varchar(50) NOT NULL,
`gender` varchar(10) NOT NULL,
`email` varchar(70) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
=========================================================
// this config.php
<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "tutorial";
$con = mysqli_connect($host, $user, $password,$dbname);
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
=================================================================
<?php
include "config.php";
?>
<div class="container">
<form method='post' action='download.php'>
<input type='submit' value='Export' name='Export'>
<table border='1' style='border-collapse:collapse;'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Name</th>
<th>Gender</th>
<th>Email</th>
</tr>
<?php
$query = "SELECT * FROM users ORDER BY id asc";
$result = mysqli_query($con,$query);
$user_arr = array();
while($row = mysqli_fetch_array($result)){
$id = $row['id'];
$uname = $row['username'];
$name = $row['name'];
$gender = $row['gender'];
$email = $row['email'];
$user_arr[] = array($id,$uname,$name,$gender,$email);
?>
<tr>
<td><?php echo $id; ?></td>
<td><?php echo $uname; ?></td>
<td><?php echo $name; ?></td>
<td><?php echo $gender; ?></td>
<td><?php echo $email; ?></td>
</tr>
<?php
}
?>
</table>
<?php
$serialize_user_arr = serialize($user_arr);
?>
<textarea name='export_data' style='display: none;'><?php echo $serialize_user_arr; ?></textarea>
</form>
</div>
=============================================================
//Create a new download.php file -- code below//
<?php
$filename = 'users.csv';
$export_data = unserialize($_POST['export_data']);
$file = fopen($filename,"w");
foreach ($export_data as $line){
fputcsv($file,$line);
}
fclose($file);
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=".$filename);
header("Content-Type: application/csv; ");
readfile($filename);
unlink($filename);
exit();
Example 2: how to export php mysql data to csv through php
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "test";
$con = new mysqli($host, $user, $password, $database);
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>
Example 3: how to export php mysql data to csv through php
<?php
require("db_connection.php");
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
exit(mysqli_error($con));
}
if (mysqli_num_rows($result) > 0) {
$number = 1;
$users = '<table class="table table-bordered">
<tr>
<th>No.</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
';
while ($row = mysqli_fetch_assoc($result)) {
$users .= '<tr>
<td>'.$number.'</td>
<td>'.$row['first_name'].'</td>
<td>'.$row['last_name'].'</td>
<td>'.$row['email'].'</td>
</tr>';
$number++;
}
$users .= '</table>';
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Export Data from MySQL to CSV Tutorial | iTech Empires</title>
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Export Data from MySQL to CSV</h2>
</div>
</div>
<div class="form-group">
<?php echo $users ?>
</div>
<div class="form-group">
<button onclick="Export()" class="btn btn-primary">Export to CSV File</button>
</div>
<script>
function Export()
{
var conf = confirm("Export users to CSV?");
if(conf == true)
{
window.open("export.php", '_blank');
}
}
</script>
</div>
</body>
</html>