";
}
if ($fieldToUpdate == "g_title") {
$fieldToUpdateWordSeparator = "-"; // just a space for titles
echo "THIS SCRIPT IS READING and possibly UPDATING g_title
";
}
//grab ID from URL if available ?id=0 or ?id=353
if (isset($_GET['begin_id'])) {
$beginId = intval($_GET['begin_id']);
}
//grab whether to INSERT into database from URL if available
if (isset($_GET['insert'])) {
$insertArray = $_GET['insert'];
}
if ($insertArray) {
echo "THIS SCRIPT IS UPDATING AND COMMITING TO DATABASE. I HOPE IT IS WHAT YOU WANTED TO DO!
";
}
else {
echo "This is only a test run. Nothing is being commited to the database.
";
}
//function call starting real work here
selectArray($database_array, $insertArray, $fieldToUpdate, $beginId, $fieldToUpdateWordSeparator);
/* db_connect
Create connection to database
*/
function db_connect($database_array) {
$result = mysql_pconnect($database_array["db_server"], $database_array["db_username"], $database_array["db_password"]);
if (!$result)
{
echo '
SCRIPT CANNOT LOGIN TO DATABASE';
// return false;
}
if (!mysql_select_db($database_array["db_name"]))
{
echo '
SCRIPT CANNOT FIND DATABASE NAMED '.$database_array["db_name"];
//return false;
}
return $result;
}
/* insertArray
Updates the database with the NEW data
*/
function insertArray($database_array, $fieldToUpdate, $fieldToUpdate, $item_id, $new_value) {
$conn = db_connect($database_array);
// print_r($new_keywords);
echo "
UPDATING DATABASE";
$sql = "UPDATE g2_Item
SET ".$fieldToUpdate."='".$new_value."'
WHERE g_id='".$item_id."'";
//echo "
".$sql;
$result = mysql_query($sql, $conn);
if ($result) {
echo "
NUMBER OF UPDATED ROWS: ".$result."
";
}
} // end function
/* selectArray
Grabs the OLD data from the database
*/
function selectArray($database_array, $insertArray, $fieldToUpdate, $beginId, $fieldToUpdateWordSeparator)
{
// if you know the item ID you want to start with use
// "WHERE g_description is null and g_id > 2745"
// for all items with an ID greater than 2745
// to find the item number, log in and go to edit the item,
// something similiar to "g2_itemId=2745" will be in the URL
$conn = db_connect($database_array);
$sql = "SELECT g_id, g_description, g_title
FROM g2_Item
WHERE g_description is null AND g_id > ".$beginId;
// FOR SQL search based on title use something like the following
// which will grab any item that contains "wedding_party" in the title:
// "g_description is null AND g_title like '%wedding_party%'"
// use "g_description is null" in order to get rid of albums
$result = mysql_query($sql, $conn);
// for debugging sql statement
echo "
YOUR SQL STATEMENT:
";
echo $sql;
echo "
";
$common_words_filter = array('a', 'about', 'above', 'across', 'act', 'add', 'after', 'again', 'against', 'all', 'along', 'also', 'amid', 'among', 'an', 'and', 'any', 'are', 'around', 'as', 'at', 'back', 'be', 'been', 'before', 'behind', 'below', 'beneath', 'beside', 'besides', 'between', 'beyond', 'but', 'by', 'came', 'can', 'cause', 'change', 'close', 'come', 'concerning', 'considering', 'could', 'cover', 'cross', 'despite', 'did', 'differ', 'do', 'does', 'down', 'draw', 'during', 'each', 'even', 'every', 'except', 'excepting', 'excluding', 'far', 'few', 'find', 'follow', 'following', 'for', 'found', 'four', 'from', 'get', 'give', 'go', 'grow', 'had', 'hard', 'has', 'have', 'he', 'her', 'here', 'high', 'him', 'his', 'how', 'if', 'in', 'inside', 'into', 'is', 'it', 'just', 'know', 'large', 'last', 'late', 'left', 'let', 'like', 'little', 'long', 'look', 'made', 'make', 'many', 'may', 'might', 'minus', 'more', 'most', 'move', 'much', 'must', 'my', 'near', 'never', 'no', 'now', 'of', 'off', 'on', 'one', 'only', 'onto', 'opposite', 'or', 'other', 'our', 'out', 'outside', 'over', 'own', 'part', 'past', 'per', 'plus', 'put', 'regarding', 'right', 'said', 'same', 'saw', 'say', 'see', 'self', 'set', 'she', 'should', 'side', 'small', 'so', 'some', 'still', 'such', 'take', 'tell', 'than', 'that', 'the', 'their', 'them', 'then', 'there', 'these', 'they', 'thing', 'this', 'three', 'through', 'to', 'too', 'toward', 'towards', 'try', 'two', 'under', 'underneath', 'unlike', 'until', 'up', 'upon', 'us', 'use', 'versus', 'very', 'via', 'view', 'want', 'was', 'way', 'we', 'went', 'were', 'what', 'when', 'where', 'which', 'while', 'who', 'why', 'will', 'with', 'within', 'without', 'would', 'you', 'your');
if ($result) {
echo "
ITEMS SELECTED FROM DATABASE: ";
for ($i=0; $i < mysql_num_rows($result); $i++) {
$row = mysql_fetch_array( $result);
// output items found
echo "
".$i.". Item ID: ".$row['g_id']." | ".$row['g_title']." | ";
$item_id[$i] = $row['g_id']; //store id
// if inserting titles
if ($fieldToUpdate == "g_title") {
// REGULAR EXPRESSIONS
// --------------------------------------------
$row['g_title'] = preg_replace("/[0-9]+/","",$row['g_title']); // remove all digits
$row['g_title'] = preg_replace("/copy/i","",$row['g_title']); // remove 'copy' a common OS-X filename suffix
$row['g_title'] = preg_replace("/__/","_",$row['g_title']); // remove double underscores
$row['g_title'] = preg_replace("/^_/","",$row['g_title']); // remove underscorces at beginning
$row['g_title'] = preg_replace("/_$/","",$row['g_title']); // remove underscorces at end
$row['g_title'] = preg_replace("/\(/","",$row['g_title']); // remove left parenthesis
$row['g_title'] = preg_replace("/\)/","",$row['g_title']); // remove right parenthesis
$row['g_title'] = preg_replace("/-/"," ",$row['g_title']); // replace hyphen with space
$row['g_title'] = preg_replace("/_/"," ",$row['g_title']); // replace underscore with space
echo " | EDITED FORMAT: ".$row['g_title']; // show progress on HTML page
$title = $row['g_title']; // character, space, to split title words
}
// if inserting keywords
if ($fieldToUpdate == "g_keywords") {
$row['g_title'] = preg_replace("/(&)|-|:|,/","",$row['g_title']); // replace some symbols with empty
$row['g_title'] = preg_replace("/[0-9]+/","",$row['g_title']); // remove all digits
$row['g_title'] = preg_replace("/\s+/"," ",$row['g_title']); // remove all digits
echo $row['g_title'];
$keywords_split[$i] = explode(" ", $row['g_title']); // character, space, to split title words
$keywords_split[$i] = array_map('strtolower', $keywords_split[$i]); // convert to lowercase
$keywords_split[$i] = array_diff($keywords_split[$i], $common_words_filter); // remove common English words from keyword list
for ($j=0; $j < count($keywords_split[$i]); $j++) {
if ($keywords_split[$i][$j] == "") {
unset($keywords_split[$i][$j]); // unset if empty
}
$keywords_split[$i] = array_values($keywords_split[$i]);
}
$new_keywords[$i] = implode($fieldToUpdateWordSeparator, $keywords_split[$i]);
echo " | EDITED FORMAT: ".$new_keywords[$i]; // show progress on HTML page
}
// prepare variables for database
if ($fieldToUpdate == "g_title") {
$new_entry = $title;
}
if ($fieldToUpdate == "g_keywords") {
$new_entry = $new_keywords[$i];
}
// actual insert SQL
if ($insertArray) {
insertArray($database_array,$fieldToUpdate, $fieldToUpdate, $item_id[$i], $new_entry); // commit to database
}
} // for
} // if result
else { // no SQL rows returned
echo "
EMPTY SQL return";
}
} // end selectArray()
?>