Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Posts
    19

    Unanswered: save query result

    I want to save my query result from mysql database as an excel file. Can I do that using php?

  2. #2
    Join Date
    Jan 2004
    Posts
    12
    I'm sure it's possible, figure out the file structure of an excel file then translate the array returned from the query into that information probably using a foreach to get the key and the value.

    If you had used google before asking your question, which you always should, you would of found this: http://www.scriptsearch.com/details/7506.html

  3. #3
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    The simplest way would be to export the data from the query as a CSV file. This can easily be read by Excel.

  4. #4
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18
    I've had good luck using the header function to tell the page you are sending it an Excel page.

    PHP Code:
     header("Content-Type: application/vnd.ms-excel"); 
    Then you just use code to write the result table just as if you were displaying it in the browser.

    The user will get a pop-up box asking them where to save the file.

  5. #5
    Join Date
    Sep 2003
    Posts
    19
    Originally posted by ursus
    I've had good luck using the header function to tell the page you are sending it an Excel page.

    PHP Code:
     header("Content-Type: application/vnd.ms-excel"); 
    Then you just use code to write the result table just as if you were displaying it in the browser.

    The user will get a pop-up box asking them where to save the file.
    Thanks for reply. How do you write the sql statement for the qureried table to enter in the following line of code:
    $sql = "SELECT * FROM $table";
    $result = @mysql_query ($sql);
    The trouble I have is that I do not know how to write code to save only the queried result, not all the data in the table.

  6. #6
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18
    I'm not following you. With the sql statement you've written there, your 'queried result' is all the data in the table. Are you asking how you define the variable $table you're using in your sql line?


    Originally posted by hailu
    Thanks for reply. How do you write the sql statement for the qureried table to enter in the following line of code:
    $sql = "SELECT * FROM $table";
    $result = @mysql_query ($sql);
    The trouble I have is that I do not know how to write code to save only the queried result, not all the data in the table.

  7. #7
    Join Date
    Sep 2003
    Posts
    19
    Originally posted by ursus
    I'm not following you. With the sql statement you've written there, your 'queried result' is all the data in the table. Are you asking how you define the variable $table you're using in your sql line?
    That is correct. The table I want to save now is only a portion of the table in mysql database.

  8. #8
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18
    Okay, $table needs to be a text string representing the name of the table you want to get results from. If the name of the table you want to see results from is my_table, then you should insert:

    $table="my_table";

    before your $sql line. The sql statement you have is going to give you every column for every record in the table you define in your $table variable. If you only want some of the columns in that table you need to revise your sql statement accordingly.

    Originally posted by hailu
    That is correct. The table I want to save now is only a portion of the table in mysql database.

  9. #9
    Join Date
    Sep 2003
    Posts
    19
    Originally posted by ursus
    Okay, $table needs to be a text string representing the name of the table you want to get results from. If the name of the table you want to see results from is my_table, then you should insert:

    $table="my_table";

    before your $sql line. The sql statement you have is going to give you every column for every record in the table you define in your $table variable. If you only want some of the columns in that table you need to revise your sql statement accordingly.
    This is the code I am trying to use. It is saved as download.php. I am calling it from search.php where the quering is done. My question is: how do I make PHP remember the result of the query in search.php so that I can save it as an excel file. Thanks for taking your time.

    <?php
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=seq.xls");

    $table ="my_table";
    $sql = "SELECT * FROM $table";
    $link = mysql_connect ("localhost", "", "") or die ("Could not connect");
    mysql_select_db("test");
    $result = mysql_query ($sql) or die ("Could not execute query");
    $numFields = mysql_num_fields($result);

    $output = "<HTML><BODY><TABLE><TR BGCOLOR=\"#D5D5D5\">";
    for($x=0; $x<$numFields; $x++)
    $output .= "<TD>".mysql_field_name($result, $x)."</TD>";
    $output .= "</TR>";
    while($row = mysql_fetch_row($result))
    {
    $output .= "<TR>";
    for($x=0; $x<$numFields; $x++)
    $output .= "<TD>".$row[$x]."</TD>";
    $output .= "</TR>";
    }
    $output .= "</TABLE></BODY></HTML>";
    mysql_close($link);
    echo $output;
    ?>

  10. #10
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18
    I don't know what you have going on in search.php, but download.php is building an Excel sheet containing all the records in the table you defined.

    Once the excel sheet is built, you won't be able to query from it. It will be saved to the user's machine, outside the scope of PHP. If you're trying to build an Excel sheet of values based on input from the user (I assume this is what happens in search.php), you should use that query instead of 'Select * from $table' in your $sql line on this page.

  11. #11
    Join Date
    Sep 2003
    Posts
    19
    Originally posted by ursus
    I don't know what you have going on in search.php, but download.php is building an Excel sheet containing all the records in the table you defined.

    Once the excel sheet is built, you won't be able to query from it. It will be saved to the user's machine, outside the scope of PHP. If you're trying to build an Excel sheet of values based on input from the user (I assume this is what happens in search.php), you should use that query instead of 'Select * from $table' in your $sql line on this page.
    Here is search.php

    ---------------------

    <?php session_start();
    $sesid=session_id();

    print "<HTML>
    <HEAD>
    <STYLE>
    FONT {font-family: \"arial\"; font-size: \"10pt\";}
    A {font-family: \"arial\"; font-size: \"10pt\";color:black;font-weight:bold;}
    :visited{font-family: \"arial\"; font-size: \"10pt\";color:black;font-weight:bold;}
    :active {font-family: \"arial\"; font-size: \"10pt\";color:black;font-weight:bold;}
    :hover {font-family: \"arial\"; font-size: \"10pt\";color:blue;font-weight:bold;}
    </STYLE>";

    $sql = "SELECT * FROM seq_table";
    $link = mysql_connect ("localhost", "", "") or die ("Could not connect");
    mysql_select_db("test");
    $result = @mysql_query ($sql)
    or die ("You have not selected any sequences yet!<BR><A HREF='search.php'>[Return to Search Page]</A>");
    $numFields = mysql_num_fields($result);
    $numrows = mysql_num_rows($result);

    </HEAD>
    <BODY BGCOLOR=\"#ffffff\">";
    print "<FONT STYLE='font-size:24pt'>Sequences</FONT><BR>
    <FONT STYLE='font-size:8pt'>Total:$numrows</FONT><BR><BR>
    <A HREF='download.php?ses=$sesid'>[Save Sequences in Excel]<A>";
    print "<TABLE BORDER='1' BORDERCOLOR='black' CELLSPACING='0' WIDTH='100%'><TR>\n";
    $check = 0;
    print "<TH BGCOLOR='#D5D5FF'>&nbsp;</TH>";
    for ($i=1; $i < $numFields; $i++)
    {
    print "<TH BGCOLOR='#D5D5FF'><FONT>".mysql_field_name($result ,$i)."</FONT></TH>\n";
    }
    while( $row = mysql_fetch_row( $result ))
    {
    $id = $row[0];
    print "<TR>";
    print "<TD><INPUT TYPE='checkbox' NAME='chk$check' VALUE='$id'></TD>";
    for($i=1; $i < $numFields; $i++)
    {
    if($i == 7)
    print "<TD><FONT STYLE=\"font-family:courier;\">".$row[$i]."&nbsp;</FONT></TD>\n";
    else if($i ==5)
    printf("<TD><A HREF='GC.php?id=%s'><FONT>%.1f</FONT></A></TD>\n",$id,$row[$i]);
    else
    print "<TD ALIGN='center'><FONT>".$row[$i]."&nbsp;</FONT></TD>\n";

    }
    $check++;
    }
    mysql_close($link);
    ?>
    </BODY>
    </HTML>

  12. #12
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18
    Alright, forgive me if I'm being rude, but I'm getting the feeling you don't know sql and that you grabbed this code from somewhere rather than writing it. Am I right?

    Seriously, if you don't know sql well enough to write basic Select statements you need to work on that first. Check out the W3 Schools tutorial:
    http://www.w3schools.com/sql/default.asp

  13. #13
    Join Date
    Sep 2003
    Posts
    19
    Originally posted by ursus
    Alright, forgive me if I'm being rude, but I'm getting the feeling you don't know sql and that you grabbed this code from somewhere rather than writing it. Am I right?

    Seriously, if you don't know sql well enough to write basic Select statements you need to work on that first. Check out the W3 Schools tutorial:
    http://www.w3schools.com/sql/default.asp
    Of course, I grabbed it somewhere else. I am just trying to adapt it to my own needs. I am not an expert in sql but I can write pretty soophisticated select statements.

    I do not think you understood my question. There must be some way I can transfer the queried data from search to download. I would appreciate a lot if some one out there can give me a tip.

  14. #14
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18
    The code you have in download.php does that. It generates an Excel file containing the results of the sql query.

Posting Permissions

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