Updating sites—HTML Notes

It’s been a while since I have looked at the code for my websites so I popped over to W3Schools to review the HTML5 standards and tested my sites in Validator. A couple of things are different and/or I never knew.

You can use either single or double quotes in tag attributes. I prefer single quotes since I often write code in HTML and then generalize it with PHP and PHP uses double quotes in echo statements.

They recommend using <html lang=”en-US”> in the header instead of just <html lang=”en”>.

The type=”text/javascript” code in no longer used in links to javascript or for inline javascript.

By default strong and b display the same on the screen but they are semantically different. <b> is a style – we know what “bold” is supposed to look like.

<strong> however is an indication of how something should be understood. “Strong” could (and often does) mean “bold” in a browser, but it could also mean a lower tone for a speaking program like Jaws (for blind people).

The abbreviation tag <abbr title=”Abbreciation name”> is useful for screen readers and search engines but it does ODD things in WORDPRESS.

Commenting HTML can be useful for debugging and is done with <!– Write your comments here –> which can span multiple lines. Keep in mind that while the browser doesn’t display comments, they are viewable in the page source. The page source displays the comments as a single line.

It turns out that there are 140 color names in HTML5.

The draggable attribute in combination with some simple javascript can be useful if you are working on interactive games. Links and images are draggable by default which explains why you can drag them to the desktop.

If you are writing a page that test spelling, you probably want to turn off spellcheck.
<p contenteditable="true" spellcheck="false">This is a paragraph that is not spell checked.</p>

I wish more websites used tabindex when designing forms. It’s especially frustrating when you tab and go to a button instead of the next input field or when you jump around on the form.

Perl Date and Concatenation

I’m working with a legacy script that needs the date in YYYY,MM,DD format and the original programmer went through some contortions to get MySQL to calculate the date and report it out. It seemed to me that perl should be able to do that easily. In fact, it can.


my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
my $perl_month = $mon + 1;
my $perl_year = $year + 1900;
my $perl_today = "$perl_year,$perl_month,$mday";
my $perl_today2 = ($year + 1900) . "," . ($mon + 1) . "," . $mday;

if ($DEBUG) { print STDOUT "\n Perl thinks today is: $perl_today, $perl_today2  \n"; }

Perl thinks today is: 2019,12,13, 2019,12,13

Perl can do the arithmetic, but you need to put the calculations in parentheses. If you don’t it gets confused and outputs 2020,13. Note that localtime() gives the year as an offset from 1900 and the month starting with January as 0—much like array notation.

Fixing a MySQL table

I accidentally created a bunch of tables without setting the `id` field as autoincrement, having a primary key, and making the `datetime` field unique. Doing some of these things as separate steps doesn’t work because they are dependent on each other. So I combined them all into one command.

ALTER TABLE `values_Temperature` ADD PRIMARY KEY (`id`), MODIFY `id` INTEGER NOT NULL AUTO_INCREMENT, ADD UNIQUE KEY `datetime` (`datetime`);

Import CSV data into MySQL with PHPMyadmin

I have been getting data for temperature and humidity from the California Irrigation and Management System (CIMIS), importing it into LibreOffice, manipulating the date field, and copying it into BBEdit to convert to sql files for import. That works fine for a years worth of data, but I wanted to get the last five years and not have to do any manual manipulation of the data.

The data from CIMIS comes in as a CSV file with the qc fields mostly blank. This is a problem when the last field is empty since the import function thinks that there is no data there. So I ignore the last field when creating the table. Also the field names have spaces and it is generally not common to use spaces in MySQL field names, so I replaced them with underscores. The qc fields all have the same name so I numbered them.

I want my data to have an ID column but I don’t want to have to add it to the CSV file. The import works fine if it is at the end of the table. You can always move it to the beginning with PHPMyAdmin later.


