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

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
?>

---

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

Quote:

---

Originally Posted by ryanbong

but how can i save the file with the filename i want?

---

Are you serious??? :confused: It's right there in the 14 lines of code...filename=extraction.xls.

no i mean. save as.

bec. the filename is automatically extraction.xls

is there a way that i can save as the filename

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

Quote:

---

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.

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'...

---

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"; ?>

---

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); 


---

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.