add_slashes($_GET,get_magic_quotes_gpc()); // Session session_start(); // session_register(); #require("quick_search_results_1.php"); $tpl = new TemplatePower("quick_search_results_1.html"); $tpl->prepare(); $tpl->printToScreen(); flush(); $tpl = new TemplatePower("quick_search_results_2.html"); $tpl->prepare(); require($C->root_class . "/class.Navbar.php"); $nav = new navbar($DB); $nav->numrowsperpage = '20'; // Date Validation // Replace Wild Cards * with % $_GET[p_first_name] = str_replace("*","%",$_GET[p_first_name]); $_GET[p_last_name] = str_replace("*","%",$_GET[p_last_name]); if ( 1820 > $_GET[m_arr_date_start] || $_GET[m_arr_date_start] > 1912 || empty($_GET[m_arr_date_start])) $_GET[m_arr_date_start] = 1820; if ( ( $_GET[m_arr_date_end] < $_GET[m_arr_date_start] ) || $_GET[m_arr_date_end] > 1912 || empty($_GET[m_arr_date_end]) ) $_GET[m_arr_date_end] = 1912; //$string_1 = "p_first_name=$_GET[p_first_name]&p_last_name=$_GET[p_last_name]&co_code=$_GET[co_code]&o_occ=$_GET[o_occ]&m_mid=$_GET[m_mid]&m_arr_date_end=$_GET[m_arr_date_end]&m_arr_date_start=$_GET[m_arr_date_start]"; // (JS) 02/13/2009 Changed m_mid to m_ship; Changed co_code to co_country; added po_port, town, province $string_1 = "p_first_name=$_GET[p_first_name]&p_last_name=$_GET[p_last_name]&co_country=$_GET[co_country]&province=$_GET[province]&town=$_GET[town]&o_occ=$_GET[o_occ]&m_ship=$_GET[m_ship]&po_port=$_GET[po_port]&m_arr_date_end=$_GET[m_arr_date_end]&m_arr_date_start=$_GET[m_arr_date_start]"; // Both first name and Last name entered if( empty($_SESSION[sort_type]) ) { if ( !empty($_GET[p_first_name]) && !empty($_GET[p_last_name]) ) $_SESSION[sort_type] = 'p_last_name'; else if ( !empty($_GET[p_first_name]) && empty($_GET[p_last_name]) ) $_SESSION[sort_type] = 'p_last_name'; else if ( empty($_GET[p_first_name]) && !empty($_GET[p_last_name]) ) $_SESSION[sort_type] = 'p_first_name'; } if( empty($_SESSION[sort_val]) ) $_SESSION[sort_val] = "DESC"; if( !empty($_GET[type]) && !empty($_GET[sort]) ) { $_SESSION[sort_type] = $_GET[type]; $_SESSION[sort_val] = $_GET[sort]; } // echo $_SESSION[sort_type]; // Debug // get sort ordering if($_SESSION[sort_type] == "first") $sort = "p_first_name"; elseif($_SESSION[sort_type] == "last") $sort = "p_last_name"; elseif($_SESSION[sort_type] == "occupation") $sort = "o_occ"; elseif($_SESSION[sort_type] == "age") $sort = "p_age"; elseif($_SESSION[sort_type] == "sex") $sort = "p_gender"; elseif($_SESSION[sort_type] == "arrived") $sort = "m_arr_date"; elseif($_SESSION[sort_type] == "origin") $sort = "co_country"; elseif($_SESSION[sort_type] == "ship") $sort = "m_ship"; elseif($_SESSION[sort_type] == "port") $sort = "po_port"; elseif($_SESSION[sort_type] == "province") $sort = "pr_province"; elseif($_SESSION[sort_type] == "town") $sort = "town"; else $sort = "p_last_name"; if($_SESSION[sort_val] == "desc") { $sort_v = "DESC"; $tpl->assignGlobal("order", "asc"); } else { $sort_v = "ASC"; $tpl->assignGlobal("order", "desc"); } if($sort == "p_first_name") { if($sort_v == "ASC") $tpl->assign("arrow_f", "" ); else $tpl->assignGlobal("arrow_f", "" ); } elseif($sort == "p_last_name") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_l", "" ); else $tpl->assignGlobal("arrow_l", "" ); } elseif($sort == "o_occ") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_o", "" ); else $tpl->assignGlobal("arrow_o", "" ); } elseif($sort == "p_age") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_a", "" ); else $tpl->assignGlobal("arrow_a", "" ); } elseif($sort == "p_gender") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_s", "" ); else $tpl->assignGlobal("arrow_s", "" ); } elseif($sort == "m_arr_date") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_ar", "" ); else $tpl->assignGlobal("arrow_ar", "" ); } elseif($sort == "co_country") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_or", "" ); else $tpl->assignGlobal("arrow_or", "" ); } elseif($sort == "m_ship") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_sh", "" ); else $tpl->assignGlobal("arrow_sh", "" ); } elseif($sort == "po_port") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_po", "" ); else $tpl->assignGlobal("arrow_po", "" ); } elseif($sort == "pr_province") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_pr", "" ); else $tpl->assignGlobal("arrow_pr", "" ); } elseif($sort == "town") { if($sort_v == "ASC") $tpl->assignGlobal("arrow_tn", "" ); else $tpl->assignGlobal("arrow_tn", "" ); } $tpl->assignGlobal("query",stripslashes($string_1)); // Error checking to make sure people have entered search data if ( empty($_GET[m_ship]) && //ship empty($_GET[po_port]) && //port:origin // empty($_GET[m_arr_date_start]) && //year between 1820 and 1913 // empty($_GET[m_arr_date_end]) && //year between 1820 and 1913 empty($_GET[p_first_name]) && //surname empty($_GET[p_last_name]) && //family name empty($_GET[o_occ]) && //ocupation empty($_GET[co_country]) && //country empty($_GET[province]) && //province empty($_GET[town]) && //town (1==1)) // 0==1 to force the condition to fail (debugish code) { // This error condition is handled with JavaScript somewhere else. echo '

