|
PHP - Export Sql Query To Pdf Or Csv With Php
Similar TutorialsI have this code that is supposed to export the results of this query into a CSV. When I run the code in Firefox, it works perfectly. The only browser that it doesn't work in is IE. It opens an HTML page with the results on the page instead of prompting to download the CSV. Can anyone help me out? Code: [Select] <?php //code to export report to excel if(isset($_GET['date'])){ include('../inc/db.php'); if (!$link = mysql_connect($dbhost, $dbuser, $dbpass)) { echo 'Could not connect to mysql'; exit; } if (!mysql_select_db($dbname, $link)) { echo 'Could not select database'; exit; } $date = $_GET['date']; $select = "SELECT patients.lname, patients.fname, patients.dob, patients.sex, visit_data.cc, FROM_UNIXTIME(visit_data.reg_time), FROM_UNIXTIME(visit_data.discharged_time), visit_data.disposition, visit_data.leftby FROM patients, visit_data WHERE discharge_date = '$date' AND patients.patientid = visit_data.patientid"; $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=DailyLog.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data"; } ?> Hi, I have tried these PDF generators: 1. TCPDF- doesn't support css class. Styles will work if inline. 2. DOMPDF - Supports CSS class. Doesn't have Unicode support. Already tried load_font to convert ttf to amf but still Chinese char are not displayed properly. - Also tried (installed) PDFlib, watermark appears in pdf but characters are not displayed properly. Any suggestion in PDF generator that can do the functionality of TCPDF and DOMPDF? BTW i'm using xampp. Thanks in advance. I need to write a cron job that will sync 2 databases. Is there a quick way to write an export from one database for import into another? Or do i manually have to write the select statements per table, format them as insert ignore strings, then execute them on the other database? Thanks guys! Dear freak gurus out there, I'm new to php and sql as i learn from online examples . Though there are lots of links and answers out there related to my post but none reflects what i need. You guys my only hope. What i have: 3 tables : each has its own number of fields (tbl1=14 ,tbl2=9,tbl3=9). There is a field in each tables that can be related (FamilyField). What i need: export to csv all the records from all 3 tables which relates to search criteria (by user) based on a field (MembershipField) which the data may vary from other tables. pseudo code: $membership_type =$_POST['mtype']; $sql=" SELECT field1,field2,field5,MembershipField from tbl1, (SELECT field1,field2,MembershipField from tbl2), (SELECT field3,field6,field8,MembershipField from tbl3) WHERE MembershipFied=$membership_type"; export to csv file by using INTO OUTFILE; I have tried all the code i can find to export from 1 table to csv, and i manage to use UNION to get output from 3 tables but unable to export as the error message was "incorrect usage of UNION and INTO. is this possible? or any workaround? guides with samples will be a great help. Hello, How can I export mysql database (tables) with php from browser to backup.sql , something like in phpMyAdmin. And simple option with file save as??? Thanks Regards I have a table in my php Mysql which contains some values.. Is it possible to get those values then export them to MS word with formatting? and make it like a table? Help pls. Hello All, I am currently working on export to pdf, below is the code, i am able to see the data is getting retrieved from mysql table. I am having 2 issue: 1. When data is getting retrieved one column is on left corner it's now showing the data as it's Sno it gets hidden from left corner. 2. Second issue when i click on the link it's just adding a # tag on the url nothing else.
Please guide me to fix the code.
<!DOCTYPE html>
<html>
<head>
<Title> Export Supplier / Visiters Details</Title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css"/>
<script type="text/javascript" src="tableExport.js"></script>
<script type="text/javascript" src="jquery.base64.js"></script>
<script type="text/javascript" src="html2canvas.js"></script>
<script type="text/javascript" src="jspdf/libs/sprintf.js"></script>
<script type="text/javascript" src="jspdf/jspdf.js"></script>
<script type="text/javascript" src="jspdf/libs/base64.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
</head>
<?php
$con=mysqli_connect("localhost", "root", "", "cvmsdb");
$qry="SELECT * FROM tblsupvisitor";
$result=mysqli_query($con, $qry);
$records = array();
while($row = mysqli_fetch_assoc($result)){
$records[] = $row;
}
?>
<div class="row" style="height:300px;overflow:scroll;">
<table id="tblsupvisitor" class="table table-striped">
<thead>
<tr class="warning">
<th>SNO.</th>
<th>Full Name</th>
<th>Mobile Number</th>
<th>Address</th>
</tr>
</thead>
<tbody>
<?php foreach($records as $rec):?>
<tr>
<td><?php echo $rec['ID']?></td>
<td><?php echo $rec['FullName']?></td>
<td><?php echo $rec['MobileNumber']?></td>
<td><?php echo $rec['Address']?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<li><a href="#" onclick="$('#tblsupvisitor').tableExport({type:'json',escape:'false'});"> <img src="images/icon/logo.png" width="24px"> Export to Pdf</a></li>
<li><a href="#" onclick="$('#tblsupvisitor').tableExport({type:'json',escape:'false'});"><img src="images/icon/logo.png" width="24px">JSON (ignoreColumn)</a></li>
</html>
Kind Regards, Naveed. ok, so I am not good with arrays, they confuse me. how can I set this up so that $data is all results that are returned from the database that correspond to it? Code: [Select] $data = array( array("firstname" => '$firstname', "lastname" => '$lastname', "age" => '$age')); the original code looked like: Code: [Select] <?PHP $data = array( array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25), array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18), array("firstname" => "James", "lastname" => "Brown", "age" => 31), array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7), array("firstname" => "Michael", "lastname" => "Davis", "age" => 43), array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24), array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27) ); ?> but that won't do it for me. I need it so that the results from the database will automatically be filled in. basically, everyone that is in the database, so when a new member is added, they will show up too. please help Hi: I think this would pertain to a PHP coding feature. I want to give a user the ability to click a link in the admin area called "Export Database" the will export the entire database like: myDataBase.sql and allow them to save it to their computer. They want to be able to make backups without logging into a PHP admin area. I use to do this with Access, but have not done this with mySQL. Has anyone done this before? Hi, I have a PHP web page that displays the results from an SQL query. I was wondering if there was anything out there that would allow my users to click a link and then export the results of that query to a CSV file? Thanks Matt Hi, Am having a total brain melt down on a php/mysql export script. I've a table wp_bp_xprofile_data with this sort of layout: ID field_id user_id value It shows data like so: id | field_id | user_id | value 1 | 1 | 1| John Smith 2 | 2 | 1 |john@smith.com 3 | 3 | 1 |23 arcacia avenue 4 | 1 | 2 |Fred Smith 5 | 2 | 2 |fred@smith.com 6 | 3 | 2 |999 lets be avenue field_id joins to another table wp_bp_xprofile_fields, where it has id name This shows data like so: id | name 1 | Name 2 | Email 3 | Address What I need to do, is export the data to a csv so the CSV will then show: Name | Email | Address John Smith | john@smith.com | 23 arcacia avenue Fred Smith | fred@smith.com | 999 lets be avenue However, mine keeps coming back Name | Email | Address John Smith john@smith.com 23 arcacia avenue Fred Smith fred@smith.com 999 lets be avenue Here's my code: [php] header ("Content-Disposition:\"inline; filename=export.csv"); header ("Content-type: text/csv\""); $query = "SELECT VALUE FROM `wp_bp_xprofile_data` ORDER BY `wp_bp_xprofile_data`.`user_id` ASC"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); echo "email,telephone,county,town,add 2,add 1,company name,name,acc type,date joined,country\r\n"; //header while($row = mysql_fetch_row($result)){ echo "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11]\r\n"; //data echo "$row[]\r\n"; //data } [php] Where am I going wrong? TIA I created this php class which recieves an sql query from flash and then produces a csv file for download. The code in its current form produces a csv file on the server. However, I want it to save on the client computer with a 'Save As' dialog box. I am testing on localhost. Here is the class: <?php class CSVExport { public function __construct() { require_once 'Zend/Date.php'; require_once 'DatabaseConnection.php'; } public function exportCSV($query) { $result = mysql_query($query); $fname = 'CSVFile.csv'; $headers = array(); $rowArray = array(); $numFields = mysql_num_fields($result); for ($i = 0; $i < $numFields; $i++) { $headers[] = mysql_field_name($result , $i); } $fp = fopen($fname, 'w'); if ($fp && $result) { header('Content-Type: application/csv'); header('Content-Disposition: inline; filename='.$fname); fputcsv($fp, $headers); while ($thisrow = mysql_fetch_row($result)) { fputcsv($fp, $thisrow); } readfile($fname); fclose($fp); } die; } } Thanks Hi I have a page displaying orders from a mysql table. How can i add a button which exports this to an excel file? Is it possible to export a single value from a mysql DB into a pre-existing excel spread sheet? I know how to create a spread sheet from php and mysql, but not a single value into a pre-existing excel document cell. here's basically what I'm doing: Creating a financial expense tracking report in php, user will input each expense, it will keep a running tally of the available funds in user's bank account. The user has an excel spread sheet that requires the current value of available funds and updates a budget forecast according. SO, I need to somehow get the available balance of the bank account (generated by user input) into the pre-existing excel spreadsheet. Any thoughts? I'm totally at a loss. Thanks! HI Gurus, I need to export (download) a csv file from the web server to my client. How can this be done in php ? How is the save as dialog invoked ? (I am assuming that file save permissions are allowed) I read that if you invoke the following script (eg form submit), it will work .. <? header("Content-type: text/plain"); header("Content-Disposition: attachment; filename=YOUR_FILE.csv"); ?> Any advice ? Steven M Hi i am new in PHP...have a project and have stuck...please please help.... I have a cart and i need to export the cart contents to excell...here is my code...please take a look and see if u can hepl... I also need to have an export button...and wen pressed the cart will be exported to excell thanks... <?php session_start(); if (!isset($_SESSION["customer"])) { header("location: customer_login.php"); exit(); } //error script error_reporting(E_ALL); ini_set('display_errors','1'); //connect to the database include "../storescripts/connect_to_mysql.php"; ?> <?php /////////////////////////////////////////////////////////////////////////////////// // SECTION ONE /////////////////////////////////////////////////////////////////////////////////// if(isset($_POST['pid'])){ $pid=$_POST['pid']; $wasFound=false; $i=0; //if the cart session is set or empty if(!isset($_SESSION["cart_array"]) || count($_SESSION["cart_array"])< 1){ //Runs if the cart is empty $_SESSION["cart_array"]= array(0 => array("item_id"=>$pid,"quantity"=>1)); }else{ //Runs if the cart has at least one item in it foreach($_SESSION["cart_array"] as $each_item){ $i++; while(list($key,$value)= each($each_item)){ if($key == "item_id"&&$value==$pid){ //the ite is in the cart..hence we adjust the quantity array_splice($_SESSION["cart_array"],$i-1,1,array(array("item_id"=>$pid,"quantity"=>$each_item['quantity']+1))); $wasFound=true; }//close if conditio }//close while loop }//close foreach loop if($wasFound==false){ array_push($_SESSION["cart_array"],array("item_id"=>$pid,"quantity"=>1)); } } header("location: cart.php"); } ?> <?php /////////////////////////////////////////////////////////////////////////////////// // SECTION TWO /////////////////////////////////////////////////////////////////////////////////// //if usser chooses to empty their sopping cart if(isset($_GET['cmd'])&& $_GET['cmd']=="emptycart"){ unset($_SESSION["cart_array"]); } ?> <?php /////////////////////////////////////////////////////////////////////////////////// // SECTION THREE /////////////////////////////////////////////////////////////////////////////////// //if usser chooses to empty their sopping cart if(isset($_POST['item_to_adjust'])&& $_POST['item_to_adjust']!=""){ //execute some code $item_to_adjust=$_POST['item_to_adjust']; $quantity=$_POST['quantity']; $quantity=preg_replace('#[^0-9]#i', '',$quantity); if($quantity >= 1000){$quantity=999;} if($quantity < 1){$quantity= 1;} $i=0; foreach($_SESSION["cart_array"] as $each_item){ $i++; while(list($key,$value)= each($each_item)){ if($key == "item_id"&&$value==$item_to_adjust){ //the ite is in the cart..hence we adjust the quantity array_splice($_SESSION["cart_array"],$i-1,1,array(array("item_id"=>$item_to_adjust,"quantity"=>$quantity))); }//close if conditio }//close while loop }//close foreach loop } ?> <?php /////////////////////////////////////////////////////////////////////////////////// // SECTION FOUR /////////////////////////////////////////////////////////////////////////////////// if(isset($_POST['index_to_remove'])&&$_POST['index_to_remove']!=""){ //access the array and run code to remove $key_to_remove= $_POST['index_to_remove']; if(count($_SESSION["cart_array"])<=1){ unset($_SESSION["cart_array"]); }else{ unset($_SESSION["cart_array"]["$key_to_remove"]); sort($_SESSION["cart_array"]); } } ?> <?php /////////////////////////////////////////////////////////////////////////////////// // SECTION FIVE /////////////////////////////////////////////////////////////////////////////////// $cartOutput=""; $cartTotal=""; if(!isset($_SESSION["cart_array"]) || count($_SESSION["cart_array"])< 1){ $cartOutput="<h2 align='center'>Your Cart Is Empty</h2>"; }else{ $i=0; foreach($_SESSION["cart_array"] as $each_item){ $item_id=$each_item['item_id']; $sql=mysql_query("SELECT * FROM products WHERE id='$item_id' LIMIT 1"); while($row=mysql_fetch_array($sql)){ $product_name=$row["product_name"]; $price=$row['price']; $details=$row['details']; } $pricetotal= $price*$each_item['quantity']; $cartTotal=$pricetotal + $cartTotal; //setlocale(LC_MONETARY,"en_KSHs"); //$pricetotal= money_format("%10.2n", $pricetotal); //dynamic table assembly $cartOutput .="<tr align='center'>"; $cartOutput .='<td><a href="../home.php?id=' . $item_id . '">' .$product_name . '</a><br/><img src="../inventory_images/' . $item_id . '.jpg" alt="' . $product_name . '" width="40" height="52" border="1"/></td>'; $cartOutput .='<td>' . $details . '</td>'; $cartOutput .='<td><form action="cart.php" method="post"> <input name="quantity" type="text" value="' . $each_item['quantity'] . '" size="1" maxlength="3" /> <input name="adjustBtn' . $item_id . '" type="image" value="change" src="../images/buttons/button_save.gif"/> <input name="item_to_adjust" type="hidden" value="' . $item_id . '" /> </form></td>'; //$cartOutput .='<td>' . $each_item['quantity'] . '</td>'; $cartOutput .='<td>' . $price . '</td>'; $cartOutput .='<td>' . $pricetotal . '</td>'; $cartOutput .='<td><form action="cart.php" method="post"> <input name="deleteBtn' . $item_id . '" type="image" value="X" src="../images/buttons/button_delete.gif"/> <input name="index_to_remove" type="hidden" value="' . $i . '" / ></form></td>'; $cartOutput .='</tr>'; $i++; } $cartTotal="<div align='right'>Your Total is KSHs. ".$cartTotal."</div>"; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Your Cart</title> <link rel="stylesheet" href="../style/style.css" type="text/css" media="screen"/> </head> <body> <div align="center" id="mainWrapper"> <?php include_once("../templates/template_header3.php");?> <div id="pageContent"> <div style="margin:24px;text-align:left;"> <br/> <table width="100%" border="1" cellspacing="0" cellpadding="6"> <tr bgcolor="#00FF66" align="center"> <td width="17%" bgcolor="#5BD7D7"><strong>Product</strong></td> <td width="50%" bgcolor="#5BD7D7"><strong>Product Details</strong></td> <td width="7%" bgcolor="#5BD7D7"><strong>Quantity</strong></td> <td width="9%" bgcolor="#5BD7D7"><strong>Unit Price<br/>(KHSs.)</strong></td> <td width="10%" bgcolor="#5BD7D7"><strong>Total<br/>(KHSs.)</strong></td> <td width="7%" bgcolor="#5BD7D7"><strong>Remove</strong></td> </tr> <?php echo $cartOutput; ?> <!-- <tr> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> --> </table> <?php echo $cartTotal; ?><br/> <br/> <a href="cart.php?cmd=emptycart">Empty Your Cart</a><br/> <a href="cart.php?cmd=export">Export</a><br/> </div> <?php include_once("../templates/template_footer.php");?> </div> </body> </html> Hello, I had page that display results dynamically from the database, I need to export that html table to pdf. Any help? Thanks Hi Everyone, Firstly, I would like to make the following advanced apologies; Please accept my apologies if this question has been asked before. If you could point me to the correct place I would be happy to read there. Thus far I have been unable to locate anything which answers this directly. Please accept my apologies if I seem somewhat ignorant with regards to PHP. I am very much a beginner here, so go light on me . I have a piece of PHP code which basically runs an SQL Query, and prints the results in HTML onto an e-mail. It then e-mails the contents to me. I would prefer this to instead print the data into a CSV file and e-mail that file to me. Essentially what I want the code to do is: Run an SQL Query on an Oracle database, to retrieve data Export the data into a CSV file - this file does not need to be kept server side E-mail this CSV File The current code I have (which prints directly into the body of the e-mail) is as below. I assume it would not be too much more difficult to put this into CSV. As this relates to company data I have substituted certain references in the data below to remove potentially sensitive references. Code: [Select] <?php putenv("TNS_ADMIN=/usr/lib/oracle/11.2/client64"); require_once "DB.php"; @$DB = DB::connect('oci8://#####:#####@domain'); if (DB::isError($DB)) { echo 'Cannot connect to database: ' . $DB->getMessage(); } else { $fromdate = strtotime("last Monday"); $todate = strtotime("last Sunday"); if($todate < $fromdate) { // subtract another week $fromdate = strtotime("-1 weeks", $fromdate); } $fromdate = date('d/m/Y', $fromdate); $todate = date('d/m/Y', $todate); $body = ""; $body .= "<h3>For period " . $fromdate . " to " . $todate . "</h3>\n"; $query = "SELECT DCONSUMER, DDATE, DTYPE, DAMT/100 AS DAMT, SUBSTR(DWHO,0,INSTR(DWHO,':')-1) AS DWHO, DREF, DEXTDESC FROM UTILITY.DEBT WHERE SUBSTR(DWHO,0,INSTR(DWHO,':')-1) IN ( SELECT ID FROM UTILITY.USERS WHERE USE_SEN = 'MANAGER' ) AND DTYPE NOT IN (CHG1',CHG2','CHG3','CHG4') AND DDATE >= TO_DATE('".$fromdate."','dd/mm/yyyy') AND DDATE <= TO_DATE('".$todate."','dd/mm/yyyy') ORDER BY DAMT"; $result = $DB->query($query); $body .= "<table cellspacing='3'\n"; $body .= "<font size='2'>\n"; $body .= "<tr><th bgcolor='#AAAAAA'>CONSUMER</th><th bgcolor='#AAAAAA'>DATE</th><th bgcolor='#AAAAAA'>TYPE</th><th bgcolor='#AAAAAA'>AMT</th><th bgcolor='#AAAAAA'>WHO</th><th bgcolor='#AAAAAA'>REF</th><th bgcolor='#AAAAAA'>EXTDESC</th></tr>\n"; while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { $body .= "<tr><td>" . $row['DCONSUMER'] . "</td><td>" . $row['DDATE'] . "</td><td>" . $row['DTYPE'] . "</td><td>" . $row['DAMT'] . "</td><td>" . $row['DWHO'] . "</td><td>" . $row['DREF'] . "</td><td>" . $row['DEXTDESC']. "</td></tr>\n"; } $body .= "</font>\n"; $body .= "</table>\n"; #print $body; $to = ""; $subject = "Automated Query: (".$fromdate." to ".$todate.")"; $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; $headers .= 'From: Automated Scripts <root@iron.domain.local>' . "\r\n"; $headers .= 'To: myemail@domain.com' . "\r\n"; $headers .= 'Cc: otheremail@domain.com' . "\r\n"; mail($to, $subject, $body, $headers); } ?> Your assistance is much appreciated. Cheers, Sean am using this code Code: [Select] <?php require_once 'PHPExcel.php'; require_once 'PHPExcel/IOFactory.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Matthew Casperson") ->setLastModifiedBy("Matthew Casperson") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes."); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', 'Hello') ->setCellValue('B2', 'world!') ->setCellValue('C1', 'Hello') ->setCellValue('D2', 'world!'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="test.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); ?> but excelsheet contain following error............. <b>Fatal error</b>: Class 'ZipArchive' not found in <b>C:\Program Files\xampp\htdocs\immigration\PHPExcelSourceCode\PHPExcel\Writer\Excel2007.php</b> on line <b>224</b><br /> but excelsheet contain this error............. please help how to fix it? I am new in programing. would like some help this is my cart.php file and i would like to export the cart contents into excel...after a user presses a submit button.. Please Please help...thanks |
|||||||