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

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'"));

//This is the conditions page.
//The user may be coming here for the first time, in which case no conditions
//exist. However, the user may also be coming here after changing (or not
//changing) which tables and fields are selected. If tables have been deselected,
//then conditions that references those tables will have been deleted by report2.php
//However, if the field structure has changed, we need to delete any conditions
//referencing fields that no longer exist.
//Note that if the user has changed which database they were viewing, that is the
//same as them arriving for the first time.

if(isset($_REQUEST['fireupdate'])) {			//this means we need to fire an update
							//if it's not set, then presumably
							//we came via the sidebar

//Get the fields that (used to) apply to this report
$report_fields = mysql_query("SELECT tbl, col, id FROM report_fields WHERE report = '$report_id'");

//Fields selected in the previous page are passed to us as
//     <table name> "-" <field name>
//We can build a two dimensional array of these fields
foreach($_REQUEST as $key => $value) {
	if(preg_match("/(.+)-(.+)/", $key, $matches)) {
		//valid
		$new_fields[$matches[1]][$matches[2]] = true;
	}
}

//Now we need to search for fields that have been removed
while($field = mysql_fetch_array($report_fields)) {
	//Right. Now, cross-reference with what we've got
	if(!isset($new_fields[$field['tbl']][$field['col']])) {
		//Delete conditionals that references the non-existant field
		$query = "DELETE FROM report_conditions WHERE lvalue = '$field[id]'";
		mysql_query($query) or die(mysql_error());		//TODO: L10N
		//Delete sorts that reference the conditionals that no longer exist
		$query = "DELETE FROM report_sorts WHERE field = '$field[id]'";
		mysql_query($query) or die(mysql_error());		//TODO: L10N
		//Delete everything that 
		//And delete the field itself
		$query = "DELETE FROM report_fields WHERE id = '$field[id]'";
		mysql_query($query) or die(mysql_error());
	}
}

//That done, we need to insert the new fields into the database.
//There's a problem: how do we tell which fields are still in the database and the
//new set; and then how do we tell which fields are not in the database but are in
//the new set. We could merely delete all the field and re-add them, but this would
//break foreign key references (not that MySQL supports them, but that's the idea).
//Consequently, we have to run a whole lot of selects.

foreach($new_fields as $table => $children) {			//children should be an array
	foreach($children as $row => $dummy) {			//dummy is merely "true"
		//Does this exist
		$query = "SELECT id FROM report_fields WHERE report = '$report_id' AND tbl = '$table' AND col = '$row'";
		if(mysql_num_rows(mysql_query($query)) == 0) {
			//A new field
			//TODO: SQL injection
			$query = "INSERT INTO report_fields(report, tbl, col) VALUES ('$report_id', '$table', '$row')";
			mysql_query($query) or die(mysql_error());
		}
	}
}

}			// from isset($_REQUEST['fireupdate']);
//OK. All validation over (phew!)

//Since we allow multiple query conditions, we have a nice XmlHttpRequest interface
//that handles that for us.

headers();
html();
head($strings['R3_TITLE'], array('js/rep3.php?rid=' . $report_id));
menu();
navpane();
?>
	<div id="mainpane">
		<?php getReportSteps(3, $report_id); echo($strings['R3_INTRO']); 
echo('<form>
<select id="lvalue" name="lvalue">');

//get all the lvalues in a nice, pretty printing form
//note that we can't use the $new_fields array, because
//that won't be set if the user has come via the sidebar

$query = "SELECT id, tbl, col FROM report_fields WHERE report = '$report_id'";
$fields = mysql_query($query);

while($field = mysql_fetch_array($fields)) {
	echo('<option value="' . $field['id'] . '">' . $field['tbl'] . '.' . $field['col'] . '</option>');
}

$literals = loadStrings($lang, 'GETFOP');

//'equal','lessthan','greaterthan','like','rlike','contains','between'
echo('</select><select id="operator" name="operator">
<option value="equal">' . $literals['GF_EQUAL'] . '</option>
<option value="notequal">' . $literals['GF_NOTEQUAL'] . '</option>
<option value="lessthan">' . $literals['GF_LT'] . '</option>
<option value="lte">' . $literals['GF_LTE'] . '</option>
<option value="greaterthan">' . $literals['GF_GT'] . '</option>
<option value="gte">' . $literals['GF_GTE'] . '</option>
<option value="like">' . $literals['GF_LIKE'] . '</option>
<option value="notlike">' . $literals['GF_NOTLIKE'] . '</option>
<option value="rlike">' . $literals['GF_RLIKE'] . '</option>
<option value="notrlike">' . $literals['GF_NOTRLIKE'] . '</option>
<option value="contains">' . $literals['GF_CONTAINS'] . '</option>
<option value="notcontains">' . $literals['GF_NOTCONTAINS'] . '</option>
<option value="between">' . $literals['GF_BETWEEN'] . '</option>
<option value="notbetween">' . $literals['GF_NOTBETWEEN'] . '</option>
</select><input id="rvalue" type="text" name="rvalue" /><br /><a href="#" onclick="addCondition();">' . $strings['R3_ADDCONDITION'] . '</a>');

//Now list all the conditions that apply to this report already
echo('<h2>' . $strings['R3_CONDITIONS_HEADING'] . '</h2><ul id="condlist">');

$query = "SELECT report_conditions.*, report_fields.tbl as 'tbl', report_fields.col as 'col' FROM report_conditions, report_fields WHERE report_conditions.report='$report_id' AND report_fields.id = report_conditions.lvalue";
$conditions = mysql_query($query);
while($c = mysql_fetch_array($conditions)) {
	echo('<li id="condition' . $c['id'] . '">' . $c['tbl'] . '.' . $c['col'] . ' ' . getFriendlyOperator($c['operator'], $c['positive']) . ' ' . $c['rvalue'] . ' [<span onclick="removeCondition(' . $c['id'] . ')" class="ll">' . $strings['R3_REMOVECONDITION'] . '</span>]</li>');
}
?>
		</ul></form><form action="report4.php" method="post"><input type="hidden" name="report_id" value="<?php echo($report_id); ?>" /><input type="submit" value="<?php echo($strings['R3_NEXTPAGE']); ?>" /></form>
	</div>
<?php
endhtml();
?>
Return current item: HypatiaDB