MySql dump to Excel - Using PHP

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

  1. nitrag

    nitrag New Member

    Joined:
    Jun 12, 2008
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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.

    HTML:
    $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";
    }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";
    }
    
    // # 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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice