Use mysqli_result to implement export and printing of tables

M66 2025-05-30

First, connect to the MySQL database and query. Suppose there is already a table in the database called users , including fields such as id , name , and email .

 <?php
// Database connection settings
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query data
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
?>

2. Export data as CSV file

Next, we use PHP to export the query results as a CSV file. The data in the mysqli_result object is extracted line by line and written to the CSV file.

 <?php
// If the query result exists
if ($result->num_rows > 0) {
    // Set file name
    $filename = "users_data_" . date("Ymd_His") . ".csv";

    // Set the response header，Tell the browser to download the file
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    // Open the output stream
    $output = fopen('php://output', 'w');

    // Write to the header
    $header = ['ID', 'Name', 'Email'];
    fputcsv($output, $header);

    // Write data rows
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, $row);
    }

    // Turn off the output stream
    fclose($output);
}
?>

3. Export data as an Excel file

Although it is a little more complicated to generate Excel files directly, it can be achieved through the third-party library PHPExcel or PhpSpreadsheet . These two libraries provide rich functionality to export query results into files in .xls or .xlsx format.

Here is a basic example of exporting Excel. First, you need to install PhpSpreadsheet :

 composer require phpoffice/phpspreadsheet

Then use the following code in PHP to export the data:

 <?php
// Introduced PhpSpreadsheet Class Library
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Setting the table header
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');

// Fill in data
$rowNum = 2;
while ($row = $result->fetch_assoc()) {
    $sheet->setCellValue('A' . $rowNum, $row['id']);
    $sheet->setCellValue('B' . $rowNum, $row['name']);
    $sheet->setCellValue('C' . $rowNum, $row['email']);
    $rowNum++;
}

// Set file name
$filename = "users_data_" . date("Ymd_His") . ".xlsx";

// Set the response header，Tell the browser to download the file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

// create Writer Object
$writer = new Xlsx($spreadsheet);

// Output file
$writer->save('php://output');
?>

4. Print data table

If you want the data table to be presented to the user for printing, you can output the query results as HTML tables. Users can print directly using the browser's printing function.

 <?php
// If the query result exists
if ($result->num_rows > 0) {
    echo '<table border="1">';
    echo '<thead>';
    echo '<tr><th>ID</th><th>Name</th><th>Email</th></tr>';
    echo '</thead>';
    echo '<tbody>';

    // Output data line
    while ($row = $result->fetch_assoc()) {
        echo '<tr>';
        echo '<td>' . $row['id'] . '</td>';
        echo '<td>' . $row['name'] . '</td>';
        echo '<td>' . $row['email'] . '</td>';
        echo '</tr>';
    }

    echo '</tbody>';
    echo '</table>';
    
    // Add print button
    echo '<br><button onclick="window.print()">Print the form</button>';
}
?>

In this way, the user can click the print button to print the page content in a table form.

mysqli_result