1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql dump to Excel - Using PHP

Discussion in 'MySQL' started by nitrag, Jun 12, 2008.

  1. nitrag

    nitrag New Member

    Jun 12, 2008
    Likes Received:
    Trophy Points:
    Hi, I'm trying to get some data dumped into excel from my database. I have the code working to dump from a single table, but i want to combine two different tables and reorganize the headers.

    $stmt1 = "SELECT parentName1, homeaddress, homecity, homezip, homestate FROM user_extra";
    $stmt2 = "SELECT firstName, middleName, lastName, email, status FROM user";
    $result = mysql_query($stmt1);
    $result = mysql_query($stmt2);
    $count = mysql_num_fields($result);
    // this is as far as i got...
    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";
    // # 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";
    // # 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'");
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=excelfile.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    echo $header."\n".$data; 
    How should I change the query so that I can pull a couple variables from user and some others from user_extra. Then have the headers all display on one line, with the results on the following rows?

Share This Page