Member Avatar for kingwash

//exporting to csv file.
// this is my code.

<?php

// Database Connection

$host="localhost";
$uname="root";
$pass="";
$database = "db_name";

$connection=mysql_connect($host,$uname,$pass); 

echo mysql_error();

//or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or die("Database could not be selected");  
$result=mysql_select_db($database)
or die("database cannot be selected <br>");


// Fetch Record from Database

$output         = "";
$table          = "information"; // Enter Your Table Name
$sql            = mysql_query("select * from $table");
$columns_total  = mysql_num_fields($sql);

// Get The Field Name

for ($i = 0; $i < $columns_total; $i++) {
    $heading    =   mysql_field_name($sql, $i);
    $output     .= '"'.$heading.'",';
}
$output .="\n";

// Get Records from the table

while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="\n";
}

// Download the file

$filename =  "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo $output;
exit;

?>

This is working very well because it is exporting a single table to a csv file.
My problem is, i want it to export from multiple tables to a single csv file.
Please, how do i do it?
Thanks in advance.

Edited by kingwash because: correction
  • 4 Contributors
  • 3 Replies
  • 3K Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by chaitu11

Dani AI

Generated

If you need one CSV from more than one table, decide first how the tables relate. As noted: use a JOIN when one table enriches rows from another (one row per entity), and use UNION/UNION ALL when you are stacking rows from tables with the same columns. is right that you control new lines and separators, but letting PHP handle quoting is safer than manually concatenating strings.

Here is a compact, stream-to-browser approach using PDO and fputcsv (works well for large exports and avoids memory bloat). Swap the $sql for JOIN vs UNION as needed.

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=db_name;charset=utf8mb4',
    'user', 'pass',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
     PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]
);

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=export.csv');
$out = fopen('php://output', 'w');

// Example A: JOIN (one row per information record with details)
$sql = 'SELECT i.id, i.name, d.value
        FROM information i
        LEFT JOIN details d ON d.info_id = i.id
        ORDER BY i.id';

// Example B: UNION ALL (same columns from multiple tables)
// $sql = 'SELECT "information" AS src, id, name FROM information
//         UNION ALL
//         SELECT "archive",     id, name FROM information_archive
//         ORDER BY id';

$stmt = $pdo->query($sql);
$first = $stmt->fetch(PDO::FETCH_ASSOC);
if ($first) {
    fputcsv($out, array_keys($first));   // header row
    fputcsv($out, $first);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        fputcsv($out, $row);
    }
}
fclose($out);

Practical tips:

  • If schemas differ, alias/NULL missing columns so each SELECT returns the same column list when using UNION ALL.
  • Keep a single header row; for UNION you generally take column names from the first SELECT.
  • Avoid mysql_* in modern PHP; use PDO or mysqli and prepared statements.
  • ’s DataTables suggestion is great for client-side viewing, but server-side streaming like above is better for big downloads.

Recommended Answers

All 3 Replies

Member Avatar for AndrisP

JOIN tables, if the second table supplements the entries from the first table or UNION tables if the tables have the same structure

Member Avatar for arunmagar

Yes you can do it. Just you need to decide where to place seprator(here COMMA in your case) and New Line character "\n".
or ofcourse as AndrisP suggested above you can do it using JOIN in a single query.

Member Avatar for chaitu11

Use this plugin

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.