Export PHP table to CSV, not specific MySQL table - Help Please!

[mfallon]

I am creating a table on my page which I want to be able to export to a CSV file.

 

Below is the code which creates the table. I have had a look at some information on exporting to a CSV but can't seem to work out how it fits in with a SQL statement as opposed to exporting a specific table.

 

I want to be able to add a button which just says download below the table to export the table to CSV.

 

Can anyone help with a link to a tutorial, code snippet or something similar to help me code this. I don't need someone to do it for me, just need some direction to allow me to do it myself.

 

Many thanks, Matt.

 

<?   /* Display Straights Table */
function displayStraights(){
   global $database;
   $q = "SELECT users.id,users.username,users.firstname,users.lastname,users.intcap,users.status,straights.username,COUNT(straights.username) AS stotal,MIN(straights.date) AS mindate,straights.status "
	."FROM straights "
	."JOIN users ON straights.username = users.username "
	."WHERE straights.status = '1' AND users.status = '1' "
	."GROUP BY straights.username "
	."ORDER BY users.id ";
   $result = $database->query($q);
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info";
      return;
   }
   if($num_rows == 0){
      echo "<br>There are no straights currently listed in the Database.";
      return;
   }
   /* Display table contents */
   echo "<p><table width=\"100%\" align=\"center\" border=\"1\" cellspacing=\"0\" cellpadding=\"3\" class=\"sortable\">\n";
   echo "<tr bgcolor=\"#CCCCCC\"><td width=\"85\"><b>Member No</td><td><b>First Name</td><td><b>Surname</td><td width=\"90\"><b>No. Straights</td><td width=\"90\"><b>First Straight</td><td width=\"30\"> </td></tr>\n";
   for($i=0; $i<$num_rows; $i++){
      $uid  = mysql_result($result,$i,"users.id");
      $fname  = mysql_result($result,$i,"users.firstname");
      $lname  = mysql_result($result,$i,"users.lastname");
      $stotal  = mysql_result($result,$i,"stotal");
  $dateArray = explode("-",mysql_result($result,$i,"mindate"),3);
  $sdate = "$dateArray[2]/$dateArray[1]/$dateArray[0]";
      $intcap  = mysql_result($result,$i,"users.intcap");

   echo "<tr><td>$uid</td><td>$fname</td><td>$lname</td><td>$stotal</td><td>$sdate</td><td> ";
   if ($intcap == '0'){ echo "  "; } 
   if ($intcap == '1'){ echo "<img src=\"images/england.gif\" width=\"13\" height=\"16\" /> "; } 
   if ($intcap == '2'){ echo "<img src=\"images/scotland.gif\" width=\"13\" height=\"16\" /> "; } 
   if ($intcap == '3'){ echo "<img src=\"images/wales.gif\" width=\"13\" height=\"16\" /> "; } 
   if ($intcap == '4'){ echo "<img src=\"images/nireland.gif\" width=\"13\" height=\"16\" /> "; } 
   if ($intcap == '5'){ echo "<img src=\"images/ireland.gif\" width=\"13\" height=\"16\" /> "; } 
   if ($intcap == '6'){ echo "<img src=\"images/usa.gif\" width=\"13\" height=\"16\" /> "; } 
   echo "</td></tr>\n";
   }
   echo "</table><br><br></p>\n";
}
?>

[jonsjava]

here's an example of how I handle forms (with these functions, I can import any form into the database, and export as csv, without knowing anything about the form):

 

functions.inc.php

<?php
/* ***************************** */
/* Database info. MUST BE SET!   */
/* ***************************** */
$host = "localhost";
$db = "sale_leads";
$db_user = "root";
$db_password = "*******";
/* ***************************** */
/* END Database info.            */
/* ***************************** */
$link = mysql_connect($host, $db_user, $db_password);
mysql_select_db($db, $link);

function cleanInput($input, $is_get=false){
if (is_array($input)){
	$new_array22 = array();
	foreach ($input as $key => $value){
		$value = checkForAttack($value, $is_get);
		$new_array22[$key] = mysql_real_escape_string($value);
	}
	$input = array();
	foreach ($new_array22 as $key=>$value){
		$input[$key] = $value;
	}
}
else{
	$$input = checkForAttack($input, $is_get);
	$input = mysql_real_escape_string($input);
}
return $input;
}