ERROR: NO SEARCH CRITERIA..... Please enter search criteria and try your search again

'; //$tpl->printToScreen(); flush(); } else // Do the search { if ( !empty($_GET[p_first_name]) && !empty($_GET[p_last_name]) ){ // Both first name and Last name entered $sql_name_w = " p_first_name LIKE '$_GET[p_first_name]' AND p_last_name LIKE '$_GET[p_last_name]' AND "; } else if ( !empty($_GET[p_first_name]) && empty($_GET[p_last_name]) ){ // Only first name. $sql_name_w = " p_first_name LIKE '$_GET[p_first_name]' AND "; } else if ( empty($_GET[p_first_name]) && !empty($_GET[p_last_name]) ){ // Only last name. $sql_name_w = " p_last_name LIKE '$_GET[p_last_name]' AND "; } else { // Neither first nor last names. $sql_name_w = ""; } $_GET[m_ship] = str_replace("*","%",$_GET[m_ship]); if ( !empty($_GET[m_ship]) ){ $sql_ship = " m_ship like '$_GET[m_ship]' AND "; } else { $sql_ship = " "; } $_GET[po_port] = str_replace("*","%",$_GET[po_port]); if ( !empty($_GET[po_port]) ){ $sql_port = " po_port like '$_GET[po_port]' AND "; } else { $sql_port = " "; } $_GET[o_occ] = str_replace("*","%",$_GET[o_occ]); if ( !empty($_GET[o_occ]) ){ $sql_occupation = " o_occ like '$_GET[o_occ]' AND "; } else { $sql_occupation = " "; } $_GET[co_country] = str_replace("*","%",$_GET[co_country]); if ( !empty($_GET[co_country]) ){ $sql_country = " co_country like '$_GET[co_country]' AND "; } else { $sql_country = " "; } $_GET[province] = str_replace("*","%",$_GET[province]); if ( !empty($_GET[province]) ){ $sql_province = " pr_province like '$_GET[province]' AND "; } else { $sql_province = " "; } $_GET[town] = str_replace("*","%",$_GET[town]); $_GET[town] = str_replace("*","%",$_GET[town]); if ( !empty($_GET[town]) ){ $sql_town = " town like '$_GET[town]' AND "; } else { $sql_town = " "; } // Notice that it is still assumed that both dates are provided // because they are pre-selected on the HTML form, but yet there // is room for failure if they are not present. $select_clause = "SELECT `p_id`, `p_last_name`, `p_first_name`, `p_age`, `p_gender`, `p_literacy`, `de_city`, `p_mid`, `m_mid`, `m_ship`, `m_arr_date`, `co_code`, `co_country`, `o_code`, `o_occ`, `po_code`, `po_port`, YEAR( m_arr_date ) AS m_year, MONTH( m_arr_date ) AS m_month, DAYOFMONTH( m_arr_date ) AS m_day, `pr_province`, `town` "; $where_clause = " WHERE $sql_name_w $sql_ship $sql_port $sql_occupation $sql_country $sql_province $sql_town YEAR(m_arr_date) >= " . $_GET[m_arr_date_start] . " AND YEAR(m_arr_date) <= " . $_GET[m_arr_date_end]; $sql_query = "$select_clause FROM `denorm` $where_clause "; // echo $sql_query; // $count_query = "SELECT COUNT(*) AS d_rows FROM `denorm` $where_clause "; // echo "This should be the result count: $count_query

\n"; // The three lines above should only be commented back in for debugging. // the query continues lengthening on the following lines: /* if ( !empty($_GET[co_code]) && $_GET[co_code] != "-1" ) $sql_query = $sql_query . " AND `co_code` = " . $_GET[co_code] . " "; // Ocupation if ( !empty($_GET[o_code]) && $_GET[o_code] != "-1" ) $sql_query = $sql_query . " AND `o_code` = \"" . $_GET[o_code] ."\" "; // Ships: there are many m_mid's for each specific ship if ( !empty($_GET[m_mid]) && $_GET[m_mid] != "-1" ) { $cond[m_mid] = $_GET[m_mid]; $row = $DB->db_select("headers",$cond); $sql_get_ships = "SELECT * FROM headers WHERE m_ship = '$row[m_ship]'"; $q_id_ships = $DB->sql_query($sql_get_ships); if ( $row = $DB->sql_fetchrow($q_id_ships) ) $sql_ships = "p_mid = $row[m_mid]"; while ( $row = $DB->sql_fetchrow($q_id_ships) ) $sql_ships .= " OR p_mid = $row[m_mid]"; $sql_ships = " ( " . $sql_ships . " ) "; $sql_query = $sql_query . " AND " . $sql_ships; } // The following code is ready to be deployed to "patch" the SQL query, // but it might actually not be needed, so I am commenting it out for the // time being. Notice how the bottom three indexes have the wrong names // and the right commen. This results from the HTML form currently having // those 'wrong' names. $listOfSQLarguments=array(); // creates an empty array. empty($_GET[m_mid]) || ($listOfSQLarguments[]=$_GET[m_mid]); //ship empty($_GET[po_code]) || ($listOfSQLarguments[]=$_GET[p]); //port:origin // empty($_GET[m_arr_date_start]) && //year between 1820 and 1913 empty($_GET[m_arr_date_start]) || ($listOfSQLarguments[]=$_GET[m_arr_date_start]); //lower // empty($_GET[m_arr_date_end]) && //year between 1820 and 1913 empty($_GET[m_arr_date_end]) || ($listOfSQLarguments[]=$_GET[m_arr_date_end]); //upper empty($_GET[p_first_name]) || ($listOfSQLarguments[]=$_GET[p_first_name]); //surname empty($_GET[p_last_name]) || ($listOfSQLarguments[]=$_GET[p_last_name]); //family name empty($_GET[o_occ]) || ($listOfSQLarguments[]=$_GET[o_occ]); //ocupation empty($_GET[co_country]) || ($listOfSQLarguments[]=$_GET[co_country]); //country empty($_GET[province]) || ($listOfSQLarguments[]=$_GET[province]); //province empty($_GET[town]) //town || ($listOfSQLarguments[]=$_GET[town]); //town */ $sql_query = $sql_query ." ORDER BY ". $sort ." ". $sort_v; // echo $count_query; // Debug $q_id_count = $DB->sql_query($count_query); $row = $DB->sql_fetchrow($q_id_count); // echo $row[d_rows]; if ( $row[d_rows] > 150000 ) { echo ' This search returns too many records ('; echo $row[d_rows]; echo '). The maximum is 150,000. Please narrow your search and try again. '; $tpl->printToScreen(); flush(); } else { // echo "

"; echo $sql_query; echo "

\n"; // Debug $q_id_main = $nav->execute($sql_query,$_GET[row]); // echo "

"; echo $nav->total_records; echo "

