Location: PHPKode > projects > HypatiaDB > hypatiadb/browse.php
<?php

include('common_db.php');
include('functions.php');
dbconnect($host, $username, $password);

//The current database
if(isset($_REQUEST['database'])) {
	$db = mysql_real_escape_string(($_REQUEST['database']));
} else {
	$db = $_SESSION['current_db'];
}

if($db == 'HypatiaDB') {
	printAuthError(); die();
}

//The table currently being browsed
if(isset($_REQUEST['table'])) {
	$table = mysql_real_escape_string($_REQUEST['table']);
} else {
	//flail arms around and flee
	$table = '';
}

$strings = loadStrings($lang, "BROWSE");
headers();
html();
//$title = sprintf($strings['BROWSE_HEADING'], $table);
head(sprintf($strings['BROWSE_HEADING'], $table));
menu();
navpane();
echo '<div id="mainpane">';

//include("common_db.php");
//include("menu.php");
//include("functions.php");

//The $insert variable will equal to "yes" if the user inserts a new record.
$insert = (isset($_REQUEST['insert']));

//The $edit variable will contain the row number of the record to be edited when the user
//clicks to edit a record
if(isset($_REQUEST['edit'])) {
	$edit = $_REQUEST['rownum'];
} else {
	$edit = false;
}

//The $save variable will equal to "yes" if the user chooses to save a record that they 
//have edited
$save = isset($_REQUEST['save']);

//The $delete variable will equal to "yes" if the user chooses to delete a record
$delete = (isset($_REQUEST['delete']));

//The $clear variable will equal to "yes" if the user chooses to clear the table
$clear = isset($_REQUEST['clear']);




//Select the current database
mysql_select_db($db) or die(sprintf($strings['BROWSE_ERR_BADSELDB'], $db) . mysql_error());

//If the following conditional holds, then the user has chosen to clear the table. The following
//piece of code does so.
if($clear == "yes" && ($_SESSION['session_user_status'] == 'normal' || $_SESSION['session_user_status'] == 'admin')) {
				//TODO: Do we need confirmation, or is Javascript fine?
				//NOTE: Menu item has been removed, incidentally

	$clear_query = "DELETE FROM $table";
	mysql_query($clear_query) or die();
}


$i=-1;

//If the following conditional holds, then the user has chosen to insert a new record. This
//means that the 'fieldX' variables will contain the values to insert. So using the 'ereg'
//function, the values of the 'fieldX' variables are collected and stored in the 'insert_values'
//array.
if($insert && ($_SESSION['session_user_status'] == 'normal' || $_SESSION['session_user_status'] == 'admin'))
{
//   while (list($lvar, $lvalue) = each($HTTP_GET_VARS))
//   while (list($lvar, $lvalue) = each($_REQUEST))
	foreach($_REQUEST as $lvar => $lvalue) {
		if (ereg("^field", $lvar)) {
			$i++;
			$insert_values[$i] = "'" . mysql_real_escape_string($lvalue) . "'";
		}
	}

//The values within the 'insert_values' array are imploded into one string, an INSERT query
//is formed and, and carried out.
$insert_query_values = implode(",", $insert_values);
$insert_query = "INSERT INTO `$table` VALUES($insert_query_values)";

mysql_query($insert_query) or print(sprintf("<p class=\"error\">$strings[BROWSE_ERR_INSERT]</p>", mysql_error()));
}

//If the following conditional holds, then the user has chosen to edit a record. This
//means that the 'fieldX' variables will contain the values of the row to be edited, so using 
//the 'ereg' function, the values of the 'fieldX' variables are collected and stored in the 
//'edit_values' array.

