-
February 16th, 2009, 02:36 AM
#1
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
-
February 16th, 2009, 08:58 AM
#2
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.
-
February 18th, 2009, 12:53 AM
#3
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?
-
February 18th, 2009, 09:30 AM
#4
Re: PHP to EXCEL
Originally Posted by ryanbong
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.
-
February 18th, 2009, 10:47 PM
#5
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
-
February 19th, 2009, 01:00 AM
#6
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-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=extraction.xls"); header("Pragma: no-cache"); header("Expires: 0"); 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
-
February 19th, 2009, 08:19 AM
#7
Re: PHP to EXCEL
Originally Posted by ryanbong
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.
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.
-
February 19th, 2009, 10:06 PM
#8
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"> Period between: </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"> 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"; ?>
-
February 19th, 2009, 10:26 PM
#9
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.
-
February 20th, 2009, 08:24 AM
#10
Re: PHP to EXCEL
Originally Posted by ryanbong
how can i pass values through an a href?
Any beginner should know about $_GET variables.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|