CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: PHP to EXCEL

  1. #1
    Join Date
    Dec 2008
    Posts
    27

    PHP to EXCEL

    im using php and mysql

    can anyone help me on using this program

    PHP Code:
    <?php
    $line1
    ="ID\tProduct\tColor\tSales\t";
    $line2="1\tPrinter\tGrey\t13\t";
    $line3="2\tCD\tBlue\t15\t";
    $line4="3\tDVD\tRed\t7\t";
    $line5="4\tMonitor\tGreen\t4\t";
    $line6="5\tTelephone\tBlack\t2\t";

    $data="$line1\n$line2\n$line3\n$line4\n$line5\n$line6\n";
     
    header("Content-type: application/x-msdownload");
    header("Content-Disposition: attachment; filename=extraction.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print 
    "$header\n$data";
    ?>
    i got this in the net and i want to edit it depending on my format

    how can i change this part on my own variable coming from my database?

    $line1="ID\tProduct\tColor\tSales\t";
    $line2="1\tPrinter\tGrey\t13\t";
    $line3="2\tCD\tBlue\t15\t";
    $line4="3\tDVD\tRed\t7\t";
    $line5="4\tMonitor\tGreen\t4\t";
    $line6="5\tTelephone\tBlack\t2\t";

    instead of this i want to use this for ex.

    $line1="ID\tProduct\tColor\tSales\t";
    $line2="1\t$product\t$color\t$sales\t";

    i have a list of records with different columns and below the records i put a button save to spreadsheet. i want that whatever is in the list, it should be on the excel when i transport it.

    help




    tnx
    Last edited by ryanbong; February 16th, 2009 at 02:40 AM. Reason: added tags

  2. #2
    Join Date
    May 2002
    Posts
    10,943

    Re: PHP to EXCEL

    Implementation of the first half of the code would be completely different with a database. You don't just replace text in the string, you would have to re-create the string itself.

    PHP Code:
    <?php
    $output 
    = array();
    $output[] = "ID\tProduct\tColor\tSales\t";

    // connect to mysql database

    $num 1;
    $query "SELECT * FROM products";
    $sql mysql_query($query);
    while (
    $row mysql_fetch_object($sql)) {
      
    $output[] = $num '\t' $row->product '\t' $row->color '\t' $row->sales '\t';
      
    $num++;
    }
    mysql_free_result($sql);

    // disconnect from database

    $data implode("\n"$output);

    // do the rest of the headers here
    ?>
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  3. #3
    Join Date
    Dec 2008
    Posts
    27

    Re: PHP to EXCEL

    tnx MOD, i got it. but how can i save the file with the filename i want? what will i change in the header?

  4. #4
    Join Date
    May 2002
    Posts
    10,943

    Re: PHP to EXCEL

    Quote Originally Posted by ryanbong View Post
    but how can i save the file with the filename i want?
    Are you serious??? It's right there in the 14 lines of code...filename=extraction.xls.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  5. #5
    Join Date
    Dec 2008
    Posts
    27

    Re: PHP to EXCEL

    no i mean. save as.

    bec. the filename is automatically extraction.xls

    is there a way that i can save as the filename

  6. #6
    Join Date
    Dec 2008
    Posts
    27

    Re: PHP to EXCEL

    MOD i have another question.

    my data fetching is already ok with this code.

    PHP Code:
    <?php
        
        
    include("conn_db.php");

    $output = array();
    $output[] = "SO No.\tProduct\tModel No.\tWarranty\t";

    // connect to mysql database

    $num 1;
    $query "SELECT so.so_no, pr.description as product, pr.model_no, w.warranty
                FROM service_order AS so
                LEFT JOIN product as pr ON pr.prod_id = so.prod_id
                LEFT JOIN product_category as pc ON pc.code_cat = pr.code_cat
                LEFT JOIN warranty as w ON w.warranty_id = so.warranty_id
                
    $sql = mysql_query($query);
    while (
    $row = mysql_fetch_object($sql)) {
      
    $output[] = $row->so_no . "\t" . $row->product . "\t" . $row->model_no . "\t" . $row->warranty . "\t";
      
    $num++;
    }
    mysql_free_result(
    $sql);

    // disconnect from database

    $data = implode("\n", $output);

    header("
    Content-typeapplication/x-msdownload"); 
    header("
    Content-Dispositionattachmentfilename=extraction.xls"); 
    header("
    Pragmano-cache"); 
    header("
    Expires0"); 
    print "
    $header\n$data"; ?>
    but when i try to add this query i got many errors

    PHP Code:
    $result mysql_query("SELECT * FROM so_trouble_reported WHERE so_no = $so_no");
            
    $getTr_id mysql_fetch_array($result);
            
    $result mysql_query("SELECT * FROM trouble_reported WHERE tr_id = ".$getTr_id["tr_id"]);
            
    $getTrCode mysql_fetch_array($result); 

    i put this right after my last LEFT JOIN and i got errors.
    now. where should i put this query? i like to use the var $getTrCode
    Last edited by ryanbong; February 19th, 2009 at 01:04 AM. Reason: added tags

  7. #7
    Join Date
    May 2002
    Posts
    10,943

    Re: PHP to EXCEL

    Quote Originally Posted by ryanbong View Post
    no i mean. save as.

    bec. the filename is automatically extraction.xls

    is there a way that i can save as the filename
    Of course the filename automatically is set to extraction.xls. That's what I just told you in my previous post. Change that value to whatever you want the new filename to be.

    As for your last post, you will notice that the color highlighting is incorrect. That's because you have syntax errors. You are missing a "; after your $query.

    Quote Originally Posted by ryanbong
    but when i try to add this query i got many errors

    PHP Code:
    $result mysql_query("SELECT * FROM so_trouble_reported WHERE so_no = $so_no");
    $getTr_id mysql_fetch_array($result);
    $result mysql_query("SELECT * FROM trouble_reported WHERE tr_id = ".$getTr_id["tr_id"]);
    $getTrCode mysql_fetch_array($result); 
    When making a SQL query, if the value you are attempting to set, or check with a where statement, is not an integer, it must be encased in quotes.

    Code:
    ...WHERE column = 'value'...
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  8. #8
    Join Date
    Dec 2008
    Posts
    27

    Re: PHP to EXCEL

    i hav a few more questions.

    how can i pass values through an a href?


    a have a form that needs date period input from the user.

    PHP Code:
    <tr>
        <
    td width="10%" align="left" class="txtformat1">&nbsp;Period between:&nbsp;</td>
        <
    td width="4%" align="left"><input name="start_string" type="text" class="txtreq" id="start_string" size="10" onClick="javascript:showCal('rpg_calendar1')" readonly/></td>
        <
    td width="1%" align="left" class="txtformat1">&nbsp;to</td>
        <
    td width="4%" align="left"><input name="end_string" type="text" class="txtreq" id="end_string" size="10" onClick="javascript:showCal('rpg_calendar2')" readonly/></td>
        <
    td width="81%" align="left"><input name="button" type="button" class="btn" id="button" value="Search" onclick="validate()"/></td>
      </
    tr
    now what i want is to get the value of start_string and end_string and pass it using this code

    PHP Code:
    <a href=excel_2a.php><input type="button" value="Import to Excel"></a
    then i will use it in excel_2a.php program for filtering.

    heres my excel_2a.php program

    PHP Code:
    <?php
        
        
    include("conn_db.php");

    $output = array();
    $output[] = "SO No.\tProduct\tModel No.\tWarranty\t";

    // connect to mysql database

    $num 1;
    $query "SELECT DATE_FORMAT(so.so_date,'%m/%d/%Y') as date, so.so_no, 
                pr.description as product, pr.model_no, w.warranty
                FROM service_order AS so
                LEFT JOIN product as pr ON pr.prod_id = so.prod_id
                LEFT JOIN warranty as w ON w.warranty_id = so.warranty_id"
    ;
                
    $sql mysql_query($query);
    while (
    $row mysql_fetch_object($sql)) {
      
    $output[] = $row->so_no "\t" $row->product "\t" $row->model_no "\t" $row->warranty "\t";
      
    $num++;
    }
    mysql_free_result($sql);

    // disconnect from database

    $data implode("\n"$output);

    header("Content-type: application/x-msdownload"); 
    header("Content-Disposition: attachment; filename=extraction.xls"); 
    header("Pragma: no-cache"); 
    header("Expires: 0"); 
    print 
    "$header\n$data"?>

  9. #9
    Join Date
    Dec 2008
    Posts
    27

    Re: PHP to EXCEL

    another question.

    on my previews post im sorry for incorrect posting but although i putted "; in the end it still dont work.

    i want to insert this query

    PHP Code:
    $result mysql_query("SELECT * FROM so_trouble_reported WHERE so_no = '$so_no'");
            
    $getTr_id mysql_fetch_array($result);
            
    $result mysql_query("SELECT * FROM trouble_reported WHERE tr_id = ".$getTr_id["tr_id"]);
            
    $getTrCode mysql_fetch_array($result); 
    after this one

    PHP Code:
    $query "SELECT DATE_FORMAT(so.so_date,'%m/%d/%Y') as date, so.so_no,
                pr.description as product, pr.model_no, w.warranty
                FROM service_order AS so
                LEFT JOIN product as pr ON pr.prod_id = so.prod_id
                LEFT JOIN warranty as w ON w.warranty_id = so.warranty_id"

    then how would i get the value of $getTrCode because it comes from another query. what will i add to this code?


    PHP Code:
    $sql mysql_query($query);
    while (
    $row mysql_fetch_object($sql)) {
      
    $output[] = $row->so_no "\t" $row->product "\t" $row->model_no "\t" $row->warranty "\t";
      
    $num++;
    }
    mysql_free_result($sql); 
    Last edited by ryanbong; February 20th, 2009 at 02:42 AM.

  10. #10
    Join Date
    May 2002
    Posts
    10,943

    Re: PHP to EXCEL

    Quote Originally Posted by ryanbong
    how can i pass values through an a href?
    Any beginner should know about $_GET variables.

    Quote Originally Posted by ryanbong
    what will i add to this code?
    Just replace the query string with your own query string.

    Honestly, before you go any further, you seriously need to sit down and read a tutorial on PHP. I suggest you read W3School's PHP tutorial. It shouldn't take you long.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured