Page 1 of 1

Download data and column headers

Posted: Thu Aug 03, 2006 11:40 am
by mohson
Hi the code below prints all my backend data on the screen I then view the page source and save the source as csv. giving me an excel spreadsheet with all my data, PERFECT!!!

BUT can I adjust this code to also download the colum headers as well as the data.

Reason is that some of the data is a simple yes/no value it would be good to know which column the Yes is reffering too, names, dates and other data are easy to recognise but yes/no values needa column header when downloaded.

So as I said above is there anyway to download column header/name as well as data using my method?

Code is below:

Code: Select all

<?php
header ("Content-type: text/csv"); 

/* Connecting, selecting database */
$link = mysql_connect("xxx", "xxx", "xxx")
   or die("Could not connect : " . mysql_error());
mysql_select_db("contact_management_system",$link) or die("Could not select database");

 
 
$query = "SELECT * FROM feedbackcontacts ORDER BY surname";
 
$result = mysql_query($query) or die(mysql_error());
 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
	
	printf ("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
	$line['salutation'],$line['name'],$line['surname'],
	$line['address'],$line['address1'],$line['telephonefax'],
	$line['mobile'],$line['mscints'],$line['mscactive'],
   $line['otheractivites'],$line['gradjobs'],$line['ugproj'],
   $line['pdev'],$line['bcsmem'],$line['bcspds'],
   $line['teach'],$line['accconsult']
	);

 
?>

Posted: Thu Aug 03, 2006 11:43 am
by feyd
fake a "first" row with your column headers.

Posted: Sat Aug 05, 2006 4:12 pm
by mohson
OK Thanks,

Any chance of a demo?

Posted: Sat Aug 05, 2006 5:02 pm
by Weirdan

Code: Select all

echo 'salutation,name,surname,address,address1,telephonefax,mobile,mscints,mscactive,otheractivites,gradjobs,ugproj,pdev,bcsmem,bcspds,teach,accconsult';
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
       
        printf ("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
        $line['salutation'],$line['name'],$line['surname'],
        $line['address'],$line['address1'],$line['telephonefax'],
        $line['mobile'],$line['mscints'],$line['mscactive'],
   $line['otheractivites'],$line['gradjobs'],$line['ugproj'],
   $line['pdev'],$line['bcsmem'],$line['bcspds'],
   $line['teach'],$line['accconsult']
        );

Posted: Sun Aug 06, 2006 6:15 am
by bokehman
Weirdan wrote:

Code: Select all

echo 'salutation, name, surname, address, address1, telephonefax, mobile, mscints, mscactive, otheractivites, gradjobs, ugproj, pdev, bcsmem, bcspds, teach, accconsult';
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
       
        printf ("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
        $line['salutation'],$line['name'],$line['surname'],
        $line['address'],$line['address1'],$line['telephonefax'],
        $line['mobile'],$line['mscints'],$line['mscactive'],
   $line['otheractivites'],$line['gradjobs'],$line['ugproj'],
   $line['pdev'],$line['bcsmem'],$line['bcspds'],
   $line['teach'],$line['accconsult']
        );
That's not very reusable or portable. The query is SELECT * which is enough to get everything needed without hard coding any variable names into the equation. Maybe something like this:

Code: Select all

function print_table($tablename)
{
	$query = "SELECT * FROM $tablename";
	$query_result = mysql_query($query) or die (mysql_error());
	if(mysql_num_rows($query_result) > 0){
		echo '<table class="mysql_table" border="1" cellspacing="0" cellpadding="6">'."\n".
			 '<caption>Table name: "'.$tablename.'"</caption>'."\n";
		$first_time = true;
		while($row = mysql_fetch_assoc($query_result)){
			if($first_time){
				$first_time = false;
				$headings = array_keys($row);
				echo '<tr>';
				foreach($headings as $heading){
					echo '<th>'.$heading.'</th>';
				}
				echo '</tr>'."\n";
			}
			echo '<tr>';
				foreach($headings as $heading){
					echo '<td>';
					echo($row[$heading]) ? $row[$heading] : '&nbsp';
					echo '</td>';
				}
			echo '</tr>'."\n";
		}
		echo '</table>'."\n";
	}else{
		echo 'The table "'.$tablename.'" does not have anything in it yet'."\n";
	}
}
By the way that long echo statement without any gaps is breaking this page.

To just get the column names one possible way is:

Code: Select all

$result = mysql_query("DESC `$tablename`");
while($row = mysql_fetch_assoc($result)) 
{
	$columns[] = $row['Field'];
}

Posted: Thu Aug 10, 2006 4:52 am
by mohson
This doesnt do anything? it still prints the data but no column headers.

Code: Select all

echo 'salutation,name,surname,address,address1,telephonefax,mobile,mscints,mscactive,otheractivites,gradjobs,ugproj,pdev,bcsmem,bcspds,teach,accconsult';
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))  
	
printf ("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
$line['person_id'],$line['salutation'],$line['firstname'],
$line['surname'],$line['organisation'],$line['role'],
$line['address1'],$line['address2'],$line['city'],$line['postcode'],
$line['telephone'],$line['mobile'],$line['fax'],
$line['dateoflastcontact'],$line['datecontactagain'],
$line['notes'],$line['email'],$line['org_id']
	);

Posted: Thu Aug 10, 2006 12:48 pm
by bokehman
If you want a table you need table elements in your output as in the example I posted.

Posted: Thu Aug 10, 2006 12:51 pm
by mohson
I dont want a table I just want the column headers to download as the first row of data.

Posted: Thu Aug 10, 2006 1:25 pm
by feyd
The code, as you've shown, should be sending a very long first row. The first echo in the snippet probably needs a carriage return like all your other lines.