function extractFormElementsBrowseMyRefs($showLinks, $loginEmail, $userID)
{
// IMPORTANT NOTE: Browse functionality is NOT fully implemented yet!!
global $tableRefs, $tableUserData;
// defined in 'db.inc.php'
$browseFieldSelector = $_REQUEST['browseFieldSelector'];
// extract field name chosen by the user
// construct the SQL query:
// TODO: build the complete SQL query using functions 'buildFROMclause()' and 'buildORDERclause()'
// if the chosen field can contain multiple items...
// TODO: we really should check here if the corresponding 'ref_...' table exists!
if (preg_match("/^(author|keywords|editor|language|summary_language|area|location|user_keys|user_groups)\$/i", $browseFieldSelector)) {
list($refTableName, $browseFieldName) = buildRefTableAndFieldNames($browseFieldSelector);
// get correct table name and field name for the 'ref_...' table that matches the chosen field
$browseFieldColumnName = " AS " . preg_replace("/^ref_(\\w+)\$/i", "\\1", $browseFieldName);
// strip the 'ref_' prefix for the column name
$queryRefTableLeftJoinPart = " LEFT JOIN {$refTableName} ON serial = ref_id";
// ...add the appropriate 'LEFT JOIN...' part to the 'FROM' clause
if (preg_match("/^(user_keys|user_groups)\$/i", $browseFieldSelector)) {
$queryRefTableLeftJoinPart .= " AND ref_user_id = " . quote_smart($userID);
}
// add the user's user_id as additional condition to this 'LEFT JOIN...' part
} else {
$browseFieldName = $browseFieldSelector;
$browseFieldColumnName = "";
$queryRefTableLeftJoinPart = "";
}
$query = buildSELECTclause("Browse", $showLinks, "", false, false, "", $browseFieldName . $browseFieldColumnName);
// function 'buildSELECTclause()' is defined in 'include.inc.php'
// if a user specific field was chosen...
if (preg_match("/^(marked|copy|selected|user_keys|user_notes|user_file|user_groups|cite_key|related)\$/i", $browseFieldSelector)) {
$query .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = " . $userID;
} else {
$query .= " FROM {$tableRefs}";
}
// add FROM clause
$query .= $queryRefTableLeftJoinPart;
// add additional 'LEFT JOIN...' part (if required)
$query .= " WHERE location RLIKE " . quote_smart($loginEmail);
// add (initial) WHERE clause
$query .= " GROUP BY {$browseFieldName}";
// add the GROUP BY clause
$query .= " ORDER BY records DESC, {$browseFieldName}";
// add the default ORDER BY clause
return $query;
}
function createNewTableWithParsedTableData($fieldName, $delim)
{
global $loginUserID;
// saved as session variable on login
global $tableRefs, $tableUserData;
// defined in 'db.inc.php'
if (preg_match("/^(user_keys|user_notes|user_file|user_groups)\$/", $fieldName)) {
$query = "SELECT {$fieldName}, record_id, user_id FROM {$tableUserData}";
// WHERE user_id = " . $loginUserID
$userIDTableSpec = "ref_user_id MEDIUMINT UNSIGNED NOT NULL, ";
} else {
$query = "SELECT {$fieldName}, serial FROM {$tableRefs}";
$userIDTableSpec = "";
}
$result = queryMySQLDatabase($query);
$fieldValuesArray = array();
// initialize array variable which will hold the splitted sub-items
// split field values on the given delimiter:
for ($i = 0; $row = @mysql_fetch_array($result); $i++) {
$fieldSubValuesArray = preg_split($delim, $row[$fieldName]);
// split field contents on '$delim' (which is interpreted as perl-style regular expression!)
foreach ($fieldSubValuesArray as $fieldSubValue) {
// // NOTE: we include empty values so that any Browse view query will also display the number of records where the given field is empty
// if (!empty($fieldSubValue))
// {
$fieldSubValue = trim($fieldSubValue);
if ($fieldName == "author") {
$fieldSubValue = trimTextPattern($fieldSubValue, " *\\(eds?\\)", false, true);
}
// remove any existing editor info from the 'author' string, i.e., kill any trailing " (ed)" or " (eds)"
// copy the individual item (as string, ready for database insertion) to the array:
if (preg_match("/^(user_keys|user_notes|user_file|user_groups)\$/", $fieldName)) {
$fieldValuesArray[] = "(NULL, \"" . addslashes($fieldSubValue) . "\", {$row['record_id']}, {$row['user_id']})";
} else {
$fieldValuesArray[] = "(NULL, \"" . addslashes($fieldSubValue) . "\", {$row['serial']})";
}
// }
}
}
// build correct 'ref_...' table and field names:
list($tableName, $fieldName) = buildRefTableAndFieldNames($fieldName);
// NOTE: the below query will only work if the current MySQL user is allowed to CREATE tables ('Create_priv = Y')
// therefore, the CREATE statements should be moved to 'update.sql'!
$queryArray[] = "CREATE TABLE " . $tableName . " (" . $fieldName . "_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, " . $fieldName . " VARCHAR(255), " . "ref_id MEDIUMINT UNSIGNED NOT NULL, " . $userIDTableSpec . "INDEX (" . $fieldName . "_id, " . $fieldName . ", ref_id))";
// TODO: Sanitize with quote_smart
foreach ($fieldValuesArray as $fieldValue) {
$queryArray[] = "INSERT INTO " . $tableName . " VALUES " . $fieldValue;
}
// inserting all values at once may cause 'URL too long' server errors:
// $fieldValuesString = implode(", ", $fieldValuesArray); // merge array
// $queryArray[] = "INSERT INTO " . $tableName . " VALUES " . $fieldValuesString;
// RUN the queries on the database through the connection:
foreach ($queryArray as $query) {
$result = queryMySQLDatabase($query);
}
return $tableName;
}