if(($edit !== false) && ($_SESSION['session_user_status'] == 'normal' || $_SESSION['session_user_status'] == 'admin')) {
//	while (list($lvar, $lvalue) = each($_REQUEST))
	foreach($_REQUEST as $lvar => $lvalue)
	if (ereg("^field", $lvar)) {
//		$i++;
		$edit_values[] = "'" . mysql_real_escape_string($lvalue) . "'";
	}

	$describe_query = "DESCRIBE `$table`";
	($describe_result = mysql_query($describe_query)) or die("query unsuccesful");		//TODO: L10N

	$i=0;

	//The code contained within this while loop creates the WHERE conditions for the UPDATE
	//statement. A string called '$edit_conditions' is then created. It will be used to 
	//ensure that the proper record is edited/updated.
	while($row = mysql_fetch_array($describe_result)) {
		$current_field = $row['Field'];
		$current_edit_value = $edit_values[$i];
		$fields_to_edit[$i] = "`$current_field`=" . $current_edit_value . "";
		$i++;
	}
$edit_conditions = implode(" AND ", $fields_to_edit);

}


//If the following conditional holds, then the user has chosen to save the editing of a record.
//This means that the 'fieldX' variables will contain the new values of the edited row, so 
//using the 'ereg' function, the values of the 'fieldX' variables are collected and stored in 
//the 'save_values' array. Also, the '$edit_conditions' variable is carried over when the 
//users clicks on save, so we know which row to update.

if($save) {
	if(isset($_REQUEST['hypatia___pk'])) {
		//we have a primary key
		$primary_key = mysql_real_escape_string($_REQUEST['hypatia___pk']);
		$edit_conditions = "`$primary_key` = '" . mysql_real_escape_string($_REQUEST['key']) . "'";
	} else {
		$edit_conditions = $_REQUEST['edit_conditions'];
	}
//   while (list($lvar, $lvalue) = each($_REQUEST))
	foreach($_REQUEST as $lvar => $lvalue) {
		if (ereg("^field", $lvar)) {
			$i++;
			$save_values[$i] = "'" . mysql_real_escape_string($lvalue) . "'";
		}
	}

$describe_query = "DESCRIBE `$table`";
($describe_result = mysql_query($describe_query)) or die(sprintf($strings['BROWSE_ERR_SAVE_DESC'], mysql_error()));

$i=0;

   //The code contained within this while loop creates the SET conditions for the UPDATE
   //statement.

	while($row = mysql_fetch_array($describe_result))
	{
		$current_field = $row['Field'];
		$current_save_value = $save_values[$i];
		$fields_to_save[$i] = "`$current_field`=$current_save_value";
		$i++;
	}

$changes = implode(", ", $fields_to_save);

//Create the string for the update query.

$update_query = "UPDATE `$table` SET $changes WHERE $edit_conditions LIMIT 1";
//$update_query = stripslashes($update_query);

mysql_query($update_query) or die(sprintf($strings['BROWSE_ERR_SAVE'], mysql_error()));
}




//If the following conditional holds, then the user has chosen to delete a record.
//This means that the 'fieldX' variables will contain the values of the row, to be deleted. So 
//using the 'ereg' function, the values of the 'fieldX' variables are collected and stored in 
//the 'delete_values' array. 

if($delete)
{
//   while (list($lvar, $lvalue) = each($HTTP_GET_VARS))
	if(isset($_REQUEST['hypatia___pk'])) {
		//We have a primary key.
		$primary = $_REQUEST['hypatia___pk'];
		$field = mysql_real_escape_string($_REQUEST['key']);
		$delete_query = "DELETE FROM `$table` WHERE `$primary` = '$field' LIMIT 1";		//executed below
	} else {
		//There's no primary key... :(
		foreach($_REQUEST as $lvar => $lvalue) {
			if (ereg("^field", $lvar)) {
				$i++;
				$delete_values[$i] = "'" . mysql_real_escape_string($lvalue) . "'";
			}
		}
		$describe_query = "DESCRIBE $table";
		($describe_result = mysql_query($describe_query)) or die(sprintf($strings['BROWSE_ERR_DESCDEL'], mysql_error()));

		$i=0;

		//The code contained within this while loop creates the WHERE conditions for the DELETE 
		//statement. 

		while($row = mysql_fetch_array($describe_result))
		{
			$current_field = $row['Field'];
			$current_delete_value = $delete_values[$i];
			$fields_to_delete[$i] = "`$current_field`=$current_delete_value";
			$i++;
		}

		$delete_conditions = implode(" AND ", $fields_to_delete);

		//Create the string for the delete query.
		$delete_query = "DELETE FROM `$table` WHERE $delete_conditions LIMIT 1";
	}
	mysql_query($delete_query) or die(sprintf($strings['BROWSE_ERR_DELETE'], mysql_error()));
}

