1) $where .= "("; foreach ($nameArray as $name) { $where .= " sh.storm_name = '$name'"; if ($size > 1 && $i < $size) { $where .= " OR "; $i++; } } if ($size > 1) $where .= ")"; } // If user selects one or more years // if ($ya) { $i = 1; $size = sizeof($yearArray); $where .= " AND "; if ($size > 1) $where .= "("; foreach ($yearArray as $year) { $where .= "YEAR(sh.start_date) = '$year'"; if ($size > 1 && $i < $size) { $where .= " OR "; $i++; } } if ($size > 1) $where .= ")"; } // If user selects one or more months // if ($ma) { $i = 1; $size = sizeof($monthArray); $where .= " AND "; if ($size > 1) $where .= "("; foreach ($monthArray as $month) { $where .= "MONTH(sh.start_date) = '$month'"; if ($size > 1 && $i < $size) { $where .= " OR "; $i++; } } if ($size > 1) $where .= ")"; } // If user selects one or more days of the month // if ($da) { $i = 1; $size = sizeof($dayArray); $where .= " AND "; if ($size > 1) $where .= "("; foreach ($dayArray as $day) { $where .= "DAY(sh.start_date) = '$day'"; if ($size > 1 && $i < $size) { $where .= " OR "; $i++; } } if ($size > 1) $where .= ")"; } // If user selects one or more storm types (Saffir-Simpson) // if ($ta) { $size = sizeof($typeArray); $where .= " AND "; if ($size > 1) $where .= "("; for ($i=0; $i<$size; $i++) { switch($typeArray[$i]) { case "Extratropical Storm": $where .="so.storm_type='E'"; break; case "Hurricane - Category 1": $where .="(so.storm_type='*' AND so.wind_speed >= 64 AND so.wind_speed <= 82) "; break; case "Hurricane - Category 2": $where .="(so.storm_type='*' AND so.wind_speed >= 83 AND so.wind_speed <= 95) "; break; case "Major Hurricane - Category 3": $where .="(so.storm_type='*' AND so.wind_speed >= 96 AND so.wind_speed <= 113) "; break; case "Major Hurricane - Category 4": $where .="(so.storm_type='*' AND so.wind_speed >= 114 AND so.wind_speed <= 135) "; break; case "Major Hurricane - Category 5": $where .="(so.storm_type='*' AND so.wind_speed >= 136) "; break; case "Tropical Storm": $where .="(so.storm_type='*' AND so.wind_speed >= 33 AND so.wind_speed <= 63) "; break; case "Tropical Depression": $where .="((so.storm_type='*' OR so.storm_type='D') AND so.wind_speed <= 32) "; break; case "Subtropical Storm": $where .="so.storm_type='S' "; break; case "Tropical Disturbance": $where .="(so.storm_type='W' OR so.storm_type='L')"; break; } if ($i < $size - 1) { $where .= " OR "; } } if ($size > 1) $where .= ")"; } // If user selects one or more states // if ($ula) { $i = 1; $size = sizeof($USLocArray); $where .= " AND "; if ($size > 1) $where .= "("; foreach ($USLocArray as $USLoc) { $where .= " sl.loc_US = '$USLoc'"; if ($size > 1 && $i < $size) { $where .= " OR "; $i++; } } if ($size > 1) $where .= ")"; } if ($ulc) { $i = 1; $size = sizeof($categoryArray); $where .= " AND "; if ($size > 1) $where .= "("; foreach ($categoryArray as $cat) { $where .= " sl.cat_loc_US = '$cat'"; if ($size > 1 && $i < $size) { $where .= " OR "; $i++; } } if ($size > 1) $where .= ")"; } // other constraint variables that can be given in boolean (AND/OR) combinations // $varAssoc = array("Latitude (-90 to 90)"=>"so.latitude", "Longitude (-180 to 180)"=>"so.longitude", "Direction (0-360, N=0)"=>"so.direction", "Speed (kts)"=>"so.translational_speed", "Windspeed (kts)"=>"so.wind_speed", "Pressure (mb)"=>"so.central_pressure"); // the value of a constraint (must be numeric) // if (strlen($textArray[0])) { $i = 0; $where .= " AND "; foreach ($textArray as $text) { if (!is_numeric($textArray[$i])) continue; $size = sizeof($varArray); if ($size > 1) $where .= "("; foreach ($varArray as $var) { $where .= $varAssoc[$var]." ". $opArray[$i]." ". $textArray[$i]; if ($size > 1 && $i < $size) { // if user chooses AND or OR to add another constraint clause if ($boolArray[$i] != "More..?") $where .= " ".$boolArray[$i]." "; $i++; } } if ($size > 1) $where .= ")"; } } if( $ba ) { $where .= " AND basin='$basin'"; } if( $di ) { // emulate distance functionality /*$radius_squared = $distance*$distance; $mq = "SET @center = GeomFromText('POINT($longitude $latitude)')@@"; $mq .= "SET @bbox = CONCAT('POLYGON(('," . "X(@center) - $distance, ' ', Y(@center) - $distance, ','," . "X(@center) + $distance, ' ', Y(@center) - $distance, ','," . "X(@center) + $distance, ' ', Y(@center) + $distance, ','," . "X(@center) - $distance, ' ', Y(@center) + $distance, ','," . "X(@center) - $distance, ' ', Y(@center) - $distance, '))')@@"; $q = $mq.$q; $q .= " AND INTERSECTS( coordinate, GeomFromText(@bbox) ) "; $q .= "AND POW( ABS( X(coordinate) - X(@center)), 2) + POW( ABS(Y(coordinate) - Y(@center)), 2 ) $distance_op $radius_squared "; */ $where .= " AND DISTANCE_PLANAR(POINTFROMWKB( POINT($longitude, $latitude) ),coordinate)*$uom_distance $distance_op $distance"; } if (!$so) { $sq .= $where . " AND so.storm_id = sh.storm_id"; if($ula || $ulc) $sq .= " AND sl.storm_id = sh.storm_id"; } // If user does NOT request only the data points selected (by checking the checkbox), close the 'where' clause if (!$so) { $sub_query = mysql_query($sq); $storm_ids="''"; while($storm_ids_fetch = mysql_fetch_row($sub_query)) { $storm_ids .= ','.$storm_ids_fetch[0]; } $q .= "$storm_ids)"; } else { $q .= $where; } // order the returned data $q .= " ORDER BY sh.start_date, sh.storm_name, sh.storm_id, so.obs_date"; // let the user see the MySQL statement generated by his/her menu selections // print "
"; print " Generated MySQL Statement:    $sq
$q
"; print "
"; // submit the query(ies) to the database // $q_arr = explode("@@",$q); foreach($q_arr as $query) { $rs = mysql_query($query); } // the result set of the last query is the only one that really matters if (! $rs) { $error = mysql_error(); print "Problem with database query: ($error)"; } print "
"; // create a definition list print "
"; print "Requested Storm Tracks: 0

