biggieuk Posted June 10, 2008 Share Posted June 10, 2008 Hi all, I am using the following code to export a list of user data from my mysql database: <?php require_once('../Connections/conn.php'); header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=users.csv"); header("Pragma: no-cache"); header("Expires: 0"); mysql_select_db($database_conf, $userconf); $select = "SELECT * FROM users"; $export = mysql_query($select); $count = mysql_num_fields($export); for ($i = 0; $i < $count; $i++) { $header .= mysql_field_name($export, $i)."\t"; } while($row = mysql_fetch_row($export)) { $line = ''; foreach($row as $value) { if ((!isset($value)) OR ($value == "")) { $value = "\t"; } else { $value = str_replace('"', '""', $value); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim($line)."\n"; } $data = str_replace("\r", "", $data); if ($data == "") { $data = "\n(0) Records Found!\n"; } print "$header\n$data"; exit; ?> This exports to a file okay but it is being displayed in a single line, for instance the column headers are showing as: useridusernameemailorganisationselections Is it possible for the user to export from mysql and have everything sorted into columns? P.s. im using Excel 2008 if that makes a difference? thanks very much. Quote Link to comment Share on other sites More sharing options...
mhodge_txs Posted June 10, 2008 Share Posted June 10, 2008 Hey As far as I know CSV files default with a seperator of a "," and not a tab as you have like ."\t" so I think its irgnoring the "\t" hence why its putting your column names in the same cell. I think tutorialized has just what you looking for http://www.tutorialized.com/view/tutorial/Export-MySQL-to-CSV-Excel/16050 In terms of exporting as CSV from mysql. I use MySQL Query browser on Ubuntu and when you get a result set back from executing a SQL statment you can export it as CSV. Hope this helps you. Quote Link to comment Share on other sites More sharing options...
MatthewJ Posted June 10, 2008 Share Posted June 10, 2008 Good concept, but poorly written tutorial at best. Quote Link to comment Share on other sites More sharing options...
jonsjava Posted June 10, 2008 Share Posted June 10, 2008 This is how I wrote my sql to csv: <?php $dbuser = 'USER'; $dbpass = 'PASS'; $db = 'DB'; $host = 'localhost'; $link = mysql_connect($host, $dbuser, $dbpass) or die ("Connection failed due to: " . mysql_error()); mysql_select_db ($db) or die("Selection failed due to: " . mysql_error()); $filename = "ip_export_".date("Y-n-j_Hi_s").".csv"; $full_loc = "export_csv/".$filename; $fp1 = fopen("export_csv/$filename", "w+"); $content1 = "IP address, Description, Company/Department \n"; fwrite($fp1, $content1); fclose($fp1); $result = mysql_query("SELECT * FROM `TABLE`") or die("Uh Oh:".mysql_error()); while ($row = mysql_fetch_array($result)){ $long_ip = $row['ipaddr']; $ip = long2ip($row['ipaddr']); $userinfo = $row['userinf']; $description = $row['descrip']; $fp = fopen("export_csv/$filename", "a"); $content = $ip.", ".$description.", ".$userinfo."\n"; fwrite($fp, $content); fclose($fp); } header("location:".$full_loc); exit(); ?> I'll be glad to explain it. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted June 10, 2008 Share Posted June 10, 2008 First the term "CSV" originally stood for "comma separated value", but has come to mean a file or string which consists of values separated by a single character. In the OP's case this character is a tab. In most cases the character is a comma, but I have seen CSV files which use other characters as separators. Now back to the problem. The original code is much too complicated. Try something like this: <?php require_once('../Connections/conn.php'); header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=users.csv"); header("Pragma: no-cache"); header("Expires: 0"); function x($s) { return (str_replace('"','""',$s)); } $head = false; $tmp = array(); mysql_select_db($database_conf, $userconf); $q = "SELECT * FROM users"; $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); while ($rw = mysql_fetch_assoc($rs)) { if (!$head) { $tmp[] = implode("\t",array_keys($rw)); $head = true; } $tmp[] = '"' . implode('"' . "\t" . '"',array_map('x',$rw)) . '"'; } echo implode("\r\n",$tmp) . "\r\n"; ?> Ken Quote Link to comment Share on other sites More sharing options...
biggieuk Posted June 10, 2008 Author Share Posted June 10, 2008 Thanks for your help, I changed the /t to , and its exporting as i wanted now. Quote Link to comment Share on other sites More sharing options...
biggieuk Posted June 23, 2008 Author Share Posted June 23, 2008 Hi again, Just want to re-open this thread and ask another question.. In my database the final column stores values either like 21,11,40 or a single number. Is it possible to output the values stored with commas into separate columns. e.g... |Selections| | | -------------------------- | 21 | 11 | 40 | thanks. Quote Link to comment Share on other sites More sharing options...
biggieuk Posted June 25, 2008 Author Share Posted June 25, 2008 Dont think i explained myself properly so here is images of what im trying to achieve: Excel output looks like this: I need it to split the values into cells like this: thanks! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.