IT_Architect
asked on
PHP MySQL MySQLi Update Insert, and mapping XML data to MySQL Columns
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.
*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.
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;
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;
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',
)),
)),
)),
),
))
ASKER
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 tableMaybe, 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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
XML Link
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Also, the Day1, Day2 buckets for Forecasts is really only 12 hour periods. E.G. Day1 + Day2 = one day.
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.
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.
ASKER
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 DATETIMENo. 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.
ASKER
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]);
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
ASKER
Thanks tons for your help!
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.