exportCSV.php
Pour exporter en CSV une base de données MySQL
simple
<?php header('Content-type: text/x-csv'); header('Content-Disposition: attachment; filename="tpg.csv"'); include_once("/register_global.inc.php"); //if register_globals=off #produit un fichier csv include("connect.php"); $db=connect_db(); $db_name=db_name(); mysql_select_db($db_name,$db); $result = mysql_query("select * from laTable"); $dbResult = mysql_list_fields("$database_name", "laTable", $db); #nom des champs //loop over each field $x="\""; for($i = 0; $i < mysql_num_fields($dbResult); $i++) { $txt=mysql_field_name($dbResult, $i); $txt=ereg_replace(";", "\;", $txt); $x.=$txt ."\";\""; } $x.="\"\n"; $i = 0; while ($i < mysql_num_rows($result)) { $x.="\""; //loop over each field for($j = 0; $j < mysql_num_fields($dbResult); $j++) { $lechamp=mysql_field_name($dbResult, $j); $txt=mysql_result($result,$i,$lechamp); $txt=ereg_replace(";", "\;", $txt); //pour les séparateurs de champ $txt=preg_replace("/(\015\012)|(\015)|(\012)/"," ", $txt); //caractères spéciaux $txt=ereg_replace("\r\n", " ", $txt); //retours chariot $txt=ereg_replace("\n", " ", $txt); //retours chariot $txt=stripslashes($txt); //enlève backslashes $txt=str_replace('\\',"", $txt);//enlève backslashes $x.=$txt ."\";\""; } $i++; $x.="\"\n"; } echo "$x"; ?>
avec fake protection apache (genre .htaccess)
<?php //protection $ok=-1; //start with no ID $login="putTheLoginHere"; $password="4e25c5072bcaa74153338743517d2a79"; //put encrypted passwd (md5) here if (($_SERVER['PHP_AUTH_USER']==$login) && (md5($_SERVER['PHP_AUTH_PW'])==$password)) { //USER ok, keep id $ok=$i; } //if check KO, $ok is still -1 //ask login+password if ($ok==-1) { header("WWW-Authenticate: Basic realm='Restricted Area'"); print("<H1>Authorization Required</H1>This server could not verify that you are authorized to access the document requested. Either you supplied the wrong credentials (e.g., bad password), or your browser doesn't understand how to supply the credentials required.<hr>"); exit; } header('Content-type: text/x-csv'); header('Content-Disposition: attachment; filename="myFile.csv"'); //change the name for the one you like see connect.inc.php #produit un fichier csv include("connect.php"); $db=connect_db(); $db_name=db_name(); mysql_select_db($db_name,$db); $result = mysql_query("select * from w3_tablename"); //change for tableName $dbResult = mysql_list_fields("$database_name", "w3_unige_fapse_psychodiff08_static_inscription", $db); #nom des champs //loop over each field $x="\""; for($i = 0; $i < mysql_num_fields($dbResult); $i++) { $txt=mysql_field_name($dbResult, $i); $txt=ereg_replace(";", "\;", $txt); $x.=$txt ."\";\""; } $x.="\"\n"; $i = 0; while ($i < mysql_num_rows($result)) { $x.="\""; //loop over each field for($j = 0; $j < mysql_num_fields($dbResult); $j++) { $lechamp=mysql_field_name($dbResult, $j); $txt=mysql_result($result,$i,$lechamp); $txt=ereg_replace(";", "\;", $txt); //pour les séparateurs de champ $txt=preg_replace("/(\015\012)|(\015)|(\012)/"," ", $txt); //caractères spéciaux $txt=ereg_replace("\r\n", " ", $txt); //retours chariot $txt=ereg_replace("\n", " ", $txt); //retours chariot $txt=stripslashes($txt); //enlève backslashes $txt=str_replace('\\',"", $txt);//enlève backslashes $x.=$txt ."\";\""; } $i++; $x.="\"\n"; } echo "$x"; ?>
old
<?php include("../common/tools.inc.php"); $serveur= $_SERVER["HTTP_HOST"]; if($serveur=="localhost") { //serveur de développement $dbPassword = ""; $dbHostname = "localhost"; $dbName = ""; } elseif ($serveur==""){ //serveur de prod $dbUsername = ""; $dbPassword = ""; $dbHostname = "localhost"; $dbName = ""; } elseif ($serveur==""){ //serveur de backups $dbName = ""; $dbUsername = ""; $dbPassword = ""; $dbHostname = "localhost"; } // Make connection to database // If no connection made, display error Message $dblink = MYSQL_CONNECT($dbHostname, $dbUsername, $dbPassword) OR DIE("Error !! Unable to connect to database"); // Select the database name to be used or else print error message if unsuccessful*/ @mysql_select_db($dbName) or die( "Unable to select database ".$dbName); header('Content-type: text/x-csv;'); header('Content-type: text/xml;'); header('Content-Disposition: attachment; filename=export.csv'); /* Generation of .php files to administrate a MySQL database page Export CSV / XML format Created Fred Radeff 2007 */ $aujourdhui=date("d-m-Y H:m"); $sql = " "; //sql query #testSql($sql); $result = mysql_query("$sql"); $i=0; while($i<mysql_num_rows($result)) { #enseignant $txt = mysql_result($result,$i,'enseignantIDidField'); $txt=trim(addslashes(ereg_replace(";", "\;", $txt))); $x.="\"" .$txt ."\";"; #...; $txt = mysql_result($result,$i,'REMARQUES'); $txt=trim(addslashes(ereg_replace(";", "\;", $txt))); $x.="\"" .$txt ."\";"; $x.="\n"; #$txt=preg_replace("/(\015\012)|(\015)|(\012)/"," ", $txt); $i++; #$x=ereg_replace('^"\"','"',$x); } #echo "<pre>"; echo "id_convocation...;...REMARQUES école\n"; echo "$x"; echo "\n\nExtration CSV du:;" .$aujourdhui ."\n"; #echo "</pre>"; ?>