|
PHP - Exporting Database Into A Sql File
How can I use PHP to export my database into a .sql file?
Similar TutorialsHi all, this is the code i'm using at the moment which is at the bottom of a webpage Code: [Select] // Open file export.csv. $f = fopen("export.csv", "w"); // Put all values from $out to export.csv. fputs($f, $out); fclose($f); $filename = "StatisticalAnalysis-" . gmdate("Y-m-d-H-i-s") . ".csv"; header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=" . $filename); readfile("export.csv"); The variable named $out is a long line of text in csv format. I've echoed that variable and it's all good. When a checkbox in a form further up the page is checked the code creates and fills in the $out variable and then carries out the code above. The problem i have is that when the csv save dialog comes up and the file is saved, all it contains is a copy of the html code which is on that page, not the info stored in the $out variable. Can anyone offer any advice on what the problem is? Cheers Hi, this is my first time here. i am trying to create an xls file from the data i get from mysql. here is the code i tried but i am not able to use it correctly. Code: [Select] ob_start(); include('config'); $datacat = mysql_query("SELECT * FROM `leads`") or die(mysql_error()); $name = mysql_fetch_assoc($datacat); $line1="Industry,Company Name,Officials Name,Job Title,Country,Direct Number Mobile Number,Switch Board Number,E-mail,Executive NamePitch Date,Call Back Date,Comments\t"; while($row = mysql_fetch_array($datacat, MYSQL_ASSOC)) { $line2= $name['industry'].",".$name['company_name'].",".$name['officials_name'].",".$name['job_title'].",".$name['country'].",".$name['direct_number_mobile_number'].",".$name['switch_board_number'].",".$name['email'].",".$name['executive_name'].",".$name['pitch_date'].",".$name['call_back_date'].",".$name['comment']."\t"."\n"; } $data="$line1\n$line2\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 get my webpage title in the top of xls file and all the fields in xls file are separated by commas, whereas i want them to be in proper tables. Help would be much appreciated. Hi people. Need some help if anyone could give a hand! I'm currently trying to incorporate an export to Excel feature on search returns on my web page. Here is how my searches are returned, so what I want now is when the user clicks on the 'Export to Excel' button it downloads the search results and opens it in the same format in Excel. I've tried using http://phpexcel.codeplex.com/ but can't understand how to use it properly, does anyone have any suggestions? I don't have any code written for the Export to Excel yet as it's just a submit button. How I see it in my head is the user clicks the button, it goes to a new page and performs a new query then downloads that but I don't no if thats right or not. Hello, guys i got some issue in my script for exporting data to CSV (exel for view) Its export all data in one collumn, but i need to export each data in separeta column like. a B a1 ID ID2 But i got there this: a B a1 ID""ID2 Here is the script: Code: [Select] <?php class WDealAdminExport extends UWidgetWorklet { public function accessRules() { return array( array('allow', 'roles' => array('company')), array('deny', 'users'=>array('*')) ); } public function taskConfig() { $list = wm()->get('deal.admin.coupon'); $_GET[$list->modelClassName] = $_POST[$list->modelClassName]; $list->init(); $dataProvider = $list->dataProvider(); $dataProvider->pagination = false; $csv = array( array( $this->t('ID'), $this->t('Order ID'), $this->t('Deal ID'), $this->t('User'), $this->t('Status'), ) ); foreach($dataProvider->data as $data) { $row = array( "#".$data->couponId(), $data->orderId, $data->dealId, $data->user->email." [".$data->user->getName(true)."]", $data->status==1 ? ($data->deal->isExpired() ? $this->t('Expired') : $this->t('Available')) : $this->t('Used') ); if($_POST['charset'] != 'utf-8') { $row[3] = iconv('utf-8', $_POST['charset'], $row[3]); $row[4] = iconv('utf-8', $_POST['charset'], $row[4]); } $csv[] = $row; } $contents = $this->generateCSV($csv); $this->send($contents); } public function taskGenerateCSV($src) { $csv = ''; foreach($src as $row) { foreach($row as $item) $csv.= $this->escape($item).$this->param('delimiter'); $csv = rtrim($csv,$this->param('delimiter'))."\r\n"; } return $csv; } public function taskEscape($value) { return '"'.str_replace('"','""',$value).'"'; } public function taskSend($contents) { $content_type = "application/octet-stream"; $filename = 'export.csv'; // disable browser caching header('Cache-control: private'); header('Pragma: private'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Content-Type: '.$content_type); header('Content-Transfer-Encoding: binary'); header('Content-Length: '.strlen($contents)); header('Content-Disposition: attachment;filename="'.$filename.'"'); echo $contents; app()->end(); } } Hello,
I created a web page that executes request from mysql and prints it on web page. I also created a export submit button to export mysql query in csv format file. When I select values and then click on export buttons, I get nothing and page gets refreshed.
Here is the link of my site: rahil.me/index.php
Here is the code:
These are the buttons:
<input type="submit" name="search" value="Search"> <input type="submit" name="export" value="Export" />PHP code:
if (isset($_POST['export'])) {
if (empty($_POST['service'])) {
echo "Please select service in dropdown" . "</br>";
}
else {
$service = $_POST['service'];
}
if (empty($_POST['environment'])) {
echo "Please select Environment in dropdown" . "</br>";
}
else {
$env = $_POST['environment'];
}
if ((!empty($service)) && (!empty($env))) {
$sql="SQLQUERY REMOVE";
if (!mysqli_query($con,$sql)) {
die('Error: ' . mysqli_error($con));
}
$result = mysqli_query($con,$sql);
/*
* send response headers to the browser
* following headers instruct the browser to treat the data as a csv file called export.csv
*/
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');
/*
* output header row (if atleast one row exists)
*/
$row = mysqli_fetch_assoc($result);
if ($row) {
echocsv(array_keys($row));
}
/*
* output data rows (if atleast one row exists)
*/
while ($row) {
echocsv($row);
$row = mysqli_fetch_assoc($result);
}
/*
* echo the input array as csv data maintaining consistency with most CSV implementations
* - uses double-quotes as enclosure when necessary
* - uses double double-quotes to escape double-quotes
* - uses CRLF as a line separator
*/
function echocsv($fields)
{
$separator = '';
foreach ($fields as $field) {
if (preg_match('/\\r|\\n|,|"/', $field)) {
$field = '"' . str_replace('"', '""', $field) . '"';
}
echo $separator . $field;
$separator = ',';
}
echo "\r\n";
}
}
}
Edited by raymak, 25 May 2014 - 02:04 PM. hello guys.. i want help from u . i am displaying a table of 5 rows and 5 columns . i want to get a pdf copy of it dynamically. i want to re-use it again for my invoice also . covert in into pdf and then print that file. can u provide a sample code of how to covert it into pdf . (same as exporting into excel) hello I wrote a php code which will get the records from database and save them in excel. <?php $t = strtotime($date1); $t1 = strtotime($date2); $t2 = $t1 - $t; $t3 = floor($t2 /60/60/24); $j = 0; while ($j <= $t3 ) { if ($j == 0) { $t = $t - 86400; } $t = $t + 86400; $today = date('Y-m-d',$t); mysql_select_db("reporting", $connect); $result1 = mysql_query("SELECT * from report where date = '$today' "); $row1 = mysql_fetch_assoc($result1); ?><tr><?php foreach($row1 as $k=>$v){ ?> <td><?php echo $v; $op1 = $row[$v]; ?></td> <?php } ?></tr><?php $j++; } ?> </table> <body> </html> <?php mysql_close($connect); header("Content-type: application/vnd.ms-excel"); header('Content-disposition: attachment; filename = "report" '); exit; ?> Problem is I am able to retrieve only 2 rows to excel file. If the number of rows are more than 2 it displays a table. can any one help me Hi everyone, I got a bit of code off the web and did some tweaking. I want to use it to export data from a mysql table to excel. I do a website for a friend and have been exporting it myself and emailing it to him. However, I thought it'd be easier if I could just direct him to a link so he could get it whenever. Anyway, the code below works pretty well...the only problem being that it exports all data to the first column of the excel file. There are commas between all fields, so it is possible to use the 'text to columns' feature in excel to separate teh data into separate columns. However, if there is a way of fixing the code that would negate the need to have to do this, it would be much appreciated. Here goes: <?php $db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD"); mysql_select_db("MYDATABASE",$db); $query="SELECT * FROM MYTABLENAME"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $csv_output = "firstname,lastname,emailaddress,crew_name_001"; $csv_output .= "\r\n"; $i=0; while ($i < $num) { $id=mysql_result($result,$i,"id"); $firstname = mysql_result($result,$i,"firstname"); $lastname = mysql_result($result,$i,"lastname"); $emailaddress = mysql_result($result,$i,"emailaddress"); $crew_name_001 = mysql_result($result,$i,"crew_name_001"); $csv_output .= "$firstname,$lastname,$emailaddress,$crew_name_001\n"; ++$i; } $mode="xls"; $type="excel"; header("Content-type: text/x-csv"); header("Content-disposition: attachment; filename=".date("d-m-Y")."-export.xls"); print $csv_output; exit; ?> Thanks for your time, Dave I have a script that will export data to excel. After the export, I need the page to reload showing status information, but it does not reload. What could be causing an issue like this? I think that because the export forced the download file dialogue to open, those headers may be preventing the form from reloading properly, but how, why, and what can be done to resolve the issue? I want to make a time limit in-between posts on my forum. Should I make a table in the database to store the seconds, or read it/write via a file? Hi PHP freaks!!
I have manage to make a script to upload file in database and its working. I will share to you the codes so for other viewers and readers to use also.
if($result){
if($_FILES['LRCard']['name'] != ""){
$filename = $_FILES['LRCard']['name'];
$ext = strrchr($filename,".");
$LRCardname = $student_id;
$LRCardname .="_". $filename;
if($ext ==".jpg" || $ext ==".jpeg" || $ext ==".JPG" || $ext ==".JPEG" || $ext ==".gif" || $ext ==".GIF"){
$size = $_FILES['LRCard']['size'];
if($size > 0 && $size < 5000000){
$archive_dir = "LRCards";
$userfile_tmp_name = $_FILES['LRCard']['tmp_name'];
if(move_uploaded_file($userfile_tmp_name, "$archive_dir/$LRCardname")){
/*
if LRC is successfully uploaded then LRC is stored in database.
*/
mysql_query("update student_information set LRCard='$LRCardname' where student_id='$student_id'", $link_id);
$flag = "success";
if(mysql_error()!=null){
die(mysql_error());
}
}
else{
if(file_exists('LRCard/' . $LRCardname)) {
unlink('LRCards/' . $LRCardname);
}
rollbackData();
}
}
else{
if(file_exists('LRCards/' . $LRCardname)) {
unlink('LRCard/' . $LRCardname);
}
rollbackData();
die("You can upload LRCard of 5 MB size only. Please, try again.");
}
}
else{
if(file_exists('LRCards/' . $LRCardname)) {
unlink('LRCards/' . $LRCardname);
}
rollbackData();
die("You can upload LRCard of .jpg, .jpeg, .gif extensions only. Please, try again. ");
}
}
}
else{
$flag="error";
}
if($flag == "success"){
mysql_query(" COMMIT ");
$flag="success";
if(mysql_error() != null){
die(mysql_error());
}
}
Now, my problem is how to make a Php script to DOWNLOAD this uploaded file considering the
1. file path
2. file name
3. file extension.
4. student_id (to determine the specific file from a specific student)
My plan is to make a download button which hyperlink to download.php and after clicking that button the specified file to a specific student id will automatically be downloaded by the browser.
how do I use a connection file (connection.php) in multiple programs? I think include? Hi, I use MySQL for real projects of course... but, I like to challenge myself and I was wondering how people make advance applications with Flat File databases... My questions are... >Display >Reading The Flat File Data >Sorting it Alphabetically For Display >How to create a WHERE clause affect with the data >Storage >Adding Records >Deleting Records >Editing Records I went over a lot of this on my own, but I'm stuck on certain parts because google only has so much on Flat File Databases... also how would u secure a Flat File Database from someone just simply direct linking to it and viewing it? Hey anybody can please guide me where I m wrong in this php code please reply here is the code Only variables should be passed by reference on line 3 in that function area function get_file_extension($file_name) main error I guess is the explode one Aand yeah I checked adding $file_name = $_FILES['fld']; before the function is started <?php require("include/dbconn.php"); //$username = mysql_real_escape_string($_POST['username1']); function get_file_extension($file_name) { return end(explode('.',$file_name)); } function errors($error){ if (!empty($error)) { $i = 0; while ($i < count($error)){ $showError.= '<div class="msg-error">'.$error[$i].'</div>'; $i ++;} return $showError; }// close if empty errors } // close function if (isset($_POST['submit'])){ $username = "Sunny"; $date1 = date("d m y"); mysql_select_db("deals") or die(mysql_error()); if($username == "") { //header("location:newnewuser1.php"); echo "Username empty"; //header("location:newnewuser1.php"); } else { function createRandomPassword() { $chars = "abcdefghijkmnopqrstuvwxyz023456789"; srand((double)microtime()*1000000); $i = 0; $pass = '' ; while ($i <= 7) { $num = rand() % 33; $tmp = substr($chars, $num, 1); $pass = $pass . $tmp; $i++; } return $pass; } // Usage $password = createRandomPassword(); //$password = mysql_real_escape_string($_POST['Password']); $date = strtotime("+2hours"); $date1 = date("d-m-y",$date); if(get_file_extension($_FILES["fld"]["name"])!= 'csv') { $error[] = 'Only CSV files accepted!'; echo "Wrong Input"; }//get_file_extension if (!$error) { $tot = 0; $handle = fopen($_FILES["fld"]["tmp_name"], "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { for ($c=0; $c < 1; $c++) { try { mysql_query("INSERT INTO newusers (email,password,location,company,name,contact,sourcename,date)VALUES('".mysql_real_escape_string($data[0])."','$password','".mysql_real_escape_string($data[1])."', '".mysql_real_escape_string($data[2])."','".mysql_real_escape_string($data[3])."','".mysql_real_escape_string($data[4])."','$username1','$date1')"); $tot++; } catch(Exception $e) { PRINT 'ERROR:' +$e; } }//for }//while fclose($handle); }// end no error }//close if isset upfile } ?> and this is the form code <form name="f1" action="" method="POST" enctype="multipart/form-data"> <table><tr><td><input type="file" value="Browse" name="fld" onselect="CheckExtension(fld)"><br/></td><td> </td></tr><tr><td>Username :<input type="text" name="username1" value="" id="user" onfocus="validateForm1()"></td> <td><input type="submit" name="submit" value="submit" id="submit"></td></tr></table> </form> Please help me I m stuck in this quite badly Please [attachment deleted by admin] I would like to be able to add a yes/no to a field in a 'user' database when a logged in user clicks on a link to download a file. The site I have built stores docs and applications. and my client would like to know which user has downloaded a certain application (clicked on a button to 'download' and installer file. Is this possible? Hey all, I've written a php search feature for a mysql database. The search returns a file name like sample.gif, how would I go about displaying the actual image instead? Since the images all have the same location could I have the search return the string into a variable then make the whole thing a link? Thanks. Where is the best to store the list of categories and menu? Obviously it is better to reduce mysql queries. From a plain list in file, we can create desirable list (menu) from an array. This is quite faster (though SELECT from an indexed table is fast too; but I do know if it is as fast as reading from a php file). Any idea how to code this to create the menu faster ? <!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=iso-8859-1" /> <title>Import a CSV File with PHP & MySQL</title> </head> <body> <form action="" method="post" enctype="multipart/form-data" name="csv" id="csv"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <input type="submit" name="Submit" value="Submit" /> </form> </body> As per the client requirement he wants to read words from a file and want to insert them in database one by one . ie a confirmation is required whether he wants to insert is or not. I have developed the thing but the problem is when i am retrieving the words it asks for all the words. i will show u wat the problem is. My main query is i want to pause the exectution until user's input. Code: [Select] <script type="text/javascript" src="jquery.js"></script> <script type"text/javascript"> function show() { document.getElementById('form#submit').fadeIn(); alert("Abhinav"); } $(document).ready(function(){ $("form#submit").submit(function() { // we want to store the values from the form input box, then send via ajax below var word = $('#word').attr('value'); var synonym = $('#synonym').attr('value'); $.ajax({ type: "POST", url: "ajax.php", data: "word="+ word + "&synonym=" + synonym, success: function(){ $('form#submit').hide(); //$('form#submit :input').val(""); $('div.success').fadeIn(); } }); return false; }); }); </script> <?php $file=fopen("welcome.txt","r") or exit("Unable to open file!"); @$word=""; @$c=""; while (!feof($file)) { $c=fgetc($file); if(strcmp($c," ")==0) { echo "". "<form id='submit' method='post' name='submit' action='' >". "<fieldset> ". "<legend>Enter Information</legend> ". " <label for='word'>Word:</label> ". "<input id='word' class='text' name='word' size='20' type='text' value='$word' > ". " <label for='Synonym'>Synonym:</label> ". "<input id='synonym' class='text' name='synonym' size='20' type='text'> ". " <button class='button positive' type='submit'> Add Client </button> ". " </fieldset> ". " </form> ". "<div class='success' style='display:none;'>Client has been added.</div> ". "</div>"; $word=""; } else $word=$word.$c; } fclose($file); ?> Now what i want is after user clicks add client then only next insertion box must be shown. please check the attached image to see the output. Guys pls help me over this. Thnx in advance I have a file in a mysql database and when I echo it shows all of the php code from the file in the database on the page when it is echoed. How can I fix this problem. I notice a lot of websites coded from php have things like ?go=register or ?page=login something like that. Could you guys tell me how to do things like that. Im pretty sure when people do things like ?go=register the register is the code of a file in a mysql database. |
|||||||