PHP export to csv

[genius_supreme]

Dear freak gurus out there,

I'm new to php and sql as i learn from online examples  .

 

Though there are lots of links and answers out there related to my post but none reflects what i need. You guys my only hope.

 

What i have:

 

3 tables : each has its own number of fields (tbl1=14 ,tbl2=9,tbl3=9). There is a field in each tables that can be related (FamilyField).

 

What i need:

 

export to csv all the records from all 3 tables which relates to search criteria (by user) based on a field (MembershipField) which the data may vary from other tables.

 

pseudo code:

$membership_type =$_POST['mtype'];

 

$sql=" SELECT field1,field2,field5,MembershipField from tbl1, (SELECT field1,field2,MembershipField from tbl2), (SELECT field3,field6,field8,MembershipField from tbl3) WHERE MembershipFied=$membership_type";

 

export to csv file by using INTO OUTFILE;

 

I have tried all the code i can find to export from 1 table to csv, and i manage to use UNION to get output from 3 tables but unable to export

as the error message was "incorrect usage of UNION and INTO.

 

is this possible? or any workaround?

guides with samples will be a great help.

 

[batwimp]

You should probably post this into the MySQL help section.

[batwimp]

There's a typo in your select statement: MembershipFied should probably be MembershipField.

 

It also looks like you may be using your sub-selects wrong (inside the parenthesis). This type of sub-select will take whatever is output from the SELECT statement and put it in there where the sub-select is located. So, for example, let me make up some cells for your tbl2 and tbl3 (i don't know what's actually in those tables). MySQL will first run what's inside the parenthesis and leave you with a SELECT statement that looks something like this:

 

SELECT field1,field2,field5,MembershipField from tbl1, 3,2,1, 5,4,3,2, WHERE MembershipFied=$membership_type

 

This type of SELECT statement doesn't make any sense. Could you explain in more detail what you want to accomplish with your database?

[genius_supreme]

batwimp.. thank you very much on your reply... and my bad i posted in the wrong section 

 

the sql query is just a simple one to give you and idea how bad i am in this part of the world

and i really appreciate the explanation. Now i know how sub-select works..

 

what i'm trying to accomplish:

 

i have 3 tables (tblmember,tblspouse,tblchildren) for family details and contact record which UNION with field:FamilyName. tblmember consists of additional 5 fields for contacts details such as address,postal,state,etc. Each tables also has a field for membership status field:MembershipType.

 

i would like to generate a "report" in csv format where the records are exported based on membership status selected from the dropdown list on the reports.php page. Once clicked on the submit button the query will do the "magic sql" to capture the selected fields from all the 3 tables based on MembershipType = $_POST['dropdownlist']

 

I have done a search query using UNION where the user enters anypart of the "member name" and the query will look into field:MemberName in all 3 tables and displays the selected fields from all 3 tables onto the page itself (no export).

can i combine the SELECT * INTO OUTFILE with UNION? as i received an error "incorrect usage of UNION and INTO".

so how do i use UNION with SELECT INTO OUTFILE?  and is this the right way?

 

i have run 2 separate test query:

1)  with UNION works well on all 3 tables just to print on page in tabulated format

2)  with INTO OUTFILE works well to export only with one table

 

is there a way to have 1 query to do the both?

 

 

[batwimp]

In this case you should use INTO OUTFILE as the last command in your last SELECT statement in the union.

 

So something like:

 

(SELECT field1,field2,field5,MembershipField from tbl1) UNION (SELECT field1,field2,MembershipField from tbl2) UNION (SELECT field3,field6,field8,MembershipField from tbl3) WHERE MembershipField=$membership_type INTO OUTFILE 'filename'

 

Keep in mind that I've never done this before, so I could be wrong. I'm just taking what I read off the MySQL site, and the WHERE statement could also be wrong. But since you've done each thing with success, I would think that just adding the INTO OUTFILE to the end of the last statement should work.

[genius_supreme]

thanks for prompt reply batwimp.

 

i have tried as combining my two queries as how you suggested and i get an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE '../../../../www/reportfolder/exportfolder/Full_membership_20120225.csv' ' at line 4

 

my sql query is

$searchsql="SELECT MemberID, MemberName, MemberIC , FamilyName, MembershipType FROM tblmember WHERE `MembershipType` ='$MembershipType' UNION 
		(SELECT MemberID, MemberName, MemberIC, FamilyName, MembershipType FROM tblspouse WHERE `MembershipType` ='$MembershipType' )  UNION
		(SELECT MemberID, MemberName, MemberIC , FamilyName, MembershipType FROM tblchildren WHERE `MembershipType` ='$MembershipType') 
		INTO OUTFILE '$filelocation' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'	ORDER BY MemberName ASC ";

[genius_supreme]

Finally i got it resolved thanks to sasa for contributing a superb code to generate sql query based on user input and export to file with a download file option, http://www.phpfreaks.com/forums/index.php/topic,155530.0.html

 

I did lots of changes to the code to meet my UNION table of 3 and export to xls instead of csv.

 

here is my code not sure if its elegant but it works for me...hope this will help others who look in google ..

 

$MembershipType='9';

$select_cols="MemberID, MemberName, MemberIC , FamilyName, MembershipType";

function convert_to_xls( $first_row , $null_to = 'NULL') {
global $select_cols;
global $MembershipType;
$sql="SELECT ".$select_cols." FROM tblmember WHERE `MembershipType` ='$MembershipType' UNION 
		(SELECT ".$select_cols." FROM tblspouse WHERE `MembershipType` ='$MembershipType' )  UNION
		(SELECT ".$select_cols." FROM tblchildren WHERE `MembershipType` ='$MembershipType')"; 


$result =mysql_query($sql) or die ("ERROR - unable to connect to table<br>".mysql_error());	
$num=mysql_num_rows($result);

$out = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"
xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
xmlns=\"http://www.w3.org/TR/REC-html40\">

<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html>
<head>
<meta http-equiv=\"Content-type\" content=\"text/html;charset=utf-8\" />
<style id=\"Classeur1_16681_Styles\">
</style>

</head>
<body>

<div id=\"Classeur1_16681\" align=center x:publishsource=\"Excel\">

<table x:str border=0 cellpadding=0 cellspacing=0 width=100% style='border-collapse: collapse'>\n	
<TR>\n";
        $result1 = mysql_fetch_array($result);  
        $result= explode(',', $select_cols);
	$n=0;  
        foreach($result as $field){
	$f_name[] = trim($field);;
	$out .=$first_row ? '<td class=xl2216681 nowrap><b>'.trim($field).'</b></td>'."\n" : '';
	$n++;  //number of columns to retrieve from table
	}
$out .= $first_row ? "</tr>\n" : ''; 
$i=1;$j=1;
$result =mysql_query($sql); 
    $cols= explode(',', $select_cols);
while ($row=mysql_fetch_array($result)){
	for($j=0;$j<$n;$j++){	//number of columns to display per row
		$rec_data=$row[$j];
		if($j==$n){
		$out .= '<td class=x12216681 nowrap>'.$rec_data.'</td>';
		}elseif($j==0){
			$out .= '<tr>'."\n".'<td class=x12216681 nowrap>'.$rec_data.'</td>'."\n";
		}else{
			$out .= '<td class=x12216681 nowrap>'.$rec_data.'</td>'."\n";
		}
	}
}
return $out .= '</table>
</div>
</body>
</html>';
}
$hr = true;	// : false;
$file = "membership.xls";
if ($file){
	$fp = fopen($file, 'w');
	fwrite($fp ,convert_to_xls($hr,"NULL"));
	fclose($fp);
	echo '<hr /><a href="',$file,'">Download</a> Excel spreadsheet';
} else echo '<hr />No file name';
echo convert_to_xls($hr,"NULL");

 

Thank you phpfreak forum and sasa, and batwimp.