Solved

PHP MySQL MySQLi Update Insert, and mapping XML data to MySQL Columns

Posted on 2016-11-22
13
21 Views
Last Modified: 2016-11-28
What I need to accomplish:
I need to read through the Zone table (file layouts below) by its primary key, wxzone, then there will be code to fetch an XML file, then I need to UPDATE or INSERT a row into the Forecast table, also with a primary key of zone,with the newly read XML info.  I am skilled in other declarative languages to do this, but I've only done simple PHP projects and modifications of existing code until now.  Pulling it off is not the problem.  Knowledge of how to do it cleanly, maintainably, and efficiently, is what might be lacking.  I'm not asking for a complete example, but rather a strategy with snippets on how you would approach this to accomplish those goals.

The environment:
- The language is PHP 5.3
- The database is MySQL

These are my thoughts  based on what I do in other languages:
- Use MySQLi since it enables both procedural and object programming.
- Efficient code to switch between INSERT and UPDATE.  I know REPLACE INTO produces clean code, but has to be massively inefficient, especially when you consider what that adds to index processing and the tables that are being updated are in InnoDB.
- An efficient, self-documenting, and maintainable mapping of SimpleXML object column values to Forecast table values, with default values for missing information in the XML.

I value your thoughts.  Thanks!

File Layouts:
*Note that both tables use the weather zone as their primary keys.
CREATE TABLE `zones` (
  `wxzone` varchar(6) NOT NULL DEFAULT '',
  `state` char(2) NOT NULL DEFAULT '',
  `country` char(2) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
  `tz` varchar(5) NOT NULL DEFAULT '',
  `tzname` char(3) NOT NULL DEFAULT '',
  `lat` varchar(9) NOT NULL DEFAULT '',
  `lon` varchar(9) NOT NULL DEFAULT '',
  `cwa` char(3) NOT NULL DEFAULT '',
  PRIMARY KEY (`wxzone`),
  KEY `state` (`state`,`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window

CREATE TABLE `forecasts` (
  `Zone` varchar(6) NOT NULL DEFAULT '',
  `TimeStamp` bigint(20) NOT NULL DEFAULT '0',
  `ForecastDate` varchar(30) NOT NULL DEFAULT '',
  `Expires` varchar(20) NOT NULL DEFAULT '',
  `DayTotal` tinyint(4) NOT NULL DEFAULT '0',
  `Day1Title` varchar(20) NOT NULL DEFAULT '',
  `Day1Wx` varchar(20) NOT NULL DEFAULT '',
  `Day1Icon` varchar(20) NOT NULL DEFAULT '',
  `Day1HI` varchar(6) NOT NULL DEFAULT '',
  `Day1LO` varchar(6) NOT NULL DEFAULT '',
  `Day1POP` char(3) NOT NULL DEFAULT '',
  `Day1FC` text NOT NULL,
  `Day2Title` varchar(20) NOT NULL DEFAULT '',
  `Day2Wx` varchar(20) NOT NULL DEFAULT '',
  `Day2Icon` varchar(20) NOT NULL DEFAULT '',
  `Day2HI` varchar(6) NOT NULL DEFAULT '',
  `Day2LO` varchar(6) NOT NULL DEFAULT '',
  `Day2POP` char(3) NOT NULL DEFAULT '',
  `Day2FC` text NOT NULL,
  `Day3Title` varchar(20) NOT NULL DEFAULT '',
  `Day3Wx` varchar(20) NOT NULL DEFAULT '',
  `Day3Icon` varchar(20) NOT NULL DEFAULT '',
  `Day3HI` varchar(6) NOT NULL DEFAULT '',
  `Day3LO` varchar(6) NOT NULL DEFAULT '',
  `Day3POP` char(3) NOT NULL DEFAULT '',
  `Day3FC` text NOT NULL,
  `Day4Title` varchar(20) NOT NULL DEFAULT '',
  `Day4Wx` varchar(20) NOT NULL DEFAULT '',
  `Day4Icon` varchar(20) NOT NULL DEFAULT '',
  `Day4HI` varchar(6) NOT NULL DEFAULT '',
  `Day4LO` varchar(6) NOT NULL DEFAULT '',
  `Day4POP` char(3) NOT NULL DEFAULT '',
  `Day4FC` text NOT NULL,
  `Day5Title` varchar(20) NOT NULL DEFAULT '',
  `Day5Wx` varchar(20) NOT NULL DEFAULT '',
  `Day5Icon` varchar(20) NOT NULL DEFAULT '',
  `Day5HI` varchar(6) NOT NULL DEFAULT '',
  `Day5LO` varchar(6) NOT NULL DEFAULT '',
  `Day5POP` char(3) NOT NULL DEFAULT '',
  `Day5FC` text NOT NULL,
  `Day6Title` varchar(20) NOT NULL DEFAULT '',
  `Day6Wx` varchar(20) NOT NULL DEFAULT '',
  `Day6Icon` varchar(20) NOT NULL DEFAULT '',
  `Day6HI` varchar(6) NOT NULL DEFAULT '',
  `Day6LO` varchar(6) NOT NULL DEFAULT '',
  `Day6POP` char(3) NOT NULL DEFAULT '',
  `Day6FC` text NOT NULL,
  `Day7Title` varchar(20) NOT NULL DEFAULT '',
  `Day7Wx` varchar(20) NOT NULL DEFAULT '',
  `Day7Icon` varchar(20) NOT NULL DEFAULT '',
  `Day7HI` varchar(6) NOT NULL DEFAULT '',
  `Day7LO` varchar(6) NOT NULL DEFAULT '',
  `Day7POP` char(3) NOT NULL DEFAULT '',
  `Day7FC` text NOT NULL,
  `Day8Title` varchar(20) NOT NULL DEFAULT '',
  `Day8Wx` varchar(20) NOT NULL DEFAULT '',
  `Day8Icon` varchar(20) NOT NULL DEFAULT '',
  `Day8HI` varchar(6) NOT NULL DEFAULT '',
  `Day8LO` varchar(6) NOT NULL DEFAULT '',
  `Day8POP` char(3) NOT NULL DEFAULT '',
  `Day8FC` text NOT NULL,
  `Day9Title` varchar(20) NOT NULL DEFAULT '',
  `Day9Wx` varchar(20) NOT NULL DEFAULT '',
  `Day9Icon` varchar(20) NOT NULL DEFAULT '',
  `Day9HI` varchar(6) NOT NULL DEFAULT '',
  `Day9LO` varchar(6) NOT NULL DEFAULT '',
  `Day9POP` char(3) NOT NULL DEFAULT '',
  `Day9FC` text NOT NULL,
  `Day10Title` varchar(20) NOT NULL DEFAULT '',
  `Day10Wx` varchar(20) NOT NULL DEFAULT '',
  `Day10Icon` varchar(20) NOT NULL DEFAULT '',
  `Day10HI` varchar(6) NOT NULL DEFAULT '',
  `Day10LO` varchar(6) NOT NULL DEFAULT '',
  `Day10POP` char(3) NOT NULL DEFAULT '',
  `Day10FC` text NOT NULL,
  `Day11Title` varchar(20) NOT NULL DEFAULT '',
  `Day11Wx` varchar(20) NOT NULL DEFAULT '',
  `Day11Icon` varchar(20) NOT NULL DEFAULT '',
  `Day11HI` varchar(6) NOT NULL DEFAULT '',
  `Day11LO` varchar(6) NOT NULL DEFAULT '',
  `Day11POP` char(3) NOT NULL DEFAULT '',
  `Day11FC` text NOT NULL,
  `Day12Title` varchar(20) NOT NULL DEFAULT '',
  `Day12Wx` varchar(20) NOT NULL DEFAULT '',
  `Day12Icon` varchar(20) NOT NULL DEFAULT '',
  `Day12HI` varchar(6) NOT NULL DEFAULT '',
  `Day12LO` varchar(6) NOT NULL DEFAULT '',
  `Day12POP` char(3) NOT NULL DEFAULT '',
  `Day12FC` text NOT NULL,
  `Day13Title` varchar(20) NOT NULL DEFAULT '',
  `Day13Wx` varchar(20) NOT NULL DEFAULT '',
  `Day13Icon` varchar(20) NOT NULL DEFAULT '',
  `Day13HI` varchar(6) NOT NULL DEFAULT '',
  `Day13LO` varchar(6) NOT NULL DEFAULT '',
  `Day13POP` char(3) NOT NULL DEFAULT '',
  `Day13FC` text NOT NULL,
  `Day14Title` varchar(20) NOT NULL DEFAULT '',
  `Day14Wx` varchar(20) NOT NULL DEFAULT '',
  `Day14Icon` varchar(20) NOT NULL DEFAULT '',
  `Day14HI` varchar(6) NOT NULL DEFAULT '',
  `Day14LO` varchar(6) NOT NULL DEFAULT '',
  `Day14POP` char(3) NOT NULL DEFAULT '',
  `Day14FC` text NOT NULL,
  PRIMARY KEY (`Zone`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window

SimpleXML Object
*Note:  For clarification, there are actually 3 forecasts returned by the XML call.  Forecast, is the first one, and the only one that matters for this discussion.  It uses between 13 and 14 elements in its arrays.  (6 1/2 to 7 days of 12 hour periods)  The other two are a 7-Day, which uses or 7 elements, one for each day, and the last one is Current Observations within the past 15 minutes.
SimpleXMLElement::__set_state(array(
   '@attributes' => 
  array (
    'version' => '1.0',
  ),
   'head' => 
  SimpleXMLElement::__set_state(array(
     'product' => 
    SimpleXMLElement::__set_state(array(
       '@attributes' => 
      array (
        'concise-name' => 'dwmlByDay',
        'operational-mode' => 'developmental',
        'srsName' => 'WGS 1984',
      ),
       'creation-date' => '2016-11-22T08:03:57-05:00',
       'category' => 'current observations and forecast',
    )),
     'source' => 
    SimpleXMLElement::__set_state(array(
       'production-center' => 'Grand Rapids, MI',
       'credit' => 'http://www.weather.gov/grr/',
       'more-information' => 'http://www.nws.noaa.gov/forecasts/xml/',
    )),
  )),
   'data' => 
  array (
    0 => 
    SimpleXMLElement::__set_state(array(
       '@attributes' => 
      array (
        'type' => 'forecast',
      ),
       'location' => 
      SimpleXMLElement::__set_state(array(
         'location-key' => 'point1',
         'point' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'latitude' => '43.02',
            'longitude' => '-85.54',
          ),
        )),
         'area-description' => '3 Miles ESE Northview MI',
         'height' => '659',
      )),
       'moreWeatherInformation' => 'http://forecast.weather.gov/MapClick.php?lat=43.02&lon=-85.54',
       'time-layout' => 
      array (
        0 => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-coordinate' => 'local',
            'summarization' => '12hourly',
          ),
           'layout-key' => 'k-p12h-n13-1',
           'start-valid-time' => 
          array (
            0 => '2016-11-22T08:00:00-05:00',
            1 => '2016-11-22T18:00:00-05:00',
            2 => '2016-11-23T06:00:00-05:00',
            3 => '2016-11-23T18:00:00-05:00',
            4 => '2016-11-24T06:00:00-05:00',
            5 => '2016-11-24T18:00:00-05:00',
            6 => '2016-11-25T06:00:00-05:00',
            7 => '2016-11-25T18:00:00-05:00',
            8 => '2016-11-26T06:00:00-05:00',
            9 => '2016-11-26T18:00:00-05:00',
            10 => '2016-11-27T06:00:00-05:00',
            11 => '2016-11-27T18:00:00-05:00',
            12 => '2016-11-28T06:00:00-05:00',
          ),
        )),
        1 => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-coordinate' => 'local',
            'summarization' => '12hourly',
          ),
           'layout-key' => 'k-p24h-n7-1',
           'start-valid-time' => 
          array (
            0 => '2016-11-22T08:00:00-05:00',
            1 => '2016-11-23T06:00:00-05:00',
            2 => '2016-11-24T06:00:00-05:00',
            3 => '2016-11-25T06:00:00-05:00',
            4 => '2016-11-26T06:00:00-05:00',
            5 => '2016-11-27T06:00:00-05:00',
            6 => '2016-11-28T06:00:00-05:00',
          ),
        )),
        2 => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-coordinate' => 'local',
            'summarization' => '12hourly',
          ),
           'layout-key' => 'k-p24h-n6-2',
           'start-valid-time' => 
          array (
            0 => '2016-11-22T18:00:00-05:00',
            1 => '2016-11-23T18:00:00-05:00',
            2 => '2016-11-24T18:00:00-05:00',
            3 => '2016-11-25T18:00:00-05:00',
            4 => '2016-11-26T18:00:00-05:00',
            5 => '2016-11-27T18:00:00-05:00',
          ),
        )),
      ),
       'parameters' => 
      SimpleXMLElement::__set_state(array(
         '@attributes' => 
        array (
          'applicable-location' => 'point1',
        ),
         'temperature' => 
        array (
          0 => 
          SimpleXMLElement::__set_state(array(
             '@attributes' => 
            array (
              'type' => 'maximum',
              'units' => 'Fahrenheit',
              'time-layout' => 'k-p24h-n7-1',
            ),
             'name' => 'Daily Maximum Temperature',
             'value' => 
            array (
              0 => '39',
              1 => '37',
              2 => '42',
              3 => '42',
              4 => '44',
              5 => '45',
              6 => '46',
            ),
          )),
          1 => 
          SimpleXMLElement::__set_state(array(
             '@attributes' => 
            array (
              'type' => 'minimum',
              'units' => 'Fahrenheit',
              'time-layout' => 'k-p24h-n6-2',
            ),
             'name' => 'Daily Minimum Temperature',
             'value' => 
            array (
              0 => '27',
              1 => '33',
              2 => '35',
              3 => '32',
              4 => '31',
              5 => '36',
            ),
          )),
        ),
         'probability-of-precipitation' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'type' => '12 hour',
            'units' => 'percent',
            'time-layout' => 'k-p12h-n13-1',
          ),
           'name' => '12 Hourly Probability of Precipitation',
           'value' => 
          array (
            0 => 
            SimpleXMLElement::__set_state(array(
            )),
            1 => '40',
            2 => '80',
            3 => '60',
            4 => 
            SimpleXMLElement::__set_state(array(
            )),
            5 => '30',
            6 => '40',
            7 => 
            SimpleXMLElement::__set_state(array(
            )),
            8 => 
            SimpleXMLElement::__set_state(array(
            )),
            9 => 
            SimpleXMLElement::__set_state(array(
            )),
            10 => 
            SimpleXMLElement::__set_state(array(
            )),
            11 => 
            SimpleXMLElement::__set_state(array(
            )),
            12 => 
            SimpleXMLElement::__set_state(array(
            )),
          ),
        )),
         'weather' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-layout' => 'k-p12h-n13-1',
          ),
           'name' => 'Weather Type, Coverage, Intensity',
           'weather-conditions' => 
          array (
            0 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Partly Sunny',
              ),
            )),
            1 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Mostly Cloudy then Chance Wintry Mix',
              ),
            )),
            2 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Wintry Mix',
              ),
            )),
            3 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Rain Likely',
              ),
            )),
            4 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Cloudy',
              ),
            )),
            5 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Chance Rain/Snow',
              ),
            )),
            6 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Chance Rain/Snow',
              ),
            )),
            7 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Mostly Cloudy',
              ),
            )),
            8 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Partly Sunny',
              ),
            )),
            9 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Partly Cloudy',
              ),
            )),
            10 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Partly Sunny then Chance Rain',
              ),
            )),
            11 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Rain Likely',
              ),
            )),
            12 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'Rain',
              ),
            )),
          ),
        )),
         'conditions-icon' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'type' => 'forecast-NWS',
            'time-layout' => 'k-p12h-n13-1',
          ),
           'name' => 'Conditions Icon',
           'icon-link' => 
          array (
            0 => 'http://forecast.weather.gov/newimages/medium/bkn.png',
            1 => 'http://forecast.weather.gov/DualImage.php?i=nbkn&j=nfzra_sn&jp=40',
            2 => 'http://forecast.weather.gov/newimages/medium/fzra_sn80.png',
            3 => 'http://forecast.weather.gov/newimages/medium/nra60.png',
            4 => 'http://forecast.weather.gov/newimages/medium/ovc.png',
            5 => 'http://forecast.weather.gov/newimages/medium/nra_sn30.png',
            6 => 'http://forecast.weather.gov/newimages/medium/ra_sn40.png',
            7 => 'http://forecast.weather.gov/newimages/medium/nbkn.png',
            8 => 'http://forecast.weather.gov/newimages/medium/bkn.png',
            9 => 'http://forecast.weather.gov/newimages/medium/nsct.png',
            10 => 'http://forecast.weather.gov/DualImage.php?i=bkn&j=ra&jp=0',
            11 => 'http://forecast.weather.gov/newimages/medium/nra.png',
            12 => 'http://forecast.weather.gov/newimages/medium/ra.png',
          ),
        )),
         'hazards' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-layout' => '',
          ),
           'name' => 'Watches, Warnings, and Advisories',
           'hazard-conditions' => 
          SimpleXMLElement::__set_state(array(
             'hazard' => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'headline' => 'Hazardous Weather Outlook',
              ),
               'hazardTextURL' => 'http://forecast.weather.gov/showsigwx.php?warnzone=MIZ057&warncounty=MIC081&firewxzone=MIZ057&local_place1=3+Miles+ESE+Northview+MI&product1=Hazardous+Weather+Outlook',
            )),
          )),
        )),
         'wordedForecast' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-layout' => 'k-p12h-n13-1',
            'dataSource' => 'grrNetcdf',
            'wordGenerator' => 'markMitchell',
          ),
           'name' => 'Text Forecast',
           'text' => 
          array (
            0 => 'Partly sunny, with a high near 39. Light and variable wind. ',
            1 => 'A chance of snow and freezing rain after 1am.  Mostly cloudy, with a low around 27. East southeast wind 6 to 9 mph.  Chance of precipitation is 40%.',
            2 => 'Rain, snow, and freezing rain likely before 1pm, then rain between 1pm and 4pm, then rain, possibly mixed with snow after 4pm.  High near 37. East southeast wind 8 to 10 mph.  Chance of precipitation is 80%. Little or no ice accumulation expected.  New snow accumulation of less than a half inch possible. ',
            3 => 'Rain likely, mainly before 1am.  Cloudy, with a low around 33. East southeast wind 5 to 9 mph becoming light  after midnight.  Chance of precipitation is 60%. New precipitation amounts between a tenth and quarter of an inch possible. ',
            4 => 'Cloudy, with a high near 42. Calm wind becoming west southwest around 5 mph in the afternoon. ',
            5 => 'A chance of rain and snow, mainly after 1am.  Mostly cloudy, with a low around 35. Chance of precipitation is 30%.',
            6 => 'A chance of rain and snow before 1pm, then a slight chance of rain.  Cloudy, with a high near 42. Chance of precipitation is 40%.',
            7 => 'Mostly cloudy, with a low around 32.',
            8 => 'Partly sunny, with a high near 44.',
            9 => 'Partly cloudy, with a low around 31.',
            10 => 'A chance of rain.  Partly sunny, with a high near 45.',
            11 => 'Rain likely.  Mostly cloudy, with a low around 36.',
            12 => 'Rain.  Cloudy, with a high near 46.',
          ),
        )),
      )),
    )),
    1 => 
    SimpleXMLElement::__set_state(array(
       '@attributes' => 
      array (
        'type' => 'current observations',
      ),
       'location' => 
      SimpleXMLElement::__set_state(array(
         'location-key' => 'point1',
         'point' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'latitude' => '42.88',
            'longitude' => '-85.52',
          ),
        )),
         'area-description' => 'Grand Rapids, Gerald R. Ford International Airport, MI',
         'height' => '794',
      )),
       'moreWeatherInformation' => 'http://www.nws.noaa.gov/data/obhistory/KGRR.html',
       'time-layout' => 
      SimpleXMLElement::__set_state(array(
         '@attributes' => 
        array (
          'time-coordinate' => 'local',
        ),
         'layout-key' => 'k-p1h-n1-1',
         'start-valid-time' => '2016-11-22T07:53:00-05:00',
      )),
       'parameters' => 
      SimpleXMLElement::__set_state(array(
         '@attributes' => 
        array (
          'applicable-location' => 'point1',
        ),
         'temperature' => 
        array (
          0 => 
          SimpleXMLElement::__set_state(array(
             '@attributes' => 
            array (
              'type' => 'apparent',
              'units' => 'Fahrenheit',
              'time-layout' => 'k-p1h-n1-1',
            ),
             'value' => '24',
          )),
          1 => 
          SimpleXMLElement::__set_state(array(
             '@attributes' => 
            array (
              'type' => 'dew point',
              'units' => 'Fahrenheit',
              'time-layout' => 'k-p1h-n1-1',
            ),
             'value' => '19',
          )),
        ),
         'humidity' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'type' => 'relative',
            'time-layout' => 'k-p1h-n1-1',
          ),
           'value' => '81',
        )),
         'weather' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'time-layout' => 'k-p1h-n1-1',
          ),
           'name' => 'Weather Type, Coverage, Intensity',
           'weather-conditions' => 
          array (
            0 => 
            SimpleXMLElement::__set_state(array(
               '@attributes' => 
              array (
                'weather-summary' => 'A Few Clouds',
              ),
            )),
            1 => 
            SimpleXMLElement::__set_state(array(
               'value' => 
              SimpleXMLElement::__set_state(array(
                 'visibility' => '10.00',
              )),
            )),
          ),
        )),
         'conditions-icon' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'type' => 'forecast-NWS',
            'time-layout' => 'k-p1h-n1-1',
          ),
           'name' => 'Conditions Icon',
           'icon-link' => 'http://forecast.weather.gov/newimages/medium/few.png',
        )),
         'direction' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'type' => 'wind',
            'units' => 'degrees true',
            'time-layout' => 'k-p1h-n1-1',
          ),
           'value' => '0',
        )),
         'wind-speed' => 
        array (
          0 => 
          SimpleXMLElement::__set_state(array(
             '@attributes' => 
            array (
              'type' => 'gust',
              'units' => 'knots',
              'time-layout' => 'k-p1h-n1-1',
            ),
             'value' => 'NA',
          )),
          1 => 
          SimpleXMLElement::__set_state(array(
             '@attributes' => 
            array (
              'type' => 'sustained',
              'units' => 'knots',
              'time-layout' => 'k-p1h-n1-1',
            ),
             'value' => '0',
          )),
        ),
         'pressure' => 
        SimpleXMLElement::__set_state(array(
           '@attributes' => 
          array (
            'type' => 'barometer',
            'units' => 'inches of mercury',
            'time-layout' => 'k-p1h-n1-1',
          ),
           'value' => '30.34',
        )),
      )),
    )),
  ),
))

Open in new window

0
Comment
Question by:IT_Architect
  • 8
  • 5
13 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41897519
Please post the XML document (or better yet, the URL of the XML document).  This is one of those anomalous things where var_export() is not as helpful as it could be!

MySQLi is a good choice because it enables OOP design and programming.  There is no advantage to procedural code any more.

Efficient code probably does not matter at this point.  You're not using enough data for efficiency to become an issue.  That's not to say we would deliberately do something wrong in the design, just that optimization should be put off until we have a working data model.

My guess is that "zones" is pretty much a fixed table, right?  It's a table that once loaded will not need to be reloaded.

And now we come to the meat of the matter... Databases are relational.  XML is hierarchical.  This is not quite as different as fish and bicycles, but it can be very, very different.  Your best design for something like this is probably some kind of lookup engine.  You will create an object from the XML document, as you've done in the question, and you'll pass two arguments to the lookup.  One argument will be the big fat XML object; the other will be the name of the column in the forecasts table.  The lookup engine will have a method for each and every column in the forecasts table (a one-to-one correspondence between column names and method names should be obviously valuable).  Then you can iterate over the list of columns, calling the lookup engine with each column name.  The lookup engine will return the default value or the value from the XML, whichever is applicable.

This will result in very verbose programming, but it will give you something that can be tested, and with complex data structures, verbose programming and fewer if() statements is a virtue.

If I get a chance, I'll try to show you some code that implements the design, once I can get the XML.
0
 

Author Comment

by:IT_Architect
ID: 41897601
My guess is that "zones" is pretty much a fixed table, right?
True
Efficient code probably does not matter at this point.
That part I wasn't clear enough about.  How fast the code executes is never the problem in a database and dynamic PHP application like this.  How efficiently it works with MySQL is.  There is a reason I made the active tables InnoDB.  The site serves up to 66 dynamic PHP pages a second on busy days.  Information comes from a lot of places.  While it is doing that, certain times every hour all or nearly all rows in the files are updated while all of that is going on.  InnoDB even beat memory tables, probably because of its cache management.  Server loads can go high, which is what forced our switch to FreeBSD.  As things slow down, you start stacking up http, where tuning becomes a fine art.  Now throw in some bad weather, day-long DDOS attacks where they fish for your http parameter settings based on timing, and automatic process you have in place to adjust on the fly, attempt to isolate the imposters by IP, it's at the DB level where things need to get out of the way quick.
You will create an object from the XML document, as you've done in the question, and you'll pass two arguments to the lookup.  One argument will be the big fat XML object; the other will be the name of the column in the forecasts table.  The lookup engine will have a method for each and every column in the forecasts table
Maybe, or maybe the XML object pointer?, along with a pointer to the table buffer?, with access the column property names, with a PHP two-dimensional array that contains the DB column name, default value, and object path.  (I'm out of my element here with PHP)  With either a very similar engine or the same engine, I may be able to load the 7-Day as well.  If I make it a class, I would be able to run both instances at the same time.  What do you think?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 41897713
Please post the XML document (or better yet, the URL of the XML document).

And as I think about default values, you might want to put those into the database table definitions, rather than have to deal with them in code.  That way if any of the data from the API is missing, you will still have sensible values in the database.
0
 

Author Comment

by:IT_Architect
ID: 41897728
The XML document is included in the document at the bottom under the table layouts because it changes every 15 minutes.  Here is the link:
XML Link
0
 

Author Comment

by:IT_Architect
ID: 41897734
you might want to put those into the database table definitions, rather than have to deal with them in code.
Excellent thought!  At the moment, I can't think of a single disadvantage.  If anything, it is more self-documenting.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41897755
Thanks for the link.  The XML document is not the same as the output of var_export(), which for a quirky reason does not play well with SimpleXML objects.  PHP at 5.3 lacks a way to conveniently attach methods to an object, so it's better if I work with the XML, instead of the var_export() output in this case.

Is there any way to request UTF-8 output instead of ISO-8859-1?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:IT_Architect
ID: 41897798
Is there any way to request UTF-8 output instead of ISO-8859-1?
Not that I'm aware of, but the last update of the XML docs from Weather.gov is 2003, and the XML they send doesn't match the the spec in many areas.  I learned by accidentally running across an internet example that I could request by zipcode, which also isn't documented.
0
 

Author Comment

by:IT_Architect
ID: 41897812
Also, the Day1, Day2 buckets for Forecasts is really only 12 hour periods.  E.G. Day1 + Day2 = one day.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41897850
OK, we'll try it with ISO-8859-1 then.  As long as the data is ASCII, it will fit into the bottom 127 characters of UTF-8, so things might be OK.  Advantages of UTF-8 include (1) it's required for JSON, and (2) almost all character encodings are moving toward UTF-8 these days.

Are you open to the idea of changing the table / column structures?  For example, if you've got a TIMESTAMP value, it makes sense to use a TIMESTAMP data type.  Same with DATE or DATETIME values.
0
 

Author Comment

by:IT_Architect
ID: 41897870
Are you open to the idea of changing the table / column structures?  For example, if you've got a TIMESTAMP value, it makes sense to use a TIMESTAMP data type.  Same with DATE or DATETIME
No.  I didn't write it.  There is a lot that I don't know about the huge code base.  The code is designed to work as regular ASCII flat file database with MySQL as an option.  If I was writing it from scratch, there is much I would do differently.

As it is, I will have to set up a bunch of SQL statements to axe old data.  I found up-to-date Zone, FIPS, and ICAO lists that I will later need to use to square it up the tables individually since there are no FK constraints.
0
 

Author Comment

by:IT_Architect
ID: 41897899
This method seems to work fine for matching things up:
$FcstMatch = array(
"Day1Title"=>(string)$ForecastTimes[0],
"Day1Icon"=>(string)$ForecastIcons[0],
"Day2Title"=>(string)$ForecastTimes[1],
"Day2Icon"=>(string)$ForecastIcons[1],
"Day3Title"=>(string)$ForecastTimes[2],
"Day3Icon"=>(string)$ForecastIcons[2]);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41897924
OK, if you can't change things, I understand.  I've given my suggestions, so I'll sign off now.  Best of luck with your project, ~Ray
0
 

Author Closing Comment

by:IT_Architect
ID: 41904911
Thanks tons for your help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now