g.sobhani Posted October 29, 2010 Share Posted October 29, 2010 hi, i'm new to php. i've got a problem. I've 3 tables and table has following content table 1 id name email address phone execution_date executor_name web_address table 2 id name email address phone execution_date executor_name project_title table 3 id name email address phone execution_date executor_name reviewer I want to export these table to and spreadsheet (.xls) with some criteria a form will assign which data will published at the spreadsheet with these criteria #all data can be exported from three tables #some column can be selected from three tables #some or all data can be selected from individual table i've implement a script for this but it didn't meet my requirements. Can anyone help? here is my script <?php $DB_Server = "localhost"; //your MySQL Server $DB_Username = "root"; //your MySQL User Name $DB_Password = "pass"; //your MySQL Password $DB_DBName = "mydb"; //your MySQL Database Name $search_from_date = $_POST['start_date']; $search_to_date = $_POST['end_date']; $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); $Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); $now_date = date('m-d-Y H:i'); if(($_POST['typer_of_report'] == 'rrc_report') || ($_POST['typer_of_report'] == 'all_report')) { $DB_TBLName = 'rrc_record'; } if(($_POST['typer_of_report'] == 'erc_report') || ($_POST['typer_of_report'] == 'all_report')) { $DB_TBLName2 = 'erc_record'; } if(($_POST['typer_of_report'] == 'aeec_report') || ($_POST['typer_of_report'] == 'all_report')) { $DB_TBLName3 = 'aeec_record'; } $file_type = "vnd.ms-excel"; $file_ending = "xls"; //} header("Content-Type: application/$file_type"); header("Content-Disposition: attachment; filename=protocol_report.$file_ending"); header("Pragma: no-cache"); header("Expires: 0"); if($DB_TBLName) { $sql = "Select * from ".$DB_TBLName." where execution_date >= '".$search_from_date."' and execution_date <= '".$search_to_date."' order by execution_date desc"; $Use_Title = 1; $title = "Report for $DB_TBLName on $now_date"; $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); if ($Use_Title == 1) { echo("$title\n"); } $sep = "\t"; //tabbed character for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } //} echo "\n...\n"; } if($DB_TBLName2) { $sql = "Select * from ".$DB_TBLName2." where execution_date >= '".$search_from_date."' and execution_date <= '".$search_to_date."' order by execution_date desc"; $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); $Use_Title = 1; $title = "Report for $DB_TBLName2 on $now_date"; if ($Use_Title == 1) { echo("$title\n"); } $sep = "\t"; //tabbed character for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } echo "\n...\n"; } if($DB_TBLName3) { $sql = "Select * from ".$DB_TBLName3." where execution_date >= '".$search_from_date."' and execution_date <= '".$search_to_date."' order by execution_date desc"; $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); $Use_Title = 1; $title = "Report for $DB_TBLName3 on $now_date"; if ($Use_Title == 1) { echo("$title\n"); } $sep = "\t"; //tabbed character for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } } ?> have anyone any idea? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.