Page 1 of 1

Report Writing Help

Posted: Mon Dec 13, 2010 11:07 pm
by rootchaos
Hi Guys

I am trying to write a simple reporting module that will :-
1. Provide a list of tables..
2. Based on the selected table, display a list of checkboxes for the column names..
3. Based on the checkboxes selected, pull the information from the DB and write to a CSV file.

I have numbers 1 and 2 working. However, I cannot get the correct php code working to generate the CSV file correctly.. This is my code thus far on the script file thats pulls the information and writes to CSV... I will comment as far as I can...

Code: Select all

// Form Post where checkboxes are marked for the columns to be included //
$projectname = $_POST['projectname'];
$hradvisor = $_POST['hradvisor'];
$projectsitelocation = $_POST['projectsitelocation'];
$projectoverview = $_POST['projectoverview'];
$administrator = $_POST['administrator'];
$countrymobilitylead = $_POST['countrymobilitylead'];
$projectlead = $_POST['projectlead'];
$country = $_POST['country'];

Code: Select all

// Create an array for the checkboxes that were selected //
$criteria = array(); 
if ($projectname == "on") { $criteria[] = 'projectname'; }
if ($hradvisor == "on") { $criteria[] = 'hradvisor';  }
if ($projectsitelocation == "on") { $criteria[] = 'projectsitelocation';  }
if ($projectoverview == "on") { $criteria[] = 'projectoverview';  }
if ($administrator == "on") { $criteria[] = 'administrator';  }
if ($countrymobilitylead == "on") { $criteria[] = 'countrymobilitylead';  }
if ($projectlead == "on") { $criteria[] = 'projectlead';  }
if ($country == "on") { $criteria[] = 'country';  }

Code: Select all

// Count the number of fields in the array //
$arraycount = count($criteria);

Code: Select all

// Get the column names in order to construct the query to MySQL and strip away the last ',' //
foreach ($criteria as $value) {
	$fields = $fields . $value . ",";
	$fields1 = substr($fields,0,-1);
}

$selects = "SELECT " . $fields1 . " FROM projects";

$result = mysql_query($selects);

// Count the number of rows returned in the query //
$numrows = mysql_num_rows($result);
// Count the number of fields returned in the query //
$numfields = mysql_num_fields($result);

// Open the CSV file, and write the top headers (column names).. //
$fp = fopen("/tmp/devtest.csv", "w");
foreach( $criteria as $key => $value){
	fwrite($fp, "$value" . ",");
	}
	fwrite($fp, "\n");

// Write each row and fields to the CSV file //
for($r=0;$r<=$numrows;$r++) {
	for($f=0;$f<=$numfields;$f++) {
		$line = $line . mysql_result($result,$r,$f) . ",";
		fwrite($fp, "$line" . "\n");
		
	}
$line = "";	
	}
fwrite($fp, "\n");
fwrite($fp, "##END##");

fclose($fp);
** I have only selected two columns to be displayed... projectname & hradvisor. There are 3 rows in the database currently, which should be displayed.

The CSV file looks like this :-
#cat devtest.csv
projectname,hradvisor,
Project Aruba Dam,
Project Aruba Dam,Cathy Williams,
Project Aruba Dam,Cathy Williams,,
Bermuda Bridge,
Bermuda Bridge,Simon Lesedi,
Bermuda Bridge,Simon Lesedi,,
STW,
STW,M van Aswegen,
STW,M van Aswegen,,
,
,,
,,,


The CSV file should actually look like this :-
#cat devtest.csv
projectname,hradvisor,
Project Aruba Dam,Cathy Williams,
Bermuda Bridge,Simon Lesedi,
STW,M van Aswegen,


Hope someone can help with the correct code...


Thanks, RC