How to export mysql table data in PHP

Question

1.00/5 (1 vote)

See more:

this is my code to export to excel, but it display the data list and not to export in excel

PHP

function ExportToExcel($requests){		
	//print_r($requests);//exit;
		extract($requests);		
		 
		$query = "SELECT * FROM ".$tname." P WHERE 1";
		$cond_search = '';
		$Querycond = '';
		if(isset($dwnld) && ($dwnld == "dwnld_all")){
			$Querycond    .= " AND  1 ";
		}
		
		if(isset($dwnld) && ($dwnld == "new_submission")){
			$Querycond    .= " AND apply_on >= CURDATE()";
		}
		
		if(isset($dwnld) && ($dwnld == "all_submission")){
			$Querycond    .= " AND  3";
		}

		$query .=  $Querycond;
	
		$query1 = $query . " ORDER BY P.apply_on desc";
		
		$setRec = mysql_query($query1);
		  
		$developer_records = array();
		while( $rows = mysql_fetch_assoc($setRec) ) {
		$developer_records[] = $rows;
		} 	
		$filename = "export_".date('Ymd') . ".xls";
		header("Content-type: application/octet-stream");
		header("Content-Type: application/vnd.ms-excel; name='excel'");
		header("Content-Disposition: attachment; filename='".$filename."'");
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Cache-Control: max-age=0');

		$show_coloumn = false;
		if(!empty($developer_records)) {
		foreach($developer_records as $record) {
		if(!$show_coloumn) {
		// display field/column names in first row
		echo implode("\t", array_keys($record)) . "\n";
		$show_coloumn = true;
		}
		echo implode("\t", array_values($record)) . "\n";
		}
		}
		
		exit;		
		return true;
	}

What I have tried:

please help me, how to solve it,i have did in ajax call to php file then that php file to another php file

Posted 19-Jan-18 0:25am

Member 13618048

Updated 21-Jan-18 18:31pm

CPallini

v2

Add a Solution

Comments

Richard Deeming 19-Jan-18 10:52am

Why are you sending three different Content-Type headers?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13618048 · Answer 1 · 2018-01-21T18:31:00

Solution 2

i am writing this

SELECT * FROM tname P WHERE 1

because of the P is used later as alias name like

$query1 = $query . " ORDER BY P.apply_on desc";

.

Posted 21-Jan-18 18:31pm

Member 13618048

Jochen Arndt · Answer 2 · 2018-01-19T01:32:00

Good code always checks if function calls fail. To do that, inspect the return values and show error messages upon failures.

If you do that, you will be informed that your query fails because you are passing an invalid SQL command:

SQL

SELECT * FROM tname P WHERE 1

A valid command would be something like

SQL

SELECT * FROM tname WHERE P=1

Similar for the appending of " AND 1 " which is also not valid.

Finally, you are not creating an Excel file. You are creating a TAB separated text file which is recognised by Excel when importing. But it is not an Excel file and should be therefore not announced as such.