[SOLVED] Select Column

[confused_aswell]

Hi

 

I have this script that connects to a table in my database and downloads it as a csv file, is it possible to select just a few of the columns titles from the database? As of right now it displays all of the column titles in 1 cell eg. address_book_id,customer_id etc. The results from the table are showing in their correct cells but I just want the column titles to do the same, is this possible?

 

Thanks,

 

Phil

 

<?php

$host = 'localhost';

$user = 'username';

$pass = 'password';

$db = 'database_name';

$table = 'zen_address_book';

$file = 'export';

 

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

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

 

 

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

$i = 0;

if (mysql_num_rows($result) > 0) {

$replace = array(

  'entry_firstname' => 'First Name',

  'entry_lastname' => 'Last Name',

  'entry_street_address' => 'Address',

  'entry_suburb' => 'City',

  'entry_state' => 'County',

  'entry_postcode' => 'Post Code'

);

while ($row = mysql_fetch_assoc($result)) {

$csv_output .= str_replace(array_keys($replace), $replace, $row['Field'])." ";

$i++;

}

}

$csv_output .= "\n";

 

$values = mysql_query("SELECT entry_firstname, entry_lastname, entry_street_address FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {

for ($j=0;$j<$i;$j++) {

$csv_output .= '"'.$rowr[$j].'",';

 

}

$csv_output .= "\n";

}

 

$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");

print $csv_output;

exit;

?>

 

 

[kpasiva]

Hi,

 

You forgot to add column separator (,) while inserting column titles,

 

replace the line

 

<?php
$csv_output .= str_replace(array_keys($replace), $replace, $row['Field'])." "; 
?>

 

to

 

<?php
$csv_output .= '"'.str_replace(array_keys($replace), $replace, $row['Field']).'",'; 
?>

 

 

[confused_aswell]

Hi

 

I did know about that, the plan is to have each column title in a seperate cell at the top of the excel sheet and the database results below in the corresponding columns, and so I took out the "," as part of the process.

 

Thanks,

 

Phil

[kpasiva]

 

Oh.. Got it but any way you have to use separator to place the title in a separate columns.

 

Instead of using SHOW COLUMNS  to get column title, Use array_keys from the result row

 

$title_array = array_keys($row);

 

then it will return only the selected column titles from the select row. But you have to use mysql_fetch_assoc instead of mysql_fetch_row

 

 

 

[confused_aswell]

Hi

 

Would it be poosible for you to write or adapt the above script as I don't seem to know where to put $title_array = array_keys($row);

 

I hope you don't mind,

 

Thanks,

 

Phil

[kpasiva]

Alright,

 

Here is the script for you 

 

 

<?php
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database_name';
$table = 'zen_address_book';
$file = 'export';

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

$replace = array( 
   'entry_firstname' => 'First Name', 
   'entry_lastname' => 'Last Name',
   'entry_street_address' => 'Address',
   'entry_suburb' => 'City',
   'entry_state' => 'County',
   'entry_postcode' => 'Post Code' 
); 

$values = mysql_query("SELECT entry_firstname, entry_lastname, entry_street_address FROM ".$table."");
$i=0;
while ($rowr = mysql_fetch_assoc($values)) {
if($i==0) {
foreach(array_keys($rowr) as $title)
$csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",';
$csv_output .= "\n";	
}

foreach ($rowr as $key => $value) {
$csv_output .= '"'.$value.'",'; 

}
$csv_output .= "\n";
$i++;
}

$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");
print $csv_output;
exit;

?>

 

 

 

[confused_aswell]

Hi

 

Thanks for that, it is showing up an error, here is the error -

 

Parse error: syntax error, unexpected $end in /home/phil/public_html/database1.php on line 54

 

Thanks,

 

Phil

 

[kpasiva]

Hi,

 

There is no error in the given script, since i have executed the script locally with test table. You might made some error while integrating with your original file.

 

Please check you might for got to remove unwanted curly braces ({)

 

-Siva

[confused_aswell]

Hi

 

Thank you very much it is all sorted now. I am so glad there are people like you in the world, have a nice day and give yourself a pat on the back.

 

Well done,

 

Phil

 

[kpasiva]

Welcome 

 

Always here to help,

 

-Siva