Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    9

    Unanswered: Exporting 2 MySQL tables into an Excel Spreadsheet

    Hi All,

    I know how to export one table into an excel spreadsheet, but what I would like to do is export 2-3 tables in one spreadsheet instead of having sep spreadsheets for all my tables.

    So suppose Table1 has 5 fields and Table2 has 3 fields.....each of them will have ID...not sure how to avoid that from coming in the spreadsheet for Table2 since ID will come from Table1 ???

    Anyways, so on spreadsheet what I would like is this

    //field1 to 5 are headers from Table1 & field-1-2-3 are from Table2

    field1 field2 field3 field4 field5 field-1 field-2 field-3
    data data data data data data data data

    Is this possible ???

    here is the code for exporting just one table into the spreadsheet...
    Code:
    <?php 
    header("Content-type: application/vnd.ms-excel"); 
    header("Content-Disposition: attachment; filename=spreadsheet.xls"); 
    header("Pragma: no-cache"); 
    header("Expires: 0"); 
    //print "$header\n$data"; 
    
    define(db_host, "your_hostname"); 
    define(db_user, "your_username"); 
    define(db_pass, "your_password"); 
    define(db_link, mysql_connect(db_host,db_user,db_pass)); 
    define(db_name, "your_database_name"); 
    mysql_select_db(db_name); 
    
    $select = "SELECT * FROM your_tablename"; 
    $export = mysql_query($select); 
    $fields = mysql_num_fields($export); 
    
    for ($i = 0; $i < $fields; $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 the db 
    echo "$header \n $data" ; 
    ?>
    cheers
    rohit

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using a join in your SQL (or if you prefer use a where clause) to define the realtionship.... Join would be better as its the approved method but it has the same effect as where table1.columna=table2.columnb.

    if you dodecide to use a join or where within PHP then Id reccomend try the query out in MySQL Query Browser untill you are happy it gets the results you expect

    OR

    consider pulling the information from within Excel and use the Excel VBA objects to do the manipulation required
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2006
    Posts
    9
    Thanks for your reply, but isn't that manipulating the MySQL part. I was under the impression I could do this in PHP while I'm exporting the data into the spreadsheet.

    Which type of JOIN would you recommend ? I tried left join - my query is

    select * from Table1 left join Table2

    But the mysql query browser kept giving error no. 1064.

    So if I use this join statement, how will I use PHP to export the joined data into excel. Do I just need to replace the SQL query statement in my PHP code ....

    this is getting damn interesting...thanks...

    cheers
    rohit

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest you do a google on SQL joins

    the syntax for MySQL V5 can be found here

    you need to explicitly tell the SQL engine what criteria you want to use to join (link) the tables

    it can take the form of:-
    select mycolumns from Table1 join Table2 on Table2.ColumnB=Table1.ColumnA

    failing that you can use the where syntax:-
    select mycolumns from Table1, Table2 where Table1.ColumnA=Table2.ColumnB
    Last edited by healdem; 11-14-06 at 11:24.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2006
    Posts
    9
    Thank you so much healden...

    It works fine now..

    cheers
    rohit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •