Location: PHPKode > projects > HypatiaDB > hypatiadb/report2.php
<?php
include("common_db.php");
include("functions.php");
dbconnect($host, $username, $password); //from common_db.php
$strings = loadStrings($lang, 'REPORT2');

if(isset($_REQUEST['report_id'])) {
	//the user has jumped back here
	$report_id = intval($_REQUEST['report_id']);
} else {
	//TODO: panic
	die('panic!!!');
}

mysql_select_db('HypatiaDB');

//Now we need to get the data back out of the database
$report_details = mysql_fetch_array(mysql_query("SELECT * FROM reports WHERE id = '$report_id'"));

//OK. Now, the user may be coming to this page for the first time,
//or the user may have already been here before, but have changed
//which tables or the database he/she was going to use
//TODO: validation and error checking
if(isset($_REQUEST['db'])) {
	//check if it's the same database
	if($report_details['db'] != $_REQUEST['db']) {
		$db = $_REQUEST['db'];						//TODO: SQL injection
		//OK, the user has changed which database they would
		//like to get tables from.
		//This invalidates conditions, sorts, selected fields
		//and the db that's selected.
		$query = "UPDATE reports SET db = '$db' WHERE id = '$report_id'";
		mysql_query($query) or die(mysql_error());			//TODO: L10N and below
		$query = "DELETE FROM report_fields WHERE report = '$report_id'";
		mysql_query($query) or die(mysql_error());			//TODO: L10N and below
		$query = "DELETE FROM report_conditions WHERE report = '$report_id'";
		mysql_query($query) or die(mysql_error());			//TODO: L10N and below
		$query = "DELETE FROM report_sorts WHERE report = '$report_id'";
		mysql_query($query) or die(mysql_error());			//TODO: L10N and below
	}
} else {
	$db = $report_details['db'];
}

if(isset($_REQUEST['tables'])) {
	//This checks if the tables have changed.
	//If they have, then it invalidates some selected fields,
	//which in turn invalidates some sorts and some conditions
	//We need to walk through these recursively to remove
	//the invalid conditions and sorts

	//query to invalidate old fields
	foreach($_REQUEST['tables'] as $new_table) {
		$tblquery[] = "tbl != '$new_table'";
	}

	//now we need to get all the conditions and sorts that rely
	//on the fields scheduled for deletion
	$query = "SELECT id FROM report_fields WHERE report = '$report_id' AND (" . implode($tblquery, " AND ") . ")";
	$old_fields = mysql_query($query);
	while($field = mysql_fetch_array($old_fields)) {
		//delete all conditions and sorts based on this
		$query = "DELETE FROM report_sorts WHERE field = '$field[id]'";
		mysql_query($query) or die(mysql_error());
		$query = "DELETE FROM report_conditions WHERE lvalue = '$field[id]'";
		mysql_query($query) or die(mysql_error());
	}
	$query = "DELETE FROM report_fields WHERE report = '$report_id' AND (" . implode($tblquery, " AND ") . ")";
	mysql_query($query) or die(mysql_error());
}		//OK. The cleaning is done (finally).

headers();
html();
head($strings['R2_TITLE'], array('xhr/create-xhr.php?target=\'xhr/get-report-tl.php?db=\'%2Br1db&amp;caller=getTables&amp;tid=tbls&amp;tfunc=clearElem(\'tbls\');'));
menu();
navpane();
?>
	<div id="mainpane">
		<?php getReportSteps(2, $report_id); echo($strings['R2_INTRO']); ?>
		<form action="report3.php" method="post" class="lblock">
<input type="hidden" name="fireupdate" value="go" />
			<?php
echo("<input type=\"hidden\" name=\"report_id\" value=\"$report_id\" />");
mysql_select_db($db);
//now, generate the appropriate fieldsets
if(isset($_REQUEST['tables'])) {
	foreach($_REQUEST['tables'] as $new_table) {
		echo('<fieldset><legend>' . htmlspecialchars($new_table, ENT_QUOTES) . '</legend>');
		//get what the user has already selected
		$sfields = mysql_query("SELECT col FROM report_fields WHERE report = '$report_id' AND tbl = '$new_table'");
		$selected_fields = array();
		while($sf = mysql_fetch_array($sfields)) {
			$selected_fields[] = $sf['col'];
		}
		//get the fields from the database
		$query = "DESCRIBE $new_table";
		$fields = mysql_query($query);
		while($field = mysql_fetch_array($fields)) {
			echo('<label for="' . htmlspecialchars($new_table, ENT_QUOTES) . '-' . htmlspecialchars($field['Field'], ENT_QUOTES) . '"><input type="checkbox"');
			if(in_array($field['Field'], $selected_fields))
				echo(' checked="checked"');
			echo(' name="' . htmlspecialchars($new_table, ENT_QUOTES) . '-' . htmlspecialchars($field['Field'], ENT_QUOTES) . '" />' . $field['Field'] . '</label>');
		}
		echo('</fieldset>');
	}
} else {
	mysql_select_db('HypatiaDB');
	//We are loading the tables
	$query = "SELECT DISTINCT tbl FROM report_fields WHERE report = '$report_id'";
	($tables = mysql_query($query)) or die(mysql_error());		//TODO: L10N
	mysql_select_db($db);
	while($table = mysql_fetch_array($tables)) {
		$tname = $table['tbl'];
		echo('<fieldset><legend>' . $tname . '</legend>');
		mysql_select_db('HypatiaDB');
		($fields = mysql_query("SELECT col FROM report_fields WHERE report = '$report_id' AND tbl='$tname'")) or die(mysql_error());
		$selected_fields = array();
		while($field = mysql_fetch_array($fields)) {
			$selected_fields[] = $field['col'];
		}
		mysql_select_db($db);
		($fields = mysql_query("DESCRIBE $tname")) or die(mysql_error());
		while($field = mysql_fetch_array($fields)) {
			echo("<label for=\"$tname-$field[Field]\"><input type=\"checkbox\" name=\"$tname-$field[Field]\"" . (in_array($field['Field'], $selected_fields) ? ' checked="checked"' : '' ) . " />$field[Field]</label>");
		}
		echo('</fieldset>');
	}
}
			?>
			<input value="Next Page" type="submit" />
		</form>
	</div>
<?php
endhtml();
?>
Return current item: HypatiaDB