?
Solved

MySQL to Excel - include more tables in the print preview.

Posted on 2009-02-24
3
Medium Priority
?
853 Views
Last Modified: 2013-12-12
Script Purpose:  PHP script Mysql to Excel for a Employee Roster.

I have two scripts below:
One builds a print preview.
The other Mysql to Excel script.

How can i get the Excel file to build the same view as the print preview. My current problem is the excel just grabs the entire data and does not attach the data from the branch table. This results in the excel file having numbers instead of names.
PRINT PREVIEW SCRIPT
 
 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Hawaii Community Federal Credit Union</title>
 
<?php
// This script and data application were generated by AppGini 4.1 on 8/26/2008 at 3:14:20 PM
// Download AppGini for free from http://www.bigprof.com/appgini/download/
 
	include(dirname(__FILE__)."/defaultLang.php");
	include(dirname(__FILE__)."/language.php");
	include(dirname(__FILE__)."/lib.php");
	include(dirname(__FILE__)."/employees_dml.php");
 
	
	echo '<a href="javascript:window.print()">Print page</a>'; 
 
	// SQL query used in the table view
	$tableViewQuery="select employees.contact_id as 'Contact ID', concat(branch2.group_name, '') as 'Branch', employees.first_name as 'First Name', employees.last_name as 'Last Name', employees.title as 'Title', employees.id as 'ID', employees.email as 'Email', employees.phone as 'Phone', employees.fax as 'Fax', concat(department10.dept_name, '') as 'Dept', employees.in_out as 'IN - OUT', employees.photo as 'Photo', employees.misc as 'Misc' from employees LEFT JOIN branch as branch2 ON employees.group_id=branch2.gid LEFT JOIN department as department10 ON employees.dept=department10.dept_id ";
// 
	// mm: can the current member access this page?
	$perm=getTablePermissions('employees');
	if(!$perm[0]){
		echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\">";
		echo "<div class=\"error\">".$Translation['tableAccessDenied']."</div>";
		exit;
	}
	
	$x = new DataList;
	$x->TableName = "employees";
	$x->DataHeight = 300;
	$x->AllowSelection = 0;
	$x->HideTableView = ($perm[2]==0 ? 1 : 0);
	$x->AllowDelete = $perm[4];
	$x->AllowInsert = $perm[1];
	$x->AllowUpdate = $perm[3];
	$x->SeparateDV = 0;
	$x->AllowDeleteOfParents = 0;
	$x->AllowFilters = 0;
	$x->AllowSavingFilters = 0;
	$x->AllowSorting = 1;
	$x->AllowNavigation = 0;
	$x->AllowPrinting = 0;
	$x->AllowCSV = 0;
	$x->RecordsPerPage = 10;
	$x->QuickSearch = 0;
	$x->QuickSearchText = $Translation["quick search"];
	$x->ScriptFileName = "print_view.php";
	$x->RedirectAfterInsert = "print_view.php?SelectedID=#ID#";
	$x->TableTitle = "&nbsp;   EMPLOYEE ROSTER";
	$x->PrimaryKey = "employees.contact_id";
 
	$x->ColWidth   = array(10, 10, 10, 60, 30, 10, 40, 40, 30, 30, 10);
	$x->ColCaption = array("Branch", "First", "Last", "Title", "ID", "Email", "Phone", "Fax", "Dept", "S", "L");
	$x->ColNumber  = array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13);
 
	$x->Template = 'print_templateTV.html';
	$x->SelectedTemplate = 'print_templateTVS.html';
	$x->ShowTableHeader = 1;
	$x->ShowRecordSlots = 0;
	$x->HighlightColor = '#c1cdd4';
	if($HTTP_POST_VARS["Filter_x"] != ""  || $HTTP_POST_VARS['CSV_x'] != ""){
		// Query used in filters page and CSV output
		// mm: build the query based on current member's permissions
		if($perm[2]==1){ // view owner only
			$x->Query = $filtersCSVQuery.", membership_userrecords  where employees.contact_id=membership_userrecords.pkValue and membership_userrecords.tableName='employees' and membership_userrecords.memberID='".getLoggedMemberID()."'";
		}elseif($perm[2]==2){ // view group only
			$x->Query = $filtersCSVQuery.", membership_userrecords  where employees.contact_id=membership_userrecords.pkValue and membership_userrecords.tableName='employees' and membership_userrecords.groupID='".getLoggedGroupID()."'";
		}elseif($perm[2]==3){ // view all
			$x->Query = $filtersCSVQuery."";
		}elseif($perm[2]==0){ // view none
			$x->Query = "select 'Not enough permissions' from employees";
		}
	}else{
		// Query used in table view
		// mm: build the query based on current member's permissions
		if($perm[2]==1){ // view owner only
			$x->Query = $tableViewQuery.", membership_userrecords  where employees.contact_id=membership_userrecords.pkValue and membership_userrecords.tableName='employees' and membership_userrecords.memberID='".getLoggedMemberID()."'";
		}elseif($perm[2]==2){ // view group only
			$x->Query = $tableViewQuery.", membership_userrecords  where employees.contact_id=membership_userrecords.pkValue and membership_userrecords.tableName='employees' and membership_userrecords.groupID='".getLoggedGroupID()."'";
		}elseif($perm[2]==3){ // view all
			$x->Query = $tableViewQuery."";
		}elseif($perm[2]==0){ // view none
			$x->Query = "select 'Not enough permissions' from employees";
		}
	}
	
	// handle date sorting correctly
	// end of date sorting handler
	$x->Render();
 
	//include(dirname(__FILE__)."/header.php");
	echo $x->HTML;?>
	
 
 