function checkForAttack($input, $is_get=false){
if ($is_get != false && stristr($input, "http")){
	$subject = "Hacking (GET) attempt on your website!";
	$to = "*******************";
	$their_ip = $_SERVER['REMOTE_ADDR'];
	$date = date("n/j/Y Hi s:u");
	$whois = shell_exec("/usr/bin/whois $their_ip");
	$page = $_SERVER['PHP_SELF'];
	$headers = "From: [email protected]";
	$message = "IP Address: $their_ip\n
	date Attempted: $date\n
	Page attacked: $page\n
	Data Passed: $input\n
	Who Is Info:\n\n
	$whois\n";
	mail($to, $subject, $message, $headers);
	session_unset();
	session_destroy();
	header("location:".$input);
	exit();
}
elseif (stristr($input, "SELECT *") || stristr($input, "INSERT INTO" || stristr($input, "DESCRIBE TABLE")) || stristr($input, "OR 1")){
	$subject = "Hacking attempt on your website!";
	$to = "[email protected]";
	$their_ip = $_SERVER['REMOTE_ADDR'];
	$date = date("n/j/Y Hi s:u");
	$whois = shell_exec("/usr/bin/whois $their_ip");
	$page = $_SERVER['PHP_SELF'].$_SERVER['QUERY_STRING'];
	$headers = "From: [email protected]";
	$message = "IP Address: $their_ip\n
	date Attempted: $date\n
	Page attacked: $page\n
	Data Passed: $input\n
	Who Is Info:\n\n
	$whois\n";
	mail($to, $subject, $message, $headers);
}
return $input;
}
/**
* Anti-Spam function. It takes the 344 most common spam words, 
* and compares input to the list.  Returns array containing
* number found, the actual text found, and a score for the input
*
* @param unknown_type $input : the data you want to check
* @return array
*/
function stopSpam($input){
$count = 0;
$data_array = array("'hidden' assets","-online","3.28","4u","accept credit cards",
"act now! don't hesitate!","additional income","addresses on cd","adipex","advicer",
"all natural","amazing stuff","apply online","as seen on","auto email removal",
"avoid bankruptcy","baccarrat","be amazed","be your own boss","being a member",
"big bucks","bill 1618","billing address","blackjack","bllogspot","booker",
"brand new pager","bulk email","buy direct","buying judgments","byob","cable converter",
"call free","call now","calling creditors","can't live without","cancel at any time",
"cannot be combined with any other offer","car-rental-e-site","car-rentals-e-site",
"carisoprodol","cash bonus","cashcashcash","casino","casinos","cell phone cancer scam",
"cents on the dollar","chatroom","check or money order","cialis","click below",
"click here link","click to remove","click to remove mailto","collect child support",
"compare rates","compete for your business","confidentially on all orders",
"congratulations","consolidate debt and credit","coolcoolhu","coolhu",
"copy accurately","copy dvds","credit bureaus","credit card offers","credit-card-debt",
"credit-report-4u","cures baldness","cwas","cyclen","cyclobenzaprine","dating-e-site",
"day-trading","dear email","dear friend","dear somebody","debt-consolidation",
"debt-consolidation-consultant","dig up dirt on friends","direct email","direct marketing",
"discreetordering","discusses search engine listings","do it today","don't delete",
"drastically reduced","duty-free","dutyfree","earn per week","easy terms",
"eliminate bad credit","email harvest","email marketing","equityloans",
"expect to earn","fantastic deal","fast viagra delivery","financial freedom",
"find out anything","fioricet","flowers-leading-site","for free","for instant access",
"for just $","free access","free cell phone","free consultation","free dvd",
"free grant money 	","free hosting","free installation","free investment",
"free leads","free membership","free money","free offer","free preview",
"free priority mail","free quote","free sample","free trial","free website",
"freenet","freenet-shopping","full refund","gambling-","get it now","get paid",
"get started now","gift certificate","great offer","guarantee","hair-loss",
"have you been turned down?","health-insurancedeals-4u","hidden assets",
"holdem","holdempoker","holdemsoftware","holdemtexasturbowilson","home employment",
"homeequityloans","homefinance","hotel-dealse-site","hotele-site","hotelse-site",
"human growth hormone","if only it were that easy","in accordance with laws","incest",
"increase sales","increase traffic","insurance","insurance-quotesdeals-4u",
"insurancedeals-4u","investment decision","it's effective","join millions of americans",
"jrcreations","laser printer","levitra","limited time only","long distance phone offer",
"lose weight spam","lower interest rates","lower monthly payment","lowest price",
"luxury car","macinstruct","mail in order form","marketing solutions","mass email",
"meet singles","member stuff","message contains disclaimer","mlm","money back",
"money making","month trial offer","more internet traffic","mortgage rates",
"mortgage-4-u","mortgagequotes","multi level marketing","name brand",
"new customers only","new domain extensions","nigerian","no age restrictions",
"no catch","no claim forms","no cost","no credit check","no disappointment",
"no experience","no fees","no gimmick","no inventory","no investment","no medical exams",
"no middleman","no obligation","no purchase necessary","no questions asked","no selling",
"no strings attached","not intended","off shore","offer expires","offers coupon",
"offers extra cash","offers free (often stolen) passwords","once in lifetime",
"one hundred percent free","one hundred percent guaranteed","one time mailing",
"online biz opportunity","online biz opportunity 	","online pharmacy",
"online-gambling","onlinegambling-4u","only $","opportunity","opt in","order now",
"order status","orders shipped by priority mail","ottawavalleyag","outstanding values",
"ownsthis","palm-texas-holdem-game","paxil","penis","pennies a day",
"people just leave money laying around","pharmacy","phentermine","please read",
"poker-chip","potential earnings","poze","print form signature","print out and fax",
"produced and sent out","profits","promise you ...!","pure profit","pussy","real thing",
"refinance home","removal instructions","remove in quotes","remove subject",
"removes wrinkles","rental-car-e-site","reply remove subject",
"requires initial investment","reserves the right","reverses aging","ringtones",
"risk free","roulette ","round the world","s 1618","safeguard notice",
"satisfaction guaranteed","save $","save big money","save up to","score with babes",
"search engine listings","section 301","see for yourself","sent in compliance",
"serious cash","serious only","shemale","shoes","shopping spree","sign up free today",
"slot-machine","social security number","special promotion","stainless steel",
"stock alert","stock pick","stop snoring","strong buy","stuff on sale",
"subject to credit","supplies are limited","take action now","terms and conditions",
"texas-holdem","the best rates","the following form","they keep your money -- no refund!",
"they're just giving it away","this isn't junk","this isn't spam","thorcarlson",
"top-e-site","top-site","tramadol","trim-spa","ultram","university diplomas","unlimited",
"unsecured credit/debt","urgent","us dollars","vacation offers",
"valeofglamorganconservatives","viagra","viagra and other drugs","vioxx",
"wants credit card","we hate spam","we honor all","weekend getaway",
"what are you waiting for?","while supplies last","while you sleep",
"who really wins?","why pay more?","will not believe your eyes","winner","winning",
"work at home","xanax","you are a winner","you have been selected","your income","zolus");
foreach ($data_array as $value){
	if (stristr($input, $value)){
		$array['bad_item'][] = $value;
		$count++;
	}
}
$array['item'][] = $input;
$total_spam_vars = count($data_array);
$score = ($count / $total_spam_vars) * 1000;
$array['total_found'] = $count;
$array['score'] = $score;
return $array;
}
function calcDate($time_back){
return date('U', strtotime("-$time_back day"));
}
function importLeads($array, $form){
foreach ($array as $key=>$value){
	$value = str_replace("\"", "'", $value);
	if (!isset($count)){
		$output = "$key;;;;;$value";
		$count = 1;
	}
	else{
		$output .= ":::::$key;;;;;$value";
	}
}
$date = date("U");
$sql = "INSERT INTO `leads` VALUES('','$output', '$date', '$form');";
mysql_query($sql);
}
function getLeads($timeframe, $form){
$time_back = calcDate($timeframe);
$sql = "SELECT * FROM `leads` WHERE `date` >= $time_back  AND `form_name`='$form' ORDER BY `id` DESC;";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$array_count = 0;
$mecount = 0;
while ($row = mysql_fetch_assoc($result)){
	$data = $row['data'];
	$date = date("Y-m-d H:i:s", $row['date']);
	$temp_array = explode(":::::", $data);
	$mecount2 = 0;
	foreach ($temp_array as $value){
		$tmp = explode(";;;;;", $value);
		$tmp[1] = str_replace("\"", "'", $tmp[1]);
		if ($mecount2 > 0){
			$tmp2[$mecount] .= ",\"{$tmp[1]}\"";

		}
		else{
			$tmp2[$mecount] = "\"{$date}\",\"{$tmp[1]}\"";
			$mecount2++;
		}
		if ($array_count == 0){
			$tmp3[] = $tmp[0];
		}
	}
	$array_count = 1;
	$mecount++;
}
$output = "\"Date/Time\",";
foreach ($tmp3 as $key=>$value){
	if ($key == 0){
		$output .= "\"$value\"";
	}
	else{
		$output .= ",\"$value\"";
	}
}
foreach ($tmp2 as $value){
	$output .= "\n$value";
}
$filename = "{$form}_leads_".date("jMY_Hi", strtotime("-365 day"))."-".date("jMY_Hi").".csv";
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
print "$output";
}
?>

If you want to know how to use this, let me know.

[Yesideez]

TIP: Don't post confidential data like connection details in your posts - always remove them!

[jonsjava]

If you will notice my past posts, I usually do. My son was distracting me (at home today, because he has an inner-ear infection), so I wasn't paying attention.

[Yesideez]

I know you erased the password but I also remove database and usernames.

[jonsjava]

I know you erased the password but I also remove database and usernames.

Good ideas, generally. Since there is no connection between this code and any site (including my local box) that I'm running, I feel safe that it won't be an issue.

[mfallon]

Not sure if I didn't understand what you posted or if I wasn't clear in my enquiry.

 

I'm not trying to export a SQL table to CSV.

 

I already have a table that I have created using PHP which pulls data from SQL which I want to download.

 

I don't know how to download the table which I have created as a HTML table in CSV format.

 

Hope this is clearer and that someone can help with this.

 

Thanks, Matt