<?php
  require_once ('../../../mysqli_connect_online_calcs_db_MYSQLI.php');

  //pull the 'timezone' (e.g., "Europe/Andorra") for this city - I could use "geoname_cities500" (194,796) instead of "geoname_cities1000" (136,581)
  if (substr($country1, 0, 2) == "US")                         //example, $country1 = "US.NY",  $city1 = "Brooklyn",  $county = "Kings County",   $county_index1 = "047"
  {
    $the_country_code = substr($country1, 0, 2);               //"US";
    $the_admin1 = substr($country1, -2);                        //"NY" - $the_admin1 is the state
    $the_admin2 = $county_index1;

    $sql = "SELECT * FROM `geoname_cities1000` WHERE ascii_name='$city1' And country_code='$the_country_code' And admin1='$the_admin1' And admin2='$county_index1' LIMIT 1";
  }
  else
  {
    $the_country_code = substr($country1, 0, 2);                //like "BA.01" - for Sarajevo
    $the_admin1 = substr($country1, 3);                        //like "01"
    $the_admin2 = $county_index1;                               //like 3343737

    $sql = "SELECT * FROM `geoname_cities1000` WHERE ascii_name='$city1' And country_code='$the_country_code' And admin1='$the_admin1' LIMIT 1";
  }

  $result = mysqli_query($conn, $sql);

  $row = @mysqli_fetch_array($result, MYSQLI_ASSOC);

  $geonameid = $row['geonameid'];
  $timezone_literal = trim($row['timezone']);

  $dt = $year1 . "-" . $month1 . "-" . $day1 . " " . $hour1 . ":" . $minute1 . ":00";           //"1974-01-06 02:00:00"

  $date = new DateTime($dt);      //247177801 (db)   PHPed returns 247195801 (18000 difference)
  $formatted_date = $date->format('Y-m-d H:i:s');

//==============================================================================

  //I could not find the city, so I am forced to use geonames data
  if (substr($country1, 0, 2) == "US")
  {
    $sql = "SELECT * FROM `zone` WHERE zone_name='$timezone_literal' AND country_code='$the_country_code' LIMIT 1";    //must find zone_id
  }
  else
  {
    $sql = "SELECT * FROM `zone` WHERE zone_name='$timezone_literal' AND country_code='$the_country_code' LIMIT 1";    //must find zone_id
  }

  $result = mysqli_query($conn, $sql);

  $row = @mysqli_fetch_array($result, MYSQLI_ASSOC);
  $zone_id = $row['zone_id'];
  $the_zone_name = $row['zone_name'];
  $the_cntry_code = $row['country_code'];

//==============================================================================

  //this finds the last entry in the "tz_new" table for the particular current $zone_id
  $sql = "SELECT * FROM `tz_new` WHERE dt <= '2099-12-31' AND zone_id='$zone_id' ORDER BY dt DESC LIMIT 1";
  $result = mysqli_query($conn, $sql);

  $row = @mysqli_fetch_array($result, MYSQLI_ASSOC);

  //if my $formatted_date >= the $row['dt'] returned above, then I must use the data in the last record
  //for this $zone_id, otherwise, do another query to find the correct info.
  if ($formatted_date >= $row['dt'])
  {
    $sql5 = "SELECT * FROM `tz_new` WHERE zone_id='$zone_id'";      //was  ORDER BY dt
    $result5 = mysqli_query($conn, $sql5);

    $number_of_recs = mysqli_num_rows($result5);

    while ($row5 = @mysqli_fetch_array($result5, MYSQLI_ASSOC))
    {
      //this just gets the very last record for this $zone_id into $row[]
      $row['gmt_offset'] = $row5['gmt_offset'];
      $row['dst'] = $row5['dst'];
    }
  }
  else
  {
    $sql5 = "SELECT * FROM `tz_new` WHERE zone_id='$zone_id'";
    $result5 = mysqli_query($conn, $sql5);

    $number_of_recs = mysqli_num_rows($result5);

    while ($row5 = @mysqli_fetch_array($result5, MYSQLI_ASSOC))
    {
      if ($row5['dt'] >= $formatted_date)          //check to see when the database date is greater than the birth date - if so, then break
      {
        break;
      }
      else
      {
        $row['gmt_offset'] = $row5['gmt_offset'];  //record last record's data - used when we break with the above break statement
        $row['dst'] = $row5['dst'];
      }
    }
  }

//==============================================================================

  $the_tz = ($row['gmt_offset'] / 3600);

  $the_dst = $row['dst'];

  if(intval($row['dst']) == 0)
  {
    $the_time_type =  "Standard Time";
  }
  else
  {
    $the_time_type =  "Daylight Saving Time";
  }

done:
  $t = $t;

Function Convert_abbrev_to_full_name($t)
{
  if ($t == "AL") { return "Alabama"; }
  if ($t == "AK") { return "Alaska"; }
  if ($t == "AZ") { return "Arizona"; }
  if ($t == "AR") { return "Arkansas"; }
  if ($t == "CA") { return "California"; }
  if ($t == "CO") { return "Colorado"; }
  if ($t == "CT") { return "Connecticut"; }
  if ($t == "DE") { return "Delaware"; }
  if ($t == "DC") { return "Dist of Columbia"; }
  if ($t == "FL") { return "Florida"; }
  if ($t == "GA") { return "Georgia"; }
  if ($t == "HI") { return "Hawaii"; }
  if ($t == "ID") { return "Idaho"; }
  if ($t == "IL") { return "Illinois"; }
  if ($t == "IN") { return "Indiana"; }
  if ($t == "IA") { return "Iowa"; }
  if ($t == "KS") { return "Kansas"; }
  if ($t == "KY") { return "Kentucky"; }
  if ($t == "LA") { return "Louisiana"; }
  if ($t == "ME") { return "Maine"; }
  if ($t == "MD") { return "Maryland"; }
  if ($t == "MA") { return "Massachusetts"; }
  if ($t == "MI") { return "Michigan"; }
  if ($t == "MN") { return "Minnesota"; }
  if ($t == "MS") { return "Mississippi"; }
  if ($t == "MO") { return "Missouri"; }
  if ($t == "MT") { return "Montana"; }
  if ($t == "NE") { return "Nebraska"; }
  if ($t == "NV") { return "Nevada"; }
  if ($t == "NH") { return "New Hampshire"; }
  if ($t == "NJ") { return "New Jersey"; }
  if ($t == "NM") { return "New Mexico"; }
  if ($t == "NY") { return "New York"; }
  if ($t == "NC") { return "North Carolina"; }
  if ($t == "ND") { return "North Dakota"; }
  if ($t == "OH") { return "Ohio"; }
  if ($t == "OK") { return "Oklahoma"; }
  if ($t == "OR") { return "Oregon"; }
  if ($t == "PA") { return "Pennsylvania"; }
  if ($t == "PR") { return "Puerto Rico"; }
  if ($t == "RI") { return "Rhode Island"; }
  if ($t == "SC") { return "South Carolina"; }
  if ($t == "SD") { return "South Dakota"; }
  if ($t == "TN") { return "Tennessee"; }
  if ($t == "TX") { return "Texas"; }
  if ($t == "UT") { return "Utah"; }
  if ($t == "VT") { return "Vermont"; }
  if ($t == "VA") { return "Virginia"; }
  if ($t == "WA") { return "Washington"; }
  if ($t == "WV") { return "West Virginia"; }
  if ($t == "WI") { return "Wisconsin"; }
  if ($t == "WY") { return "Wyoming"; }
}

?>