_________________________________________________________
EXPORT TO EXCEL SCRIPT 
 
 
<?php
/**************
This PHP script Extracts MySQL table and downloads into an Excel Spreadsheet.
Script by Jeff Johns, for a full explanation and tutorial on this, see: http://www.phpfreaks.com/tutorials/114/0.php
**************
CONFIGURATION:
 
YOUR DATABASE HOST = (ex. localhost)
USERNAME = username used to connect to host
PASSWORD = password used to connect to host
DB_NAME = your database name
TABLE_NAME = table in the database used for extraction
**************
To extract specific fields and not the whole table, simply replace
the * in the $select variable with the fields you want
**************/
define(db_host, "localhost");
define(db_user, "root");
define(db_pass, "admin7449");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "roster");
mysql_select_db(db_name);
/*************
Build query, call it, and find the number of fields
/*************/
$select = "SELECT * FROM employees";
$export = mysql_query($select);
$count = mysql_num_fields($export);
/************
Extract field names and write them to the $header variable
/***********/
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($export, $i)."\t";
}
/***********
Extract all data, format it, and assign to the $data variable
/**********/
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
/************
Set the default message for zero records
/************/
if ($data == "") {
$data = "n(0) Records Found!n";
}
/************
Set the automatic download section
/************/
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=EmployeeRoster.xls");
header("Pragma: no-cache");
header("Expires: 0");
print $header. "\n". $data;
?>

Open in new window

0
Comment
Question by:hicommfcu
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
ThatDeadDude earned 2000 total points
ID: 23726068
Well, you are using completely different queries in your Excel code to those in the print preview section.  If you want the output to be identical then you're going to have to make sure that the same query is used.

More specifically,
You want to replace
 
$select = "SELECT * FROM employees";
 
with something along the lines of
 
$perm=getTablePermissions('employees');
$select="select employees.contact_id as 'Contact ID', concat(branch2.group_name, '') as 'Branch', employees.first_name as 'First Name', employees.last_name as 'Last Name', employees.title as 'Title', employees.id as 'ID', employees.email as 'Email', employees.phone as 'Phone', employees.fax as 'Fax', concat(department10.dept_name, '') as 'Dept', employees.in_out as 'IN - OUT', employees.photo as 'Photo', employees.misc as 'Misc' from employees LEFT JOIN branch as branch2 ON employees.group_id=branch2.gid LEFT JOIN department as department10 ON employees.dept=department10.dept_id ";
 
if($perm[2]==1){ // view owner only
    $select = $select.", membership_userrecords  where employees.contact_id=membership_userrecords.pkValue and membership_userrecords.tableName='employees' and membership_userrecords.memberID='".getLoggedMemberID()."'";
}elseif($perm[2]==2){ // view group only
    $select = $select.", membership_userrecords  where employees.contact_id=membership_userrecords.pkValue and membership_userrecords.tableName='employees' and membership_userrecords.groupID='".getLoggedGroupID()."'";
}elseif($perm[2]==3){ // view all
    $select = $select."";
}elseif($perm[2]==0){ // view none
    $select = "select 'Not enough permissions' from employees";
}

Open in new window

0
 

Author Comment

by:hicommfcu
ID: 23726760
Aloha!

Thank you, i already had the script going in as root access so, all i added was the select section and it worked perfectly.

Thank you.
$select="select employees.contact_id as 'Contact ID', concat(branch2.group_name, '') as 'Branch', employees.first_name as 'First Name', employees.last_name as 'Last Name', employees.title as 'Title', employees.id as 'ID', employees.email as 'Email', employees.phone as 'Phone', employees.fax as 'Fax', concat(department10.dept_name, '') as 'Dept', employees.in_out as 'IN - OUT', employees.photo as 'Photo', employees.misc as 'Misc' from employees LEFT JOIN branch as branch2 ON employees.group_id=branch2.gid LEFT JOIN department as department10 ON employees.dept=department10.dept_id ";

Open in new window

0
 

Author Closing Comment

by:hicommfcu
ID: 31550749
Mahalo Nui Loa
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question