Select Into Outfile with columns name. How to ?

Applications Blank
#1

Dear all,
I did a parametric export of selected tables of a database.
Following select works correctly.
SELECT * INTO OUTFILE ‘filename.csv’ FIELDS TERMINATED BY ‘;’ FROM tableName"

$sql_1 = “SELECT * INTO OUTFILE '”. $dest_file ."’ ". $outparam . "FROM ". $valori[$i];
sc_exec_sql($sql_1);

Now I would like to add to it also a first line with columns name separated with ‘,’ .
I’ve tried to use UNION but it give me an error about the nr of columns that are different .

Just to test it with a only table I did the following.

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘calendar’
UNION
SELECT * FROM associations_mng.calendar

error: #1222 - The used SELECT statements have a different number of columns
It’s correct becouse first select is about 1 column with all columns names that they are 9 records,
the second is about 9 columns with some records. (1 vs 9)

Have you got an easy solution to have something like that as csv file in output

COL_NAME1, COL_NAME2, COL_NAME3, … COL_NAME n,
AAAAA, bbbbbb, CCCCC, … NNNNNN
AAAAA, bbbbbb, CCCCC, … NNNNNN
AAAAA, bbbbbb, CCCCC, … NNNNNN

In XLS it would be similar to make the reverse copy of first colum…

Bye
Thanks

#2

Re: Select Into Outfile with columns name. How to ?

A little step forward is to add a GROUP_CONCAT but still problem about different number of colums

SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘calendar’
UNION
SELECT * FROM associations_mng.calendar

#1222 - The used SELECT statements have a different number of columns

STUCKED HERE !

#3

Re: Select Into Outfile with columns name. How to ?

Hi Giovannino, maybe this can be useful to export to csv:

<?php
$conexion = mysql_connect(“localhost”, “root”, “sistemas”);
mysql_select_db(“escolar”, $conexion);

$f = fopen(“archalumnos.csv”,“w”); // abre el archivo CSV de salida
$separador = “,”;

$encabezado = “Id_Escuela,No_Control,Nombre,Nivel,Grado,Ciclo_Escolar,Beca,Activo”."
"; fwrite($f,$encabezado);

$queData = “SELECT * FROM alumnos ORDER BY nombre”;
$resData = mysql_query($queData, $conexion) or die(mysql_error());
$totData = mysql_num_rows($resData);

if ($totData> 0) {

while($reg = mysql_fetch_array($resData) ) {
$linea = $reg[‘idescuela’] . $separador . $reg[‘nocontrol’] . $separador . $reg[‘nombre’] . $separador . $reg[‘nivelestudios’] . $separador . $reg[‘grado’] . $separador . $reg[‘cicloescolar’] . $separador . $reg[‘beca’] . $separador . $reg[‘activo’] . "
"; fwrite($f,$linea);
}
}

fclose($f); // cierra el archivo CSV de salida

echo “<a href=archalumnos.csv>Descargar CSV</a>”;

mysql_close($conexion); //cierra la conexion
?>

Bye

Alberto Aguilar
M?xico