mysql_select_db($db);

//pagination
if(isset($_REQUEST['perpage'])) {
	$perpage = intval($_REQUEST['perpage']);
} else {
	$perpage = 20;
}

if(isset($_REQUEST['page'])) {
	$start = 20 * ($_REQUEST['page'] - 1);
	$current_page = intval($_REQUEST['page']);
	if($start < 0)			//can't hurt to have another check, and a non-int
		$start = 0;		//$page will evaluate to 0.
	$end = $start + $perpage;
} else {
	$current_page = 1;
	$start = 0;
	$end = $perpage;
}

$query = "SELECT * FROM `$table` LIMIT $start, $perpage";		//MySQL, incidentally, uses LIMIT offset, count
($items = mysql_query($query)) or die('TODO L10N: ' . mysql_error());

//we also need to know how many results there are in total, for pagination purposes
$query = "SELECT COUNT(*) FROM `$table`";
$total_rows = mysql_result(mysql_query($query), 0, 0);


//finally, we need to get all the different columns
$query = "DESCRIBE `$table`";
$results = mysql_query($query);

$fieldcount = mysql_num_rows($results);

$pk = false;

$hrow = '';
while($heading = mysql_fetch_array($results)) {
	$hrow .= '<th class="firstrow">' . htmlspecialchars($heading['Field'], ENT_QUOTES) . '</th>';
	//check for primary keyness
	if($heading['Key'] == "PRI") {
		$pk = $heading['Field'];
	}
}

//Check if we have a primary key
//If we don't, print a warning
if(!$pk) {
	echo('<p class="warning">' . $strings['BROWSE_WARN_NOPK'] . '</p>');
}

