PHP - Mysql To Excel....exporting Only One Column

I am trying to find a loop type script that starts at a particular row in a particular column in an excel file and keeps adding values in each row until the last value is reached.

I have searched and am pretty new to loops. can anyone point me in the right direction please?

My Php Buddies,

I have mysql tbl columns these:

id: 
date_&_time: 
account_activation_code: 
account_activation_status: 
id_video_verification_status: 
id_verification_video_file_url: 
username: 
password: 
primary_domain: 
primary_website_email: 
sponsor_username: 
 

Now, I want to display their row data by excluded a few columns.

Want to exclude these columns:

date_&_time

account_activation_code

account_activation_status

id_verification_video_file_url

password

 

So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:

Id: 1
Id Video Verification Status: 1
Username: requinix
Primary_domain: requinix.majesty
Primary Website Email: requeen@requinix.majesty
Sponsor Username: phpsane
Recruits Number: 2

 

For your convenience only PART 1 works. Need help on Part 2

My attempted code:

 

	PART 1
	<?php 
	// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
	// Query to get columns from table
$query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'");
	while($row = $query->fetch_assoc()){
    $result[] = $row;
}
	// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');
	foreach ($columnArr as $value) { 
echo "<b>$value</b>: ";?><br><?php 
} 
	?>
	 
	PART 2
	<?php 
	//Display User Account Details 
echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br> 
<?php 
$excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password"); 
foreach ($excluded_columns as $value2) 
{    
    echo "Excluded Column: <b>$value2</b><br>";
}
foreach ($columnArr as $value) 
{ 
    
    if($value != "$value2")
    { 
        $label = str_replace("_"," ","$value"); 
        $label = ucwords("$label"); 
        //echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php 
        echo "<b>$label</b>: "; echo "${$value}";?><br><?php 
    }
} 
?>
	

PROBLEM: Columns from the excluded list still get displayed.

Edited November 19, 2018 by phpsane

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 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;



?>

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, 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.

I have a mysql table named songs with 3 columns, id, artist,title.

Most of the songs in the artist column are correct, ex:

artist
------------------
John Denver
Loretta Lynn
Shania Twain
Luke Bryan

But some of the songs in the artist column are reveresed with a comma, ex:

artist
-----------------
Dever, John
Lynn. Loretta
Twain, Shania
Bryan, Luke

Is there an easy php code snippet or mysql statement that i can use to reverse the order of first name and last name and remove the comma in the last example so the artst columd matches the first example?

I hope this makes sense, thanks, Dale.

Php Buddies,

Just how do you insert an uploaded img via html for ($_POST) to mysql tbl and what type of column should it be ?

Also, how to link the img from the mysql column to a webpage ?

Following html links the img from a folder/directory to a webpage:

<a href="../html-link.htm"><img src="flower.jpg" style="width:82px; height:86px" title="White flower" alt="Flower"></a>

Is there a way to use php to make an automatic column addition to a table?  like if I had a comment form and want to save the replys in a database, I would want a column for each reply, is there a way to make a new column each time someone submits the form?

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'

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)

How can I use PHP to export my database into a .sql file?