Stn Id,Stn Name,CIMIS Region,Date,Hour (PST),Jul,ETo (in),qc,Precip (in),qc,Sol Rad (Ly/day),qc,Vap Pres (mBars),qc,Air Temp (F),qc,Rel Hum (%),qc,Dew Point (F),qc,Wind Speed (mph),qc,Wind Dir (0-360),qc,Soil Temp (F),qc
125,Arvin-Edison,San Joaquin Valley,9/30/2014,0100,273,0.00, ,0.00, ,0, ,13.3, ,59.6, ,76, ,52.1, ,3.2, ,156, ,72.1,



CREATE TABLE `CIMIS_All` (
  `Stn_Id` int(4),
  `Stn_Name` varchar(50),
  `CIMIS_Region` varchar(50),
  `Date` varchar(10),
  `Hour_(PST)` varchar(4),
  `Jul` float,
  `ETo_(in)` float,
  `qc1` int(4),
  `Precip_(in)` float,
  `qc2` int(4),
  `Sol_Rad_(Ly/day)` float,
  `qc3` int(4),
  `Vap_Pres_(mBars)` float,
  `qc4` int(4),
  `Air_Temp_(F)` float,
  `qc5` int(4),
  `Rel_Hum_(%)` float,
  `qc6` int(4),
  `Dew_Point_(F)` float,
  `qc7` int(4),
  `Wind_Speed_(mph)` float,
  `qc8` int(4),
  `Wind_Dir_(0-360)` float,
  `qc9` int(4),
  `Soil_Temp_(F)` float,
  `id` int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When importing, I needed to compress the data since it exceeds the PHPMyAdmin 2MB limit. I also didn’t want to fool with the file at all so I when importing I skipped the first line using this selection in PHPMyAdmin.

Skip this number of queries (for SQL) starting from the first one: 1

I also used the default CSV import settings. After import I got this error because the last line in the CSV file is empty.
Invalid column count in CSV input on line 45434.

Otherwise the import went fine.

Now I need to get the time and date info into a datetime field. CIMIS is a bit weird in that they treat midnight as 2400 of the current day whereas MySQL treats it as 00:00:00 of the next day. So I just converted all of the 2400 to 2359. Then I add a minute to convert them to MySQL format. Since I asked for data through today, I got all of today’s times—even though there is no data for it. CIMIS puts an ‘M’ in the field which is imported as ‘0’. If air temp, soil temp, and relative humidity are all 0, that means there is no data for that time period, so I deleted those rows.


ALTER TABLE `CIMIS_ALL` ADD `datetime` DATETIME NOT NULL AFTER `id`;

UPDATE `CIMIS_All` SET `Hour_(PST)` = '2359' WHERE `Hour_(PST)` = '2400';
UPDATE `CIMIS_All` SET `datetime` = STR_TO_DATE(CONCAT(`Date`,`Hour_(PST)`), '%m/%d/%Y%H%i');
UPDATE `CIMIS_All` SET `datetime` = ADDTIME(`datetime`, '0:01:0') WHERE MINUTE(`datetime`) = '59';
DELETE FROM `CIMIS_All` WHERE `Air_Temp_(F)` = 0 && `Soil_Temp_(F)` = 0 && `Rel_Hum_(%)` = 0

Now that I have the data, I just export the fields I need into the tables that are already set up for the locations. I didn’t create a datetime field originally, so I’ll have to create it in the new table. Here’s an example where I import the temperature and humidity. You can import as many fields as you use.


INSERT INTO `CIMIS_Location` (`datetime`,`Air_Temp_(F)`,`Rel_Hum_(%)`) SELECT `datetime`,`Air_Temp_(F)`,`Rel_Hum_(%)` FROM `CIMIS_All`;

You can also automate this process by retrieving an XML file from the CIMIS server. You can retrieve it with this command.


wget -q -O CIMIS_data.xml  "https://et.water.ca.gov/api/data?appKey=YourKey&targets=70,71&startDate=2019-01-01&endDate=2019-01-05"

You can list as many targets as you want. The API key can be gotten from the CIMIS website. Once you have the data, you can parse it using XML parsing tools.