How to Export MySQL Table to Excel in PHP

We will see how to export the table in MySQL into excel using PHP in this article.

Export MySQL Table to Excel in PHP

We can use the excel headers in PHP to import the table in MySQL into an excel file. We should specify the Content-type header as application/xls to include the excel header. To download the excel file, we should use the Content-Disposition header as an attachment and provide the filename with the filename option. We can query the required table in PHP and save the result in a variable. The variable should be an HTML table containing the queried results. Then, we can send the header information to the browser using the header() function and output the variable containing the table using the echo function. We will break down the steps to export a MySQL table into an excel file.

For instance, we have a table named users in our database.

+----+-----------+----------+------------+
| id | firstname | lastname | dob        |
+----+-----------+----------+------------+
|  1 | james     | gunn     | 1998-08-13 |
|  2 | bille     | joe      | 1970-02-15 |
+----+-----------+----------+------------+

Now, we will export the table into an excel file. At first, let’s create a button to export the table into excel.

<form method="post" action="index.php">
<input type="submit" name="submit" value="Export" />
</form>

We created a form that submits the data to the index.php file when the button is clicked. The name attribute of our button is submit. We will use this value later to check if the data has been submitted in the form.

At first, we have to establish a connection to the database. For that, use the mysqli_connect() function and provide your server details. Save the connection in the $connect variable.

$connect = mysqli_connect("hostname", "username", "password", "db_name");

Note that the values written as the parameters to the function above are merely placeholders. You should fill it with your server information.

Next, we can use the isset() function to check if the form has been submitted. As the method attribute is post in the form that we created, we should use the $_POST array to check. We use the name attribute’s value, submit as the index of the $_POST array inside the isset() function.

After that, write the SQL query and save the output in the $res variable. The query to select all the rows from the users table is below.

SELECT * FROM users;

Then, run the query using the mysqli_query() function. The database connection variable is the first parameter in the function, and the MySQL query is the second parameter. We can check the query result using the mysqli_num_rows() function. The function returns the number of rows from the database. The number of rows must be greater than zero to execute further operations. Write the if condition to evaluate the condition.

Inside the if condition, create a variable $export and save the HTML table in it. Create the table headers with the th tag. Create the headers with the same name as the column names in the table. After that, fetch the data from the database using the mysqli_fetch_array() function. Assign the function to a $row variable.

Next, use the $row variable to extract the data using the column name as the index. Wrap the variable inside the td tag so that the data will be populated in the table. An example is shown below.

<td>'.$row["id"].'</td>

Next, close all the table tags. The $export variable contains the table as a string. Next, write the header() function to denote the content sent to the browser as an excel file. Again, use the function to download the excel file. The example is shown below.

 header('Content-Type: application/xls');
 header('Content-Disposition: attachment; filename=info.xls');

Finally, print the $export variable using the echo function. Note that all the PHP part is done in the index.php file.

The final code example is shown below.

Example Code:

$connect = mysqli_connect("hostname", "username", "password", "db_name");
if(isset($_POST["submit"]))
{
 $query = "SELECT * FROM users";
 $res = mysqli_query($connect, $query);
 if(mysqli_num_rows($res) > 0)
 {
 $export .= '
 <table> 
 <tr> 
 <th> id </th>
 <th>firstname</th> 
 <th>lastname</th> 
 <th>dob</th> 
 
 </tr>
 ';
 while($row = mysqli_fetch_array($res))
 {
 $export .= '
 <tr>
 <td>'.$row["id"].'</td> 
 <td>'.$row["firstname"].'</td> 
 <td>'.$row["lastname"].'</td> 
 <td>'.$row["dob"].'</td> 
 
 
 </tr>
 ';
 }
 $export .= '</table>';
 header('Content-Type: application/xls');
 header('Content-Disposition: attachment; filename=info.xls');
 echo $export;
 }
}

When we click the Export button, we will be able to download the excel file, which contains the contents of the database. In this way, we can export the MySQL table into an excel file in PHP.