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.