\n"; // Debug // No results if ( $nav->total_records < 1 ) { $tpl->newBlock("no_results"); } else { $tpl->newBlock("results"); } $tpl->assign("total_results",$nav->total_records); if ( empty($_GET[row]) || $_GET[row] < 1 ) $tpl->assign("result_start","1"); else $tpl->assign("result_start",$_GET[row] * $nav->numrowsperpage + 1 ); $result_end = ( $_GET[row] + 1 ) * $nav->numrowsperpage; if ( $result_end > $nav->total_records ) $tpl->assign("result_end","1"); else $tpl->assign("result_end",$result_end); $tpl->assign("link_back",$HTTP_REFERER); $count = 0; while( $row = $DB->sql_fetchrow($q_id_main) ) { $tpl->newblock("p_person"); $count ++; if ( ($count % 2) == 0 ) $tpl->assign("tr_props","class=\"tr-back\""); $tpl->assign("p_id",$row[p_id]); $tpl->assign("p_last_name",$row[p_last_name]); $tpl->assign("p_first_name",$row[p_first_name]); $tpl->assign("p_gender",$row[p_gender]); $tpl->assign("o_occ",$row[o_occ]); $tpl->assign("co_country",$row[co_country]); $tpl->assign("po_port",$row[po_port]); $tpl->assign("pr_province",$row[pr_province]); $tpl->assign("town",$row[town]); $date = mktime(0,0,0,$row[m_month],$row[m_day],2004); $tpl->assign("m_arr_date",$row[m_day] . " " . $FV->num_to_month($row[m_month]) . " " . $row[m_year]); $tpl->assign("m_ship",$row[m_ship]); if ( $row[p_age] == "800" || $row[p_age] <= 0) $tpl->assign("p_age","U"); else if ( $row[p_age] > 110 && $row[p_age] != 800) { $age = $row[p_age] - 900; $tpl->assign("p_age",$age . " m"); } else { $tpl->assign("p_age",$row[p_age]); } } // Assign quick search refine values if ( $nav->total_records < 1 ) { $tpl->newBlock("no_search"); $tpl->assign("qs_p_first_name",ucwords(stripslashes(str_replace("%","*",$_GET[p_first_name])))); $tpl->assign("qs_p_last_name",ucwords(stripslashes(str_replace("%","*",$_GET[p_last_name])))); if ( empty($_GET[m_arr_date_start]) ) $_GET[m_arr_date_start] = 1820; if ( empty($_GET[m_arr_date_end]) ) $_GET[m_arr_date_end] = 1912; $tpl->assign("dm_ds",$HTML->dm_dates("m_arr_date_start",$_GET[m_arr_date_start])); $tpl->assign("dm_de",$HTML->dm_dates("m_arr_date_end",$_GET[m_arr_date_end])); } else { $tpl->newBlock("refine_search"); $tpl->assign("qs_p_first_name",ucwords(stripslashes(str_replace("%","*",$_GET[p_first_name])))); $tpl->assign("qs_p_last_name",ucwords(stripslashes(str_replace("%","*",$_GET[p_last_name])))); if ( empty($_GET[m_arr_date_start]) ) $_GET[m_arr_date_start] = 1820; if ( empty($_GET[m_arr_date_end]) ) $_GET[m_arr_date_end] = 1912; $tpl->assign("dm_ds",$HTML->dm_dates("m_arr_date_start",$_GET[m_arr_date_start])); $tpl->assign("dm_de",$HTML->dm_dates("m_arr_date_end",$_GET[m_arr_date_end])); $tpl->assign("occupation",$HTML->dm_occupation("o_code",$nav->q_id,$_GET[o_code])); $tpl->assign("country",$HTML->dm_country("co_code",$nav->q_id,$_GET[co_Code])); # Somehow the following line is triggering a problem. #$tpl->assign("port",$HTML->dm_port("po_code",$nav->q_id,$_GET[po_code])); $tpl->assign("headers",$HTML->dm_headers("m_mid",$nav->q_id,$_GET[m_mid])); } } } $links = $nav->getlinks("sides", "on", $_GET[row],"&".$string_1 . "&sort=" . $_SESSION[sort_val]."&type=".$_SESSION[sort_type]); $temp_num = $nav->getlinks("all", "on", $_GET[row]); $count=0; $num_pages = count($temp_num)-2; for ($y = 0; $y < count($links); $y++) { if($count==1) { $tpl->newBlock("pages"); if(empty($_GET[row])) $row = 1; else $row = $_GET[row] + 1; $tpl->assign("page", $row." of ".$num_pages. " "); } $tpl->newBlock("pages"); $tpl->assign("page", $links[$y]. " "); $count++; } $end = benchit(); $tpl->assignGlobal("C_Time", round( $end - $start, 3 )); $tpl->assignGlobal("Queries",$DB->num_queries); $tpl->printToScreen(); flush(); ?>