"; // parse the result set and build data tables // $prevName = ""; $prevStormId = -1; $tableId = 0; $rowNum = 1; while ($row = mysql_fetch_object($rs)) { // If the storm-name or storm id changed, check if this is the first storm in the result if ($row->storm_id != $prevStormId) { // if this is not the first storm name in query result set, close current table if ($prevStormId!=-1) { print ""; print ""; print "
"; print ""; } $prevName = $row->storm_name; $prevStormId = $row->storm_id; $year = substr($row->start_date, 0, 4); $stormNumberInYearId = $row->storm_num; // start a new table $tableId++; $rowNum = 1; print "
"; print " " .$year. ' ' . $prevName . " " . $year . " " . 'Storm #'.$stormNumberInYearId . ""; print "
"; // each table is in a
, which is opened/closed when its icon is clicked print "
"; print "
"; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; } switch($row->storm_type) { case "E": $storm_type ="Extratropical Storm"; break; case "*": if($row->wind_speed <= 32) $storm_type = "Tropical Depression"; if($row->wind_speed >= 33 && $row->wind_speed <= 63) $storm_type = "Tropical Storm"; if($row->wind_speed >= 64 && $row->wind_speed <= 82) $storm_type = "Hurricane - Category 1"; if($row->wind_speed >= 83 && $row->wind_speed <= 95) $storm_type = "Hurricane - Category 2"; if($row->wind_speed >= 96 && $row->wind_speed <= 113) $storm_type = "Major Hurricane - Category 3"; if($row->wind_speed >= 114 && $row->wind_speed <= 135) $storm_type = "Major Hurricane - Category 4"; if($row->wind_speed >= 136) $storm_type = "Major Hurricane - Category 5"; break; case "D": $storm_type = "Tropical Depression"; break; case "S": $storm_type = "Subtropical Storm"; break; case "L": case "W": $storm_type = "Tropical Disturbance"; break; } $prevStormId = $row->storm_id; // this table/row id is used in draw_map.js to open the appropriate table // (and highlight the correct row) for the selected track (or point in track) // $idStr = $tableId.'_'.$rowNum.'_'.$year; print ""; // print a table row print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; print ""; $rowNum++; } // close the last table print ""; print "
Point#Date-TimeLatitudeLongitudeDirectionSpeedWindspeedPressureStorm Type
$rowNum".$row->obs_date."".$row->latitude."".$row->longitude."".$row->direction."".$row->translational_speed."".$row->wind_speed."".$row->central_pressure."".$storm_type.""; print "
"; print "
"; print ""; print ""; // close tablesDiv print ''; ?>