searls03 Posted February 19, 2012 Share Posted February 19, 2012 How do I download a table that php queried to excel? I have a code that downloads a table, but how would I make it so that the query for the table is the same as the query for the html table? here is the excel: $select = "SELECT academy, product FROM transactions"; $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) ); $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 ) ) || ( $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"; } header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=your_desired_name.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data"; here is the html: <?php session_start(); require_once "connect.php"; if(isset($_SESSION['logged'])){ $cid = $_SESSION['complete']; $academy = $_SESSION['academy']; } echo $cid; echo $academy; ?> <style type="text/css"> body p { font-size: 14px; } body p { font-size: 24px; } #0 tr td { font-size: 24px; } #0 { font-size: 24px; } </style> <br /> </p> <table width="1200" border="1" cellpadding="0" cellspacing="0" id="0"> <tr style="background-color: #999; font-size: 16px;"> <th width="132" scope="col">Academy</th> <th width="401" scope="col">Product</th> <th width="139" scope="col">Qty.</th> <th width="194" scope="col">Net Price</th> <th width="164" scope="col">Price per unit</th> <th width="259" scope="col">Date Sold</th> </tr> <?php if($_POST['submit']) { $startdate=$_POST['startdate']; $enddate=$_POST['enddate']; $category1=$_POST['category']; $academy1=$_POST['academy']; $products1=$_POST['products']; echo $academy1; if($category1 =="----All----"){ $category1 ='';} else {$category1 = "&& category='".$_POST['category']."'";} if($academy1 =="----All----"){ $academy1 = ''; } else{$academy1 = "and Academy='".$academy1."'"; } if($products1 == "----All----"){ $products1 = ''; } else{$products1 = "and product='".$_POST['products']."'"; } $result = mysql_query("SELECT * FROM transactions where date between '$startdate' and '$enddate' $category1 $products1 $academy1"); $_SESSION['query']= $result; $_SESSION['category1']= $category1; $_SESSION['academy1']= $academy1; $_SESSION['products1']= $products1; while($row = mysql_fetch_array($result)) { $date = $row["date"]; $product = $row["product"]; $month = $row["month"]; $day = $row["day"]; $year = $row["year"]; $category = $row["category"]; $academy = $row["Academy"]; $price = $row["price"]; $priceunit = $row["priceunit"]; $quantity = $row["quantity"]; echo "<tr id='0'>"; echo "<td>"; echo $academy; echo "</td>"; echo "<td>"; echo $product; echo "</td>"; echo "<td>"; echo $quantity; echo "</td>"; echo "<td>"; setlocale(LC_MONETARY, "en_US"); echo money_format("%n", $price); echo "</td>"; echo "<td>"; setlocale(LC_MONETARY, "en_US"); echo money_format("%n", $priceunit); echo "</td>"; echo "<td>"; echo $date; echo "</td>"; echo "</tr>"; $total = $price + $total; } } ?> <tr> <td scope="col">Totals:</td> <th scope="col"> </th> <th scope="col"> </th> <th scope="col"><?php setlocale(LC_MONETARY, "en_US"); echo money_format("%n", $total); ?></th> <th scope="col"> </th> <th scope="col"> </th> </tr> </table><a href="print.php" > export to excel</a> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.