Json_encode and PHP 5.2.17

Leave a reply

A server I push PHP scripts to is still running PHP 5.2.17 – its insanely old, almost 5 years!

I discovered that if I try to use that version of json_encode and pass it a string with unicode characters, I get nulls back where I should have UTF-8 encoded strings!  The code works fine on my local server, running PHP 7.0.15.

The fix for this is pretty simple, but worth noting for future reference:

 $text = utf8_encode($row[$key]);
 array_push($items,$text);

Where $row is an assoc. array and $key points to the value I want to push into my array which in turn is encoded with:

$json = json_encode($items);

Without the ‘utf8_encode’ I just get nulls in my encoded string 🙁

Javascript, UTC datetime and formatting oh my…

Leave a reply

I’ve been working on a project to display data stored in a MySQL database using the Google Graph API.  I’ve done this before, but this time I need to display the time the MySQL entry was created.  The DB row contains this information as a MySQL timestamp field, and is returned as a string when I fetch it, for example:

2017-07-09 02:30:00

This is in UTC, and as I’ve discovered, the PHP script has no idea of what timezone the browser is in, so either I need to pass that from JavaScript, or hand the UTC times back to the browser and let it take care of the conversion.  I decided it would be best to let the browser handle it (for now), and immediately discovered the confusion around formatting a Date string with JavaScript.

To me, this seems pretty basic, and I really don’t understand why it was omitted from the language.  There are a lot of libraries out there that will do this for you, but I ended up using jQuery, as I was already using it for the AJAX and graphing support.

jQuery provides the .datepicker class, which includes a date formatter.  I just needed the month and day, the month needs to be a short string, while the day is a number without any leading zeros.  I used the following:

$.datepicker.formatDate("M-d",utcTime)

Where utcTime is a Date object that’s been created from the MySQL string with:

var dt = new Date(Date.parse(item[0] + "Z"));

I append the Z to the MySQL string, so JavaScript knows its a UTC time.

At the time I did this, there is no option for handling Time within the standard jQuery package, so I added that manually, padding with a leading ‘0’ as needed, with the final date/time string being created with:

return $.datepicker.formatDate("M-d",utcTime) + " "
        + ("0" + utcTime.getHours()).slice(-2) + ":"
        + ("0" + utcTime.getMinutes()).slice(-2);

Note: I could have had .datepicker parse the string, but it wouldn’t have given me the time portion, so I do that in a separate step.

MySQL and ON DUPLICATE KEY UPDATE

Leave a reply

I’ve been working on some MySQL stuff recently, specifically storing a snapshot of the price of bitcoin every 5 minutes from an online exchange.  As you can imagine, the amount of data this can generate is quite large, and becomes more than I need.  I mean, I don’t care what the price for Bitcoin was exactly 3 weeks, 2 days, 11 hours and 20 minutes ago was.  I do care about averages and trends though.

So, I decided to aggregate my data to hourly averages, along with a min/max for each hour.  Then I can delete any of the 5 minute interval data older than a week or so, and use the aggregated data to do my analysis and graphs.

In order to do this I wanted to run a query that would average all the entries within the current hour, and then update a row with these updated averages, along with min/max values.  I also wanted the query to insert a new row if it was a new hour.  After a little research I knew I needed to figure out INSERT INTO… ON DUPLICATE KEY UPDATE as my pattern.

Everything seemed to be going great, until I needed to store calculated fields in an updated row.  I couldn’t get the SQL statement to actually update the row.

It turns out, when you use a SELECT to generate the data to be INSERTed, MySQL creates a temporary table with the data in it, and I couldn’t find the name of the table, so trying to update the values with entries from the SELECT would fail with ‘No such column’ error.

I fixed this by storing the calculated results in specific variables, and in turn using those variables to update the existing row.

INSERT INTO tickerHourly
 (ID, time, product_id, priceAvg, priceMax, priceMin, volumeAvg, counter)
 SELECT
 (select ID from tickerHourly WHERE product_id = ticker.product_id 
 AND hour(time) = hour(UTC_TIMESTAMP()) and date(time) = date(UTC_TIMESTAMP())),
 FROM_UNIXTIME(60*60*ROUND(UNIX_TIMESTAMP(time)/(60*60))),
 product_id,
 @avg := AVG(price),
 @max := MAX(price),
 @min := MIN(price),
 @volumeAvg := AVG(volume),
 @counter := count(*)
 FROM ticker where hour(time) = hour(UTC_TIMESTAMP())
 AND date(time) = date(UTC_TIMESTAMP())
 AND product_id = ?
 group by date(time),hour(time),product_id
 ON DUPLICATE KEY UPDATE
 tickerHourly.counter = @counter,
 tickerHourly.priceAvg = @avg,
 tickerHourly.priceMax = @max,
 tickerHourly.priceMin = @min,
 tickerHourly.volumeAvg = @volumeAvg;

So the line ‘@avg := AVG(price)’ takes the average of all the price entries and stores it in the local variable avg, which I then use as part of the UPDATE clause.  This was my missing information.

Unity 5.6 and Android overlays – Updated

Leave a reply

Unity just released 5.6.2f1 which rolls back the change they made to their Android player.  While the method described below works, it’s no longer needed.  There was also some performance issues using the PopupWindow, so it’s better just to go back to using a view added to the Unity view.

I updated the Unity version I’ve been using to 5.6 when it came out of beta, but when I built my android app, I noticed a major problem – the ad banners no longer appeared!  So glad I made sure to test my app before updating in to GPlay 😀

Turns out 5.6 uses a feature in Android to make their view sit on top of all other views for that activity, and when my plugin would create a view to hold both my AdMob view and Amazon adView, it wouldn’t display them on top of the Unity view as before.

I tried a few different methods before seeing that the folks who maintain the AdMob plugin for Android on Unity had  updated their method to use a PopupWindow.  I did some research on this method, and got it to work for both AdMob and Amazon ads.

The one thing that had me stuck for a bit was that you cannot show a PopupWindow right after creating it in the application onCreate method, you need to wait for a bit.  This was also true when I created the PopupWindow during my plugin initialization.

I used the post() method on the rootView to get the delay I needed.

    if (mPopupWindow!=null)
    {
        activity.getWindow().getDecorView().getRootView().post(new Runnable() {
        public void run() {
            if (isDebug)
                Log.i(LOGTAG,"Showing POPUP window...");
            mPopupWindow.showAtLocation(activity.getWindow().getDecorView().getRootView(),
                            Gravity.TOP, 0, 0);
        }
    });
    }

At some point, I’ll update my plugin to allow the PopupWindow to be either at the top or bottom of the display, right now it’s hard-coded to appear at the top.

iOS and App Transport Security Settings

Leave a reply

Some time ago, Apple added a security feature to iOS that requires all domains that an app needs to connect to without HTTPS be white-listed in the info.plist file.

It’s pretty simple, you create a Dictionary object with the name of NSAppTransportSecurity and then add a key with the name NSAllowsArbitraryLoads and the bool value of True.  This will basically bypass the security system and allow your app to read from any website.

The problem coming down the pipe, is they are going to turn this ‘temp’ feature off sometime this year.  When they do, an App using this scheme will not be able to read from a non-HTTPS URL.

To get around this you need to explicitly add domains to your white-list.  Not too hard, but it needs to be done.

In the NSAppTransportSecurity, create another dictionary with the name NSExceptionDomains.  Then create another dictionary for each domain you’ll need access to, with the root domain as the name.  Inside that dictionary, add two items – NSTemporaryExceptionAllowsInsecureHTTPLoads with a bool value of TRUE, and NSIncludesSubdomains also with a bool value of TRUE.

That will let you read from those domains in the future, even when Apple kill NSAllowsArbitraryLoads.

The following is an extract from one of our apps which allows the app to read from purplebuttons.com.

<dict>
<key>purplebuttons.com</key>
<dict>
<key>NSTemporaryExceptionAllowsInsecureHTTPLoads</key>
<true/>
<key>NSIncludesSubdomains</key>
<true/>
</dict>
</dict>