Here is a bit of PHP scripting hocus pocus that will be very useful to some people and very useless to the vast majority. I wrote it before realising that what I wanted to do could be more easily done from phpMyAdmin, but think it still may benefit someone.

Browse Table Structures, Export SQL Data as CSV File

This single, self-contained PHP script allows you to access an SQL database by entering the connection information. Once in, you can freely browse the table structures, and can export SQL data of any table as a CSV file (comma separated values)

Import a CSV File Into A Table Within A Database

It also contains code that allows you to import a CSV file to an existing table within the database.

Here Is The Code:

You can run this script : here or download it here.

<?php
/************************************************************/
/*** A Script To Import / Export Tables as CSV Files ***/
/*** R. A. Davies 2009 ***/
/************************************************************/

/* Store name of this script */
$thisfile=substr(strrchr($_SERVER['PHP_SELF'], ‘/’), 1 );

/* If some action has been performed */
if (isset($_POST['action'])) {

/* Fetch Database Connection Settings */

$user=$_POST['dbusername'];
$host=$_POST['dbaddress'];
$pass=$_POST['dbpassword'];
$db=$_POST['dbname'];

/* Connect To Database */

$link = mysql_connect($host, $user, $pass) or die(”Can not connect.” . mysql_error());
mysql_select_db($db) or die(”Can not connect.”);

if ($_POST['action'] == 1) {

/* Fetch List of Tables From Database To Browse */

$sql = “SHOW TABLES FROM $db”;
$result = mysql_query($sql);

if (!$result) {
echo “DB Error, could not list tablesn”;
echo ‘MySQL Error: ‘ . mysql_error();
exit;
}

/* If the user is not viewing or exporting a table, they just want a list of them */

if ((!isset($_POST['exporttable'])) && (!isset($_POST['viewtable']))) {

echo “<h1>Listing All Tables in $db</h1><table border=1>”;
while ($row = mysql_fetch_row($result)) {

/* Print Out One Table At a Time, Give User Option To View Table Or Export */

echo “<tr><td>Table:</td><td><strong>{$row[0]}</strong></td><td><form

action=”$thisfile” method=”post”><input type=”hidden” name=”action” value=”1

“><input type=”hidden” name=”viewtable” value=”$row[0]“><input type=”hidden”

name=”dbname” value=”".$_POST['dbname'].”"/><input type=”hidden”

name=”dbaddress” value=”".$_POST['dbaddress'].”"/><input type=”hidden”

name=”dbusername” value=”".$_POST['dbusername'].”"/><input type=”hidden”

name=”dbpassword” value=”".$_POST['dbpassword'].”" /><input type=”submit”

value=”view table”></form></td><td><form action=”$thisfile” method=”post”>

<input type=”hidden” name=”action” value=”1″><input type=”hidden”

name=”exporttable” value=”$row[0]“><input type=”hidden” name=”dbname”

value=”".$_POST['dbname'].”"/><input type=”hidden” name=”dbaddress”

value=”".$_POST['dbaddress'].”"/><input type=”hidden” name=”dbusername”

value=”".$_POST['dbusername'].”"/><input type=”hidden” name=”dbpassword”

value=”".$_POST['dbpassword'].”" /><input type=”submit” value=”export

table”></form></td></tr>”; }
echo “</table>”;

mysql_free_result($result);
}

/* If User Has Chosen To Export Table … */

if (isset($_POST['exporttable'])) {
$table=$_POST['exporttable'];
$link = mysql_connect($host, $user, $pass) or die(”Can not connect.” .

mysql_error());
mysql_select_db($db) or die(”Can not connect.”);

/* List All Fields in Chosen Table */

$result = mysql_query(”SHOW COLUMNS FROM “.$table.”");

/* Fetch The Attributes of Each Field In Table */

$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].”; “;
$i++;
}
}

$csv_output .= “n”;

/* Select All Fields Within Table and Print Them */

$values = mysql_query(”SELECT * FROM “.$table.”");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].”; “;
}
$csv_output .= “n”;
}

/* Start of Header To Send To Client */

$filename = $file.”_”.date(”Y-m-d_H-i”,time());
header(”Content-type: application/vnd.ms-excel”);
header(”Content-disposition: csv” . date(”Y-m-d”) . “.csv”);
header( “Content-disposition: filename=”.$filename.”.csv”);

/* Send Output As File */

print $csv_output;

/* Ensure Script Terminates */

exit;

}

/* If The User Has Chosen To View A Table */

if (isset($_POST['viewtable'])) {
$table=$_POST['viewtable']; // name of table user has chosen to view

/* Fetch All Attributes About All Tables */

$result = mysql_query(”SELECT * FROM $table”);
$fields = mysql_num_fields($result);
$rows = mysql_num_rows($result);
$table = mysql_field_table($result, 0);

for ($i=0; $i < $fields; $i++) {
$type[$i] = mysql_field_type($result, $i);
$name[$i] = mysql_field_name($result, $i);
$len[$i] = mysql_field_len($result, $i);
$flags[$i] = mysql_field_flags($result, $i);
}

/* Now List All Fields */

$sql = “SHOW FIELDS FROM $table”;
$result = mysql_query($sql);

if (!$result) {
echo “DB Error, could not list tablesn”;
echo ‘MySQL Error: ‘ . mysql_error();
exit;
}

/* Output Headers Of Table */

echo “<table border=1><tr bgcolor=”black”><td style=”color:white”><strong>Fields in

<u>$table</u></strong></td><td style=”color:white”>Field Type</td><td

style=”color:white”>Size</td><td style=”color:white”>Length</td><td

style=”color:white”>Flags</td></tr>”;

/* Output Fields One By One, Including The Attributes Fetched (Above) */

$x=0;

while ($row = mysql_fetch_row($result)) {
echo “<tr><td>{$row[0]}</td><td>”;
echo $type[$x].”</td><td>”;
echo $len[$x].”</td><td>”;
echo $flags[$x++].”</td></tr>”;

}
echo “</table><form action=”$thisfile” method=”post”><input type=”hidden”

name=”action” value=”1″><input type=”hidden” name=”dbname” value=”".$_POST

['dbname'].”"/><input type=”hidden” name=”dbaddress” value=”".$_POST

['dbaddress'].”"/><input type=”hidden” name=”dbusername” value=”".$_POST

['dbusername'].”"/><input type=”hidden” name=”dbpassword” value=”".$_POST

['dbpassword'].”" /><input type=”submit” value=”Back” />
</form>
“;

mysql_free_result($result);

}
}

else { /* Else they are not exporting, but IMPORTING */

/* Fetch Database Settings Again */

$databasehost = $_POST['dbaddress'];
$databasename = $_POST['dbname'];
$databasetable = $_POST['importtablename'];
$databaseusername =$_POST['dbusername'];
$databasepassword = $_POST['dbpassword'];
$fieldseparator = “,”;
$lineseparator = “n”;

/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment

integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don’t set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the

table
/********************************/
$addauto = 1;

/* Fetch CSV Content From File Uploaded By User */

$csvcontent = file_get_contents($_FILES['uploadedfile']['tmp_name']);

/* Connect To Database In Usual Way… */

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die

(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = “”;
$linearray = array();

/* Go through lines of CSV File One By One */

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;
$line = trim($line,” t”);
$line = str_replace(”r”,”",$line);
$line = str_replace(”‘”,”‘”,$line);

$linearray = explode($fieldseparator,$line);

$linemysql = implode(”‘,’”,$linearray);

if($addauto)
$query = “insert into $databasetable values(”,’$linemysql’);”;
else
$query = “insert into $databasetable values(’$linemysql’);”;

$queries .= $query . “n”;

@mysql_query($query);
echo “executing : $query<br>”;
}

@mysql_close($con);

echo “Found a total of $lines records in this csv file.n”;

}
}

?>
<html>
<head>
<title>CSV File Importer / Exporter</title>
<style type=”text/css”>
body { padding:25px; text-align:center; /* for IE */
margin:0 auto; /* for the rest */ }
#import { float:left; margin-left:100px; }
#export { float:right; }
</style>
</head>
<h1>CSV PHP Importer/Exporter <a href=”http://www.rogerdavies.com”>Roger A. Davies</a>

&copy; 2009</h1>
<div id=”export”>
<h2>Export Database to CSV File</h2>
<p>Please enter your database settings to view database:</p>
<form action=”<?=$thisfile;?>” method=”post”>
<input type=”hidden” name=”action” value=”1″>
Database Name: <input type=”text” name=”dbname” value=”<?=$_POST['dbname'];?>”/><br

/>
Database Address: <input type=”text” name=”dbaddress” value=”<?=$_POST

['dbaddress'];?>”/><br />
Database Username: <input type=”text” name=”dbusername” value=”<?=$_POST

['dbusername'];?>” /><br />
Database Password: <input type=”text” name=”dbpassword” value=”<?=$_POST

['dbpassword'];?>” /><br />
<input type=”submit” value=”Connect” />
</form>
</div>
<div id=”import”>
<h2>Import CSV File to Database</h2>
<form action=”<?=$thisfile;?>”enctype=”multipart/form-data” method=”post”>
<input type=”hidden” name=”action” value=”2″>
Import To Table: <input type=”text” name=”importtablename” value=”<?=$_POST

['importtablename'];?>”><br />
Choose a file to upload: <input name=”uploadedfile” type=”file” /><br />
Database Name: <input type=”text” name=”dbname” value=”<?=$_POST['dbname'];?>”/><br

/>
Database Address: <input type=”text” name=”dbaddress” value=”<?=$_POST

['dbaddress'];?>”/><br />
Database Username: <input type=”text” name=”dbusername” value=”<?=$_POST

['dbusername'];?>” /><br />
Database Password: <input type=”text” name=”dbpassword” value=”<?=$_POST

['dbpassword'];?>” /><br />
<input type=”submit” value=”Import”>
</form>
</div>
</html>