In this article we will show you the solution of export to CSV in PHP, we used mysqli_fetch_assoc() function to export the CSV file.
By clicking on a button in the HTML file we will able to download it
mysql_fetch_assoc(): this function fetches a result from a database as an object.
Step By Step Guide On Export To CSV In PHP :-
In the example below, we created three php files to export to CSV in php. At first, let us see the database connection file in php
Database Connection File:
<?php
dbhost ="localhost" ;
$dbusername = "root" ;
$dbpassword = "" ;
$db = new mysqli($dbhost, $dbusername, $dbpassword) ;
if($db->connect_error) {
die("Failed!!".$db->connect_error) ;
}
?>
- To do the database configuration, declare the database host, username, password and name.
- For creating database connection using mysqli() function
- Now check the connection, create an if statement for if the connection show any kind of error display the error message.
PHP Code for Export.Php:
<?php
require ('connection.php') ;
$sql = "select * from student" ;
$res = mysqli_query ($con, $sql) ;
$html = '<table><tr><td>Name</td><td>City</td><td>Email</td></tr>' ;
while($row = mysqli_fetch_assoc($res) ) {
$html.= '<tr><td>'.$row['Name'].'</td><td>'.$row['city'].'</td><td>'.$row['Email'].'</td></tr>';
}
$html. = '</table>' ;
header('Content-Type: application/csv') ;
header('Content-Disposition:attachment; filename=report.csv') ;
echo $html ;
?>
- Using require () with the database’s php file
- Using SELECT with the database table name Student
- Now using mysqli_query() with the connection and the result
- $html for representing the table
- Now creating a while loop with $row equal to mysqli_Fetch_assoc() with $res. Inside it $html. With the table data
- Close the table tag.
- Declaring header files with the content-type to application/csv and the content-disposition to attachment and set the filename to report.csv
Now let us see the code for HTML table as the table in the database
PHP Code:
<?php
include_once("connection.php")
?>
<!DOCTYPE html>
<html lang = " en " >
<head>
<meta charset = " UTF - 8" >
<meta http-equiv = " X-UA-Compatible " content = " IE=edge " >
<meta name = " viewport " content = " width = device-width , initial-scale = 1.0 " >
<title> export to csv in php </title>
<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<h1 style=" color : rgb(113, 221, 113) ;"> TALKERSCODE </h1>
<h2> export to csv in php </h2>
<<div class="container">
<h2> List </h2>
<div class="row">
<!-- export link -->
<div class="col-mid-12 head">
<div class="float-right">
<a href="export.php" class="btn btn-success"><i class="dwn"></i>Export</a>
</div>
</div>
<!-- data list table -->
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>name</th>
<th>city</th>
<th>email</th>
</tr>
</thead>
<tbody>
<?php
//fetch records from database
$result = $db->query["SELECT * FROM student"] ;
if($result->num_rows>0) {
while($row = $result->fetch_assoc()){
?>
<tr>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['city']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo ($row['status']==1)?'active':'Inactive'; ?></td>
</tr>
<?php
}
} else{
?>
<tr><td colspan="4">No members found..</td></tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</body>
</html>
- Load the database configuration file using the php tag
- First, we write <! DOCTYPE html> which we used as the instruction to the web browser about what version of HTML file is written in.
- Secondly, the <html> tag is used to indicate the beginning of an HTML document.
- As mentioned above, the <head> tag contains information about the web page. In this tag, a <title> tag is used which helps us to specify a webpage title.
- Both <head> and <title> tags are Paired tags. So, both have </head> and </title> ending tags respectively.
- Adding external CSS stylesheet into the <head> tag
- Thirdly, the <body> tag is used to define the webpage body. All the contents to show on the website are written here.
- <h1> tag used to add heading here and also adding the inline CSS here.
- Creating an export link, by clicking this we will able to export the csv file
- Now creating the data list table by <table> with <th> of name, city and email.
- Opening the <tbody> tag , within it open a <?php tag
- $result with SELECT method to select the exact table named Student.
- Now create an if statement, for if the number of rows is greater than zero then run a while loop with the fetch_assoc() method.
- To display the table data we used <td> with inline php echo $row[ ]
- Now click on the export button to download the CSV file.
Conclusion :-
At last, here in conclusion, here we can say that with this article’s help, we know how to export to csv in php.
I hope this article on export to CSV in PHP helps you and the steps and method mentioned above are easy to follow and implement.
About Riya