if($edit !== false) {
	echo('<form action="browse.php" method="post">
	<input type="hidden" name="database" value="' . htmlspecialchars($db) . '" />
	<input type="hidden" name="table" value="' . htmlspecialchars($table) . '" />
	<input type="hidden" name="save" value="s" />
');
}

echo('<table class="browse"><thead><tr><th class="firstrow" title="' . $strings['BROWSE_TITLE_ROWCOUNT'] . '">' . $total_rows . '</th>');

echo($hrow . '</tr></thead><tbody>');

$crow = 0;
while($row = mysql_fetch_array($items)) {
	echo('<tr');
	if($crow % 2 == 0)
		echo(' class="banded"');
	echo('>');

	echo("<td class=\"firstcol\"><a href=\"browse.php?database=" . htmlspecialchars(($db), ENT_QUOTES) . "&amp;table=" . htmlspecialchars(($table), ENT_QUOTES) . "&amp;delete&amp;");

	if($pk) {
		//There's a primary key, so we use that for all this complicated trickery
		//TODO: Support multiple primary keys
		echo("hypatia___pk=$pk&amp;key=" . htmlspecialchars($row[$pk], ENT_QUOTES));
	} else {
		//There's no primary key, which is a pain
		//We need to read out the values of every field into the GET line
		//If there's a duplicate, it shouldn't matter (because we LIMIT 1 at the
		//SQL query), except that the first duplicate will be the one deleted.
		//If people have a problem with this, they will have to add a primary key
		//(we even print out a warning about this!)
		for($i = 0; $i < $fieldcount; $i++) {
			echo('field' . $i . '=' . htmlspecialchars($row[$i], ENT_QUOTES));
			if($i != ($fieldcount - 1))
				echo('&amp;');		//separator
		}
	}

	echo("\" onclick=\"return confirm('$strings[BROWSE_DELETECONFIRMATION]');\">$strings[BROWSE_DELETE]</a>/"); 

	if($edit !== false && $crow == $edit) {
		//we need to edit this row, using a form
		echo('<input type="submit" name="save" value="' . $strings['BROWSE_SAVE'] . '" />');
		if(isset($_REQUEST['hypatia___pk'])) {
			echo('<input type="hidden" name="hypatia___pk" value="' . htmlspecialchars($_REQUEST['hypatia___pk'], ENT_QUOTES) . '" />');
			echo('<input type="hidden" name="key" value="' . htmlspecialchars($_REQUEST['key'], ENT_QUOTES) . '" />');
		} else {
			//otherwise, we use the edit_conditions variable built for us
			echo('<input type="hidden" name="edit_conditions" value="' . htmlspecialchars($edit_conditions, ENT_QUOTES) . '" />');
		}
	} else {
		echo("<a href=\"browse.php?database=" . htmlspecialchars(($db)) . "&amp;table=" . htmlspecialchars(($table), ENT_QUOTES) . "&amp;edit&amp;rownum=$crow&amp;");

		if($pk) {
			//There's a primary key, so we use that for all this complicated trickery
			//TODO: Support multiple primary keys
			echo("hypatia___pk=" . htmlspecialchars(($pk), ENT_QUOTES) . "&amp;key=" . htmlspecialchars($row[$pk], ENT_QUOTES));
		} else {
			//There's no primary key, which is a pain
			//We need to read out the values of every field into the GET line
			//If there's a duplicate, it shouldn't matter (because we LIMIT 1 at the
			//SQL query), except that the first duplicate will be the one deleted.
			//If people have a problem with this, they will have to add a primary key
			//(we even print out a warning about this!)
			for($i = 0; $i < $fieldcount; $i++) {
				echo('field' . $i . '=' . htmlspecialchars($row[$i], ENT_QUOTES));
				if($i != ($fieldcount - 1))
					echo('&amp;');		//separator
			}
		}
		echo("\">$strings[BROWSE_EDIT]</a>");
	}

	echo('</td>');

	for($i = 0; $i < $fieldcount; $i++) {
		if($edit !== false && $crow == $edit) {
			//echo the new value form
			echo('<td><input type="text" name="field' . $i . '" value="' . htmlspecialchars($row[$i], ENT_QUOTES) . '" /></td>');
		} else {
			echo('<td>' . htmlspecialchars($row[$i], ENT_QUOTES) . '</td>');
		}
	}

	echo('</tr>');
	$crow++;
}

echo('</tbody></table>');
if($edit !== false) {
	echo('</form>');
}


//more pagination stuff
if($total_rows > $perpage) {
	//print page information
	for($i = 0; $i < $total_rows; $i += $perpage) {
		if((($i / $perpage) + 1) == $current_page)
			echo(($i / $perpage) + 1);
		else {
			echo("<a href=\"browse.php?database=$db&amp;table=$table&amp;perpage=$perpage&amp;page=" . (($i / $perpage) + 1) . "\">" . (1 + ($i / $perpage)) . "</a>");
		}
		if($i + $perpage < $total_rows) 
			echo (' | ');			//separator
	}
}

echo('<h2>' . $strings['BROWSE_ADDRECORD'] . '</h2>
<form action="browse.php" method="post">
<input type="hidden" name="database" value="' . htmlspecialchars($db) . '" />
<input type="hidden" name="table" value="' . htmlspecialchars($table) . '" /><dl>
');

//The new record section
//For convienience, we read out the names and put them into various
//appropriate text boxes
//TODO: validation

$query = "DESCRIBE `$table`";
$results = mysql_query($query);

$i = 0;
while($r = mysql_fetch_row($results)) {
	$i++;
	echo("<dt>" . htmlspecialchars($r[0]) . "</dt><dd>" . generateInputField($r,"field{$i}") . "</dd>");
}

echo('</dl><input type="submit" name="insert" value="Add Record" /></form></div>');


endhtml();
?>
Return current item: HypatiaDB