Download data and column headers

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Download data and column headers

Post 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']
	);

 
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

fake a "first" row with your column headers.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

OK Thanks,

Any chance of a demo?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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']
        );
User avatar
bokehman
Forum Regular
Posts: 509
Joined: Wed May 11, 2005 2:33 am
Location: Alicante (Spain)

Post 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'];
}
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post 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']
	);
User avatar
bokehman
Forum Regular
Posts: 509
Joined: Wed May 11, 2005 2:33 am
Location: Alicante (Spain)

Post by bokehman »

If you want a table you need table elements in your output as in the example I posted.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

I dont want a table I just want the column headers to download as the first row of data.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply