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; }