|
PHP - How To Download Two Mysql Tables In Same Excel Sheet Using Php
I have a PHP code to download one mysql table to excel sheet.what i want to do is download 2 tables in to same excel sheet.those tables are should be in different excel worksheet.please help me. this code work fine.it's download one table
<?php
ob_start();
session_start();
include('dbconnection.php');
$usr= $_SESSION['fname'];
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=exported-data.csv');
$select_table=mysql_query("select * from regfarmer WHERE ffname='$usr'");
$rows = mysql_fetch_assoc($select_table);
if ($rows)
{
getcsv(array_keys($rows));
}
while($rows)
{
getcsv($rows);
$rows = mysql_fetch_assoc($select_table);
}
function getcsv($no_of_field_names)
{
$separate = '';
foreach ($no_of_field_names as $field_name)
{
if (preg_match('/\\r|\\n|,|"/', $field_name))
{
$field_name = '' . str_replace('', $field_name) . '';
}
echo $separate . $field_name;
$separate = ',';
}
echo "\r\n";
}
?>
Similar TutorialsHello, well I guess my question is pretty clear. How would I be able to read data out of an Excel sheet? F.E. to allow my users to simply upload an .xls file to help them avoid the coding and such. Of course, I know how to create the tables etc, the main problem is just getting that data. Also, I am uncertain if uploading an .xls file is a lot harder/totally done differently then images, and if so.. what is the best way to do this? P.s. I do want all the fancy equations to be included, just normally entered data (which would just follow some sort of a protocol). Thanks in advance. I need some help and hopefully will respond. I'm trying to put some information to the second row of the excel sheet once extracted. Can somebody help me with this script? I'm trying to move the address line to the second row. Thanks. hi, I'm using MIME to send a html email and i wanted to attach an excel sheet that will be generated from My SQL database to that email. can you help me on how can i generate and attach that excel sheet to emails that my script sends? thank you Hello to all Here is the headache: I want to make a website. In this website there is a table with multiple columns and rows. The cells of the table are editable by the user on the web. After filling the cells with numbers, the user clicks a button and the table is sent to an existing (but blank) excel spreadsheet. I have the website and i am trying to understand the code beneath this. This is for my graduation paperwork and the schedule is very tight. If you can teach me how to this I would be most grateful: How to make a php script that transfers all the data in a table to an excel sheet? I am using ODBC connection and sql language like this one: <?php $ligacao=odbc_pconnect("test","","") or die ("error"); $sql="INSERT INTO [sheet$](F1) VALUES ('value1')"; $resultado=odbc_exec($ligacao,$sql); echo $resultado; odbc_close($ligacao); ?> Thank you a lot! Hi there, I have a cronjob for PHP which generates a table with dynamic data and this table is exported to excel when run. When i have one table it downloads one excel file. I want to download multiple excel files using a for loop. Please Help... Any suggestions will be helpful.. Thank you, Regards, Rohit Does anyone how to make those little buttons where you can have the option of downloading a table on a website to a pdf or excel document? does anyone know what I am talking about? what I am trying to do is use this php script to load the data being submitted in the html form into my database and then populate the database into an excel (xls) file and then e-mail it to my address. Everything works great it populates into the database and creates the xls file perfect. But it is wanting me to download the file. What can I add to the script to have it e-mail the file to my e-mail address INSTEAD of downloading it. Code: [Select] <?php define('DB_NAME', 'database'); define('DB_USER', 'username'); define('DB_PASSWORD', 'password'); define('DB_HOST', 'hostname'); $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db(DB_NAME, $link); if (!$db_selected) { die('Can\'t use ' . DB_NAME . ': ' . mysql_error()); } $value1 = $_POST['groupname']; $value2 = $_POST['name']; $value3 = $_POST['address']; $value4 = $_POST['city']; $value5 = $_POST['state']; $value6 = $_POST['zip']; $value7 = $_POST['homephone']; $value8 = $_POST['cellphone']; $value9 = $_POST['email']; $value10 = $_POST['age']; $value11 = $_POST['maritalstatus']; $value12 = $_POST['income']; $value13 = $_POST['contact1']; $value14 = $_POST['contact2']; $value15 = $_POST['contact3']; $value16 = $_POST['date1']; $value17 = $_POST['date2']; $value18 = $_POST['date3']; $sql = "INSERT INTO clients (groupname, name, address, city, state, zip, homephone, cellphone, email, age, maritalstatus, income, contact1, contact2, contact3, date1, date2, date3) VALUES ('$value1', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10', '$value11', '$value12', '$value13', '$value14', '$value15', '$value16', '$value17', '$value18')"; if (!mysql_query($sql)) { die('Error: ' . mysql_error()); } mysql_close(); mysql_connect('hostname', 'username', 'password'); mysql_select_db('database'); $sql = "SELECT `groupname` AS `Group`, `name` AS `Customer Name`, `address` AS `Address`, `city` AS `City`, `state` AS `State`, `zip` AS `Zip Code`, `homephone` AS `Home Phone`, `cellphone` AS `Cell Phone`, `email` AS `E-Mail`, `age` AS `Age Group`, `maritalstatus` AS `Marital Status`, `income` AS `Household Income`, `contact1` AS `Contact VIA`, `contact2` AS `Contact VIA`, `contact3` AS `Contact VIA`, `date1` AS `1st Date`, `date2` AS `2nd Date`, `date3` AS `3rd Date` FROM fundtour_info.clients clients"; // Query Database $result=mysql_query($sql); $filename = 'file.xls'; // Send Header header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download");; header("Content-Disposition: attachment;filename=$filename"); header("Content-Transfer-Encoding: binary "); // XLS Data Cell xlsBOF(); xlsWriteLabel(0,0,"Group"); xlsWriteLabel(0,1,"Name"); xlsWriteLabel(0,2,"Address"); xlsWriteLabel(0,3,"City"); xlsWriteLabel(0,4,"State"); xlsWriteLabel(0,5,"Zip Code"); xlsWriteLabel(0,6,"Home Phone"); xlsWriteLabel(0,7,"Cell Phone"); xlsWriteLabel(0,8,"E-mail Address :"); xlsWriteLabel(0,9,"Age Group"); xlsWriteLabel(0,10,"Marital Status"); xlsWriteLabel(0,11,"Income"); xlsWriteLabel(0,12,"Contact Via"); xlsWriteLabel(0,13,"Dates"); $xlsRow = 1; while(list($groupname,$name,$address,$city,$state,$zip,$homephone,$cellphone,$email,$age,$maritalstatus,$income,$contact1, $contact2, $contact3,$date1, $date3, $date3)=mysql_fetch_row($result)) { ++$i; xlsWriteLabel($xlsRow,0,"$groupname"); xlsWriteLabel($xlsRow,1,"$name"); xlsWriteLabel($xlsRow,2,"$address"); xlsWriteLabel($xlsRow,3,"$city"); xlsWriteLabel($xlsRow,4,"$state"); xlsWriteLabel($xlsRow,5,"$zip"); xlsWriteLabel($xlsRow,6,"$homephone"); xlsWriteLabel($xlsRow,7,"$cellphone"); xlsWriteLabel($xlsRow,8,"$email"); xlsWriteLabel($xlsRow,9,"$age"); xlsWriteLabel($xlsRow,10,"$maritalstatus"); xlsWriteLabel($xlsRow,11,"$income"); xlsWriteLabel($xlsRow,12,"$contact1, $contact2, $contact3"); xlsWriteLabel($xlsRow,13,"$date1, $date3, $date3"); $xlsRow+++; } xlsEOF(); exit(); function xlsBOF() { echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); return; } function xlsEOF() { echo pack("ss", 0x0A, 0x00); return; } function xlsWriteNumber($Row, $Col, $Value) { echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); echo pack("d", $Value); return; } function xlsWriteLabel($Row, $Col, $Value ) { $L = strlen($Value); echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); echo $Value; return; } ?> Thanks for any help
create table mimi (mimiId int(11) not null, mimiBody varchar(255) );
<?php
//connecting to database
include_once ('conn.php');
$sql ="SELECT mimiId, mimiBody FROM mimi";
$result = mysqli_query($conn, $sql );
$mimi = mysqli_fetch_assoc($result);
$mimiId ='<span>No: '.$mimi['mimiId'].'</span>';
$mimiBody ='<p class="leading text-justify">'.$mimi['mimiBody'].'</p>';
?>
//what is next?
i want to download pdf or text document after clicking button or link how to do that Here is function that backup mysql database. It's working fine, but I want to download file instead of creating it. /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file $date= date("d-M-Y_h-i"); $handle = fopen('Backup-'.$date.'.sql','w+'); fwrite($handle,$return); fclose($handle); } Usage: Quote backup_tables('HOST','USER','PASS','TABLE'); But this will create backup file on server. I also tried to add following code in function (at the end): echo $return; header('Content-type: text/plain'); header("Content-Disposition: attachment; filename=\"$filename\""); How to just download file, without showing it on screen instead of using fwrite/fclose function? 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 This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization) users: id / username / password / realname / access_level users_access_level: access_level / access_name What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries. Example row for users: 1234 / tmac / password / tmac / 99 Example row for users_access_level: 99 / Admin Using the examples above, I would want the output to appear as such: Username: Access Name: Tmac Admin I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction. The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself. Thanks for reading! Hello, I want to export data from php-mysql to excel in a particular cell. 1. Get the data from php-Mysql and export data to excel sheet. I already create a template excel sheet format.I want to export data from mysql to a particular cell. Sorry I can't able to attach my template excel sheet. 2. Is it possible to export image? Thanks in advance This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330652.0 Hi there, I got some good advice from Harristweed the other day about how to export from Mysql to excel, so that each field appears in a different column in excel. I expanded the code Harristweed gave me to cover all fields in my database, and all went well until the 2nd last field, when I hit a brick wall. Unlike previous fields, which were based on info inputted via radio buttons, dropdown lists, or text boxes, the field causing problems was based on data that came from a text area on the web form. A few tests revealed that the problem was related to when a user pressed the "Enter" key while filling in that field, to begin a new paragraph. When this happens, it would go into the database fine. However, when I used the code to export it to excel, anything after the "Enter" key had been pressed would appear in a new cell on a new row, thereby putting the table out of alignment. I could get around this by rewording the question into a few questions which each use a text box rather than a text area box. However, if there is a way to get around this by tweaking the php code, I'd love to know. The field with which I'm having the problem is the one called "experience". Thanks for your time, Dave <?PHP $db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD"); mysql_select_db("MYDATABASE",$db); $query="SELECT * FROM applications2010"; $result=mysql_query($query); $header=" date\t firstname\t lastname\t sex\t dobday\t dobmonth\t dobyear\t streetaddress\t suburb\t state\t postcode\t country\t preferredphone\t secondphone\t thirdphone\t emailaddress\t emconname\t emconphone\t experience\t "; while($row = mysql_fetch_assoc($result)) { if(empty($row[date]))$row[date]=" "; if(empty($row[firstname]))$row[firstname]=" "; if(empty($row[lastname]))$row[lastname]=" "; if(empty($row[sex]))$row[sex]=" "; if(empty($row[dobday]))$row[dobday]=" "; if(empty($row[dobmonth]))$row[dobmonth]=" "; if(empty($row[dobyear]))$row[dobyear]=" "; if(empty($row[streetaddress]))$row[streetaddress]=" "; if(empty($row[suburb]))$row[suburb]=" "; if(empty($row[state]))$row[state]=" "; if(empty($row[postcode]))$row[postcode]=" "; if(empty($row[country]))$row[country]=" "; if(empty($row[preferredphone]))$row[preferredphone]=" "; if(empty($row[secondphone]))$row[secondphone]=" "; if(empty($row[thirdphone]))$row[thirdphone]=" "; if(empty($row[emailaddress]))$row[emailaddress]=" "; if(empty($row[emconname]))$row[emconname]=" "; if(empty($row[emconphone]))$row[emconphone]=" "; if(empty($row[experience]))$row[experience]=" "; $line = ''; $line .= "$row[date]\t $row[firstname]\t $row[lastname]\t $row[sex]\t $row[dobday]\t $row[dobmonth]\t $row[dobyear]\t $row[streetaddress]\t $row[suburb]\t $row[state]\t $row[postcode]\t $row[country]\t $row[preferredphone]\t $row[secondphone]\t $row[thirdphone]\t $row[emailaddress]\t $row[emconname]\t $row[emconphone]\t $row[experience]\t "; $data .= trim($line)."\n"; } $data = str_replace("\r","",$data); if ($data == "") { $data = "\n(0) Records Found!\n"; } header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=filename=".date("d-m-Y")."-export.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data";exit; ?> Hi guys, I am trying to create a coding which allows users to upload and download files into mysql server, but I came across some errors. The coding itself works fine on the surface, but when I uploaded the file into mysql and download them, the contents of the downloaded file was different from the original file that was upload. One good example would be when I upload txt file or doc file. I was stuck on this for quite a few days without any leads. Here are the codes: index.html <form action="add_file.php" method="post" enctype="multipart/form-data"> <input type="file" name="uploaded_file"><br> <input type="submit" value="Upload file"> </form> <p> <a href="list_files.php">See all files</a> </p> add_file.php <?php // Check if a file has been uploaded if(isset($_FILES['uploaded_file'])) { // Make sure the file was sent without errors if($_FILES['uploaded_file']['error'] == 0) { // Connect to the database $dbLink = new mysqli('localhost', 'root', 'pwd', 'myTable'); if(mysqli_connect_errno()) { die("MySQL connection failed: ". mysqli_connect_error()); } // Gather all required data $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']); $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']); $data = $dbLink->real_escape_string(file_get_contents($_FILES ['uploaded_file']['tmp_name'])); $size = intval($_FILES['uploaded_file']['size']); // Create the SQL query $query = " INSERT INTO `file` ( `name`, `mime`, `size`, `data`, `created` ) VALUES ( '{$name}', '{$mime}', {$size}, '{$data}', NOW() )"; // Execute the query $result = $dbLink->query($query); // Check if it was successfull if($result) { echo 'Success! Your file was successfully added!'; } else { echo 'Error! Failed to insert the file' . "<pre>{$dbLink->error}</pre>"; } } else { echo 'An error accured while the file was being uploaded. ' . 'Error code: '. intval($_FILES['uploaded_file']['error']); } // Close the mysql connection $dbLink->close(); } else { echo 'Error! A file was not sent!'; } // Echo a link back to the main page echo '<p>Click <a href="index.html">here</a> to go back</p>'; ?> list_files.php <?php // Connect to the database $dbLink = new mysqli('localhost', 'root', 'pwd', 'myTable'); if(mysqli_connect_errno()) { die("MySQL connection failed: ". mysqli_connect_error()); } // Query for a list of all existing files $sql = 'SELECT `id`, `name`, `mime`, `size`, `created` FROM `file`'; $result = $dbLink->query($sql); // Check if it was successfull if($result) { // Make sure there are some files in there if($result->num_rows == 0) { echo '<p>There are no files in the database</p>'; } else { // Print the top of a table echo '<table width="100%"> <tr> <td><b>Name</b></td> <td><b>Mime</b></td> <td><b>Size (bytes)</b></td> <td><b>Created</b></td> <td><b> </b></td> </tr>'; // Print each file while($row = $result->fetch_assoc()) { echo " <tr> <td>{$row['name']}</td> <td>{$row['mime']}</td> <td>{$row['size']}</td> <td>{$row['created']}</td> <td><a href='get_file.php?id={$row['id']}'>Download</a></td> </tr>"; } // Close table echo '</table>'; } // Free the result $result->free(); } else { echo 'Error! SQL query failed:'; echo "<pre>{$dbLink->error}</pre>"; } // Close the mysql connection $dbLink->close(); ?> get_file.php <?php // Make sure an ID was passed if(isset($_GET['id'])) { // Get the ID $id = intval($_GET['id']); // Make sure the ID is in fact a valid ID if($id <= 0) { die('The ID is invalid!'); } else { // Connect to the database $dbLink = new mysqli('localhost', 'root', 'pwd', 'myTable'); if(mysqli_connect_errno()) { die("MySQL connection failed: ". mysqli_connect_error()); } // Fetch the file information $query = " SELECT `mime`, `name`, `size`, `data` FROM `file` WHERE `id` = {$id}"; $result = $dbLink->query($query); if($result) { // Make sure the result is valid if($result->num_rows == 1) { // Get the row $row = mysqli_fetch_assoc($result); // Print headers header("Content-Type: ". $row['mime']); header("Content-Length: ". $row['size']); header("Content-Disposition: attachment; filename=". $row['name']); // Print data echo $row['data']; } else { echo 'Error! No image exists with that ID.'; } // Free the mysqli resources @mysqli_free_result($result); } else { echo "Error! Query failed: <pre>{$dbLink->error}</pre>"; } @mysqli_close($dbLink); } } else { echo 'Error! No ID was passed.'; } ?> I am not too sure where the error could be but I suspect that it could be somewhere at the add_file or get_file. Seriously hope someone could help me with this bug and thanks for the trouble. Regards Jasmine Hiya guys After getting everything else working how I expected, I'm sort of struggling on the last step. Downloading I have an upload.php file that allows me to upload a file to Mysql, the fields available a upid - Primary id - Need to link this to the logged on user id name type size content The upload works perfectly Can anyone help with implmenting it to the profile.php (1st page after login) On profile page I have: Welcome "username" from Session Dynamic Table display his user id, username and password at the moment, this will be changed as not needed tho. I am using the sessions MM_Username to pass from the login The table for Login looks like: id - primary username password I assume that if I can copy the ID from login and put it in ID in Upload and add colums to dynamic table to show the upload file, will this make that file only available to logged in user? Cheers Hi, i have a table which shows some mysql data, every entry has a checkbox to select individual entries, now i want to be able to export those selected entries into a xml or txt file, i tried this: Code: [Select] <?php if($_POST['exporttxt']){ for($i=0;$i<count($_POST['checkbox']);$i++){ $export_id = $checkbox[$i]; $sql = "SELECT * FROM table WHERE id='$export_id'"; $result = mysql_query($sql);} $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n"; if($result->num_rows > 0) { while($myrow = $result->fetch_assoc()) { $output .= "\t<row>\n"; foreach($myrow as $_name => $_value) { $output .= "\t\t<$_name>$_value</$_name>\n"; } $output .= "\t</row>\n"; } } $output .= "</root>"; } header('content-type: text/xml'); header('content-disposition: attachment; filename=data_export.xml'); echo $output; exit; ?> but that doesn't seem to work. Any hints ? I have a webpage where the candidates can attach their resumes and send to the admin.These attachments are saved in the mysql db as blob datatype.In another webpage the admin needs to download all this resumes and see the content. How will i code for that. Hi Guys, I have been contacted by a client who wants me to develop an application which performs the following functionality: Lets say the domain name is xyz.com . We provide an email of our domain to the person who signup on our website for our services. Lets say the email that is given to the customer is email@xyz.com The client wants to built PHP application in which if an email is sent to email@xyz.com it gets downloaded and is stored in the MySql Database. After the download there is going to be a set of algorithms which will decided whether to forward this email to the client or not on his email address. I have no idea how to accomplish this functionality. Any suggestions would be appreciated. Thanks Sadan Masroor. Hi, this is my first post:) pretty sure i will be posting here in the future. Anyway i am having trouble with a php script which downloads a file from a mysql database. This php script works for text files and should work for most files from what i understand. When downloading a text file, the whole file is downloaded from the server. When downloading an mp3 file, only 16kb are downloaded and the file does not play. When looking at the data in the database, it displays that the full file is there (correct amount of bytes, so there is nothing wrong with my upload php script). Does anyone have any suggestions as to what I can change to make this work? Code: [Select] <?php if(isset($_GET['id'])) { // if id is set then get the file with the id from database $link=mysql_connect('localhost', 'root', ''); @mysql_select_db('filemgr') or die ("<p>Could not connect to mysql!</p>"); $id = $_GET['id']; $query = "SELECT name, type, size, content " . "FROM upload WHERE id = '$id'"; $result = mysql_query($query) or die('Error, query failed'); list($name, $type, $size, $content) = mysql_fetch_array($result); header("Content-length: $size"); header("Content-type:$type"); header("Content-Disposition: attachment; filename=$name"); echo $content; mysql_close($link); exit; } ?> |
|||||||