|
PHP - Mysql To Excel....problem With Text Areas And Paragraphs.
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; ?> Similar TutorialsHi guys, I am very simply inserting text into a table with php and mysql, and outputting it, it all works fine but when I use paragraphs when inserting it, and then try to ouput it, all the paragraphs are gone and it looks like one big block of text, I suppose the way to go around this is to use <br> instead of just enters as it probably doesnt read this, but is it possible to make this text area do read and insert <br>'s where people use enter to make paragraphs? Much help appreciated, I realize this must be a very beginner question but I am not really sure how to word this so I wasn't sure on specific search on this. Hello, i am currently getting an Microsoft Excel formatted text file whose save type is .Txt from a URL.I used to open it and will change the save type as excel file. Please suggest whether we can do this with php code. currently my code is like this, <? php copy("http://www.faa.gov/airports/airport_safety/airportdata_5010/menu/emergencyplanexport.cfm?Region=&District=&State=&County=&City=LAS%20VEGAS&Use=&Certification=","./contactsexport.xls"); ?> where as the contactsexport.xls type is .Txt which i need it in .xls Thanks in Advance. Assist a newbie! I've got a TEXT field in a database, that contains something like this: "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam tristique eros quis dui ullamcorper vulputate. Nullam cursus mollis leo nec dignissim. Suspendisse rutrum accumsan turpis, nec semper neque mattis in. Sed in eros erat, ac condimentum tortor. Donec id erat ac arcu elementum pharetra vitae sit amet odio. Donec adipiscing, enim a sagittis lacinia, justo risus pulvinar tortor, ut suscipit ligula metus eget nisi. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Vestibulum vel purus ut metus convallis facilisis. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Aliquam erat volutpat. Vestibulum id nisl pretium lectus molestie semper. Quisque augue lorem, adipiscing ac varius vitae, hendrerit sit amet metus. Phasellus hendrerit sagittis nisl ac tincidunt." However, when I display this data using PHP is does not differentiate between the linebreak and a space, so the displayed text looks like this: "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam tristique eros quis dui ullamcorper vulputate. Nullam cursus mollis leo nec dignissim. Suspendisse rutrum accumsan turpis, nec semper neque mattis in. Sed in eros erat, ac condimentum tortor. Donec id erat ac arcu elementum pharetra vitae sit amet odio. Donec adipiscing, enim a sagittis lacinia, justo risus pulvinar tortor, ut suscipit ligula metus eget nisi. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Vestibulum vel purus ut metus convallis facilisis. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Aliquam erat volutpat. Vestibulum id nisl pretium lectus molestie semper. Quisque augue lorem, adipiscing ac varius vitae, hendrerit sit amet metus. Phasellus hendrerit sagittis nisl ac tincidunt." How can I keep a paragraph as a paragraph using MySQL and PHP?? 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 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";
}
?>
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 All, I am exporting data to excel, but run into a problem if the text contains a line break. When it gets to the line break, it cuts off the rest of the text. Here is my printer code: Code: [Select] $file = 'Notes_Export'; $csv_output = array(); $tmp = array(); $tmp[] = 'Created On'; $tmp[] = 'Created By'; $tmp[] = 'Note'; $csv_output[] = '"' . implode('","', $tmp) . '"'; $sql = "SELECT pn.created_on, CONCAT( u.firstname,' ', u.lastname) AS created_by, pn.note FROM prop_notes pn LEFT JOIN users u ON pn.created_by = u.user_id "; $sql .= "WHERE pn.deleted_by IS NULL AND pn.archive = '0' AND pn.property_id = '".$_GET['pid']."'"; $result = mysqli_query($connect, $sql); while($rowr = mysqli_fetch_row($result)) { $tmp = array(); for ($j=0; $j<3; $j++) {$tmp[] = $rowr[$j];} $csv_output[] = '"' . implode('","', $tmp) . '"'; } $filename = $file."_".date("Y-m-d_H-i",time()); header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); // header( "Content-disposition: filename=".$filename.".csv"); header("Content-disposition: attachment; filename=".$filename.".csv"); print implode("\n",$csv_output) . "\n"; exit; Hi, I am attempting to export some table data to an excel spreadsheet. Below is the code I am working with. <?php include('includes/admin_session.php'); require_once("includes/connection.php"); function cleanData(&$str) { $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; } // file name for download $filename = "student_list_" . date('Ymd') . ".xls"; header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: application/vnd.ms-excel"); $flag = false; $result = mysqli_query($connection,"SELECT * FROM students ORDER BY tutor") or die(mysqli_error($connection)); while(false !== ($row = mysqli_fetch_assoc($result))) { if(!$flag) { // display field/column names as first row echo implode("\t", array_keys($row)) . "\n"; $flag = true; } array_walk($row, 'cleanData'); echo implode("\t", array_values($row)) . "\n"; } ?> It is exporting the rows as requested and I am able to open the file in excel....however...when downloading it becomes stuck on a loop and continues to download over 20mb of nothing. As a result I am forced to stop the download. When I open my excel file, below my table data is the following error, over and over and over and over! Quote <br /> <b>Warning</b>: array_walk() [<a href='function.array-walk'>function.array-walk</a>]: The argument should be an array in <b>/home/inschool/public_html/student_list_export.php</b> on line <b>33</b><br /> <br /> <b>Warning</b>: array_values() [<a href='function.array-values'>function.array-values</a>]: The argument should be an array in <b>/home/inschool/public_html/student_list_export.php</b> on line <b>34</b><br /> <br /> <b>Warning</b>: implode() [<a href='function.implode'>function.implode</a>]: Invalid arguments passed in <b>/home/inschool/public_html/student_list_export.php</b> on line <b>34</b><br /> Any help on this would be awesome 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. 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, 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 All, As we know........to export excel sheet the database content....we query the data base and then process the result and using Workbook and header we export the result in excel.................. Is there any way we can reduce the down load time or increase the performance for same. ? Thanks. does anyone know who to resolve this issue of importing a CSV file from excel into sql? I get this error when I do. LOAD DATA LOCAL INFILE '/tmp/phpq2aAbU' INTO TABLE `Events` FIELDS TERMINATED BY ',' ENCLOSED BY '\\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\n' This is the script i am using i two areas , but the goal is not the same :( Code: <div class='allDeal'> <div class='date'><?php echo app()->locale->dateFormatter->formatDateTime( utime($data->start,false),'medium',false); ?></div> <div class='content'> <div class='name'><?php echo CHtml::link($data->name,url('/deal/view',array('url'=>$data->url))); ?></div> <div class='clearfix prepend-top'> <div class='column data'> <div class='stats txt-center'> <span class='num'><?php echo $data->stats && $data->stats->bought?$data->stats->bought:'0'; ?></span><br /> <?php echo $this->t('Coupons Bought'); ?> </div> <div class='info prepend-top'> <div class='row price'> <label><?php echo $this->t('Price').':'; ?></label> <?php echo m('payment')->format($data->price); ?> </div> <div class='row'> <label><?php echo $this->t('Value').':'; ?></label> <?php echo m('payment')->format($data->value); ?> </div> <div class='row'> <label><?php echo $this->t('Savings').':'; ?></label> <?php echo m('payment')->format($data->value-$data->price); ?> </div> </div> </div> <div class='column last image'><?php if($data->imageBin) echo CHtml::link( CHtml::image(app()->storage->bin($data->imageBin)->getFileUrl('original')), url('/deal/view',array('url'=>$data->url)) ); ?></div> </div> </div> </div><?php if($index && ($index+1)%2==0) { ?><div class='clearfix'></div><?php } Only change: div class='allDeal' to div class='recentDeal' This is the good: http://kupon.woano.lv/deal/recent and the same code in other place looks ugly :( http://kupon.woano.lv/deal/all What i am doing wrong
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 how is it possible to allow someone to do paragraphs when using preg_replace? would someone show a few good examples of hows its done? I'm wondering if the GD library is capable of coloring ONLY the non-transparent areas in an image. I had this image white, black, and every other color under the moon, but could only get GD to color the entire canvas (width and height) of the image with an opacity over it. I'm merging many layers on top of each other so I can't afford to color the entire square and lower the opacity and do some weird multiply stuff.
That's the image above. Then I have some lineart, markings, eye color, etc. So I had all my possible colors white areas that I layer on top of eachother. I want to color the entire non-transparent areas with a color (dynamic).. so I can't just color it in Photoshop and then upload it or else I would literally have millions of possibilities for every single color and shade..
Thanks for any help. I was trying to go with ImageMagick if GD isn't capable, but I can't even get that downloaded in my Wamp. I'm using 64 bit Wamp so I have no idea if that does something or whatever. I can get it installed on my computer and run it via command line, but I can't get it to work and render images in my Wamp files... can't ever find a .dll that works.
|
|||||||