MySQL queries result download to CSV file

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alliance
Forum Newbie
Posts: 8
Joined: Sun Nov 14, 2004 1:22 pm

MySQL queries result download to CSV file

Post by alliance »

Hello,

Let's say user login to his own account, when user request to see all his transactions history we run a MySQL query and display the results.

Now we would like to offer the user a possibility to click a button to download the result of the query into a CSV file.

Does anyone have a ready script to do this ?
Or any suggestion where to start looking for script example ?

Thanks for your help
Samuel
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

search for "csv export"
neyo
Forum Newbie
Posts: 3
Joined: Wed Mar 16, 2005 2:02 pm
Location: Nigeria
Contact:

Post by neyo »

hi, u can link to a script below with ur query string defined & d data will be exported to Excel, hope this helps!!

NB
Do not 4get 2 include ur connection string

Code: Select all

<?php
# This line will stream the file to the user rather than spray it across the screen
header("Content-Type: application/vnd.ms-excel; name='excel'");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=exporthistory.xls");
header("Pragma: no-cache");
header("Expires: 0");


$result = mysql_query("SELECT Message,Date,Mobile_No,Status FROM history where Username='$CD' and HStatus=0");
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++){
    $header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result)){
  $line = '';
  foreach($row as $value){
    if(!isset($value) || $value == ""){
      $value = "\t";
    }else{
# important to escape any quotes to preserve them in the data.
      $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
  $data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
  $data = "\nno matching records found\n";
}

echo $header."\n".$data; 

?>

feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
alliance
Forum Newbie
Posts: 8
Joined: Sun Nov 14, 2004 1:22 pm

Post by alliance »

many thanks neyo,
works fine
Samuel
alliance
Forum Newbie
Posts: 8
Joined: Sun Nov 14, 2004 1:22 pm

some extra function

Post by alliance »

The script from neyo works fine but let add some extra function.

Right now we read all fields just like they are from table[1]

Let say the field[1] - table[1] is auto-increment and don't need to be included in the result

and

Let say the field[4] - table [1] is an unique ID in table[2] from which one we want to relplace the field[4] - table[1] with the value in field[2] and field[3] from table[2] using a function (get_info_table2 in example below)

I think about something like this:

Code: Select all

20. foreach($row as $value){   
     strpos(get_info_table2($value[4]), $value[4])===false
21. if(!isset($value) || $value == ""){ 
22.      $value = "\t";    }
23. }
Maybe is something more simple than using strpos()

Hope you can help me further on this case.
Thanks
Post Reply