Topic: [SOLVED] extract your mysql db to excel using php?

[ballhogjoni]

How do you extract your mysql db to excel using php?

[obsidian]

Seeing as how Excel will read tab delimited, CSV and other delimited file types, the easiest way is to simply loop over your MySQL database table, echoing each record while implode()ing it with the delimiter of choice.

[ballhogjoni]

Thank you for your reply. Can you explain what you are saying with code? I do not know what the implode() function is.

[obsidian]

Try something like this for starters, and then you can modify it to whatever your circumstances may be:

Code: [Select]

<?php
// This page, if included, will require a download of the file you can then open in Excel
header("Content-type: application/text");
header("Content-Disposition: attachment; filename=my_export.txt");
$sql = mysql_query("SELECT * FROM my_table");
if (mysql_num_rows($sql) > 0) {
  while ($row = mysql_fetch_array($sql)) {
    echo implode("\t", $row) . "\n";
  }
}
exit();
?>

You can change the filename within the Content-Disposition header call to whatever you like. The implode() function, as I am using it, creates a string with all of the values of the row you just pulled from MySQL, joined with tabs (hence, creating a tab delimited file).

Hope this helps.

[ballhogjoni]

Can I do this for multiple tables in one db?

[ballhogjoni]

Its also duplicating the content.

[obsidian]

To answer your first question, you can do this with any SELECT query you can dream up. So, if you want to join tables and export them, no problem. Otherwise, you can create some sort of admin panel that allows for exporting of individual tables.

Its also duplicating the content.

Can you give some examples? I've just tried it with my tables, and it seems to work fine.

[ballhogjoni]

1   1   Chris   Chris   DJKD   DJKD   Owner   Owner   123@cool.com   123@cool.com   NE   NE   456   456   233   233   4561   4561   I agree   I agree
2   2   Mitch   Mitch   sdlfkj   sdlfkj   The Man   The Man   mitch@sdf.net   mitch@sdf.net   GA   GA   770   770   233   233   3808   3808   I agree   I agree


I copied this straight from the txt file.

[obsidian]

Ah, ok... you'll want to use mysql_fetch_row() instead of mysql_fetch_array()... my bad.

[ballhogjoni]

thanks that worked. Now how would I be able to echo multiple tables from my db?

[ballhogjoni]

I am actually playing around with this code:

Code: [Select]

<?php
$sep = "\t"; //tabbed character

//start of printing column names as names of MySQL fields
/* 
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
for ($i = 0; $i < mysql_num_fields($result1); $i++)
{
echo mysql_field_name($result1,$i) . "\t";
}
print("\n");
//end of printing column names
*/

//start while loop to get data
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";

print(trim($schema_insert));
print $sep."\n";
}
while($row = mysql_fetch_row($result1))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result1);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";

print(trim($schema_insert));
print $sep."\n";
}
}
mysql_close();
?>


[ballhogjoni]

anybody know this?

[obsidian]

thanks that worked. Now how would I be able to echo multiple tables from my db?

Can you be more specific with what you're after? Are you wanting to output a separate file for each table, or are you simply wanting to loop through several tables and output them all to one file? If it's the latter, do your tables have a similar structure, or are they completely different?

There is a lot more detail we need before we can help come up with a valid solution.

[ballhogjoni]

thanks obsidian,

Yes its the latter. As far as I know the tables are similiar. they have an ID that auto_increments and then some other rows.

[obsidian]

Since you're just wanting to loop through several tables and output them all to one file, just modify the output above to loop through a predefined array of tables like this:

Code: [Select]

<?php
// This page, if included, will require a download of the file you can then open in Excel
header("Content-type: application/text");
header("Content-Disposition: attachment; filename=my_export.txt");

// Define tables
$tabes = array('table01', 'table02', 'table03', 'table04');

foreach ($tables as $table) {
  $sql = mysql_query("SELECT * FROM $table");
  if (mysql_num_rows($sql) > 0) {
    while ($row = mysql_fetch_array($sql)) {
      echo implode("\t", $row) . "\n";
    }
  }
}

exit();
?>