Code Samples

Thursday, October 19, 2023

How to fix the MySQL error: Illegal mix of collations for operation union

Well, you probably have different collations in some MySQL views or stored procedures, try to force collation like this:

SET character_set_client = 'utf8mb4'; 
SET character_set_connection = 'utf8mb4'; 
SET collation_connection = 'utf8mb4_unicode_ci'; 
drop view if exists myviewwithproblem; 
create view myviewwithproblem as … da da etc etc

Another solution might be:

I ran into this recently as well. In my case the relevant columns were utf8mb4_unicode_ci, but I found out that the session was utf8mb4_general_ci. You can see this from the collation_connection variable:
CODE: SELECT ALL

SHOW VARIABLES LIKE '%collat%';

I found that this came from the connection string including "Character Set=utf8mb4". This caused a "SET NAMES UTF8MB4;" query to be run on every connection, which causes MySQL to take the default collation for this character set, which is utf8mb4_general_ci.

Updating the server's character set and collation to match the data (setting character-set-server and collation-server in the server config) and then using "Character Set=Auto" caused the connection to have the correct collation, and the issue was fixed. Manually running something like "SET NAMES UTF8MB4 COLLATE utf8mb4_unicode_ci" should fix it, too.

Sunday, October 8, 2023

PHP image output and browser caching

This post is for return image from server by PHP script and cache in browser to save on our bandwidth.




Below code will return image using PHP script

<?php

function image_function_cache($file_name) {
    $file = "WWW_ROOT/public/img/$file_name";
    if (!file_exists($file) || !is_file($file)) {
        echo "";
        exit;
    }

    $fileTime = filemtime($file);
    $headerTime = IfModifiedSince();

    // Will return 304 when image source not changed
    if (!is_null($headerTime) && (strtotime($headerTime) == $fileTime)) {
        header('Last-Modified: '.gmdate('D, d M Y H:i:s', $fileTime).' GMT', true, 304);
        exit;
    }
    session_cache_limiter('none');

    $type = 'image/png';
    header('Content-Type:'.$type);
    header('Content-Length: ' . filesize($file));
    header('Cache-control: max-age='.(60*60*24*365));
    header('Expires: '.gmdate(DATE_RFC1123,time()+60*60*24*365));
    header('Last-Modified: '.gmdate('D, d M Y H:i:s', $fileTime).' GMT', true, 200);
    readfile($file);
    exit;
}

function IfModifiedSince() {
    if (function_exists("apache_request_headers")) {
        if ($headers = apache_request_headers()) {
            if (isset($headers['If-Modified-Since'])) {
                return $headers['If-Modified-Since'];
            }
        }
    }
    if (isset($_SERVER['HTTP_IF_MODIFIED_SINCE'])) {
        return $_SERVER['HTTP_IF_MODIFIED_SINCE'];
    }
    return null;
}



Check below image, there are two request for an image, first request status is 200 and image returned, but 2nd call if you see Size column image returned from cache. All happened for above functionality:

How to Send files via POST with cURL and PHP (Example)

In this process cURL send file to any web server along with other params. Check it out below code:




  
Assuming that you're using PHP 5.5+, you need to use CURLFile for uploading your file:


<?php

$headers = array(
    'Authorization: Bearer Token Value',
    'Content-type: multipart/form-data'
);

$url = "https://example.com/api/v1/import/uploadfile";

$post_data = array(
    "file1" => new CURLFile("/var/www/files/file1.csv", 'text/csv', 'File1.csv'),
    "file2" => new CURLFile("/var/www/files/file1.pdf", 'application.pdf', 'File1.pdf'),
    "param1" => "Test"
);

$curl = curl_init();
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_VERBOSE, true);
curl_setopt($curl, CURLOPT_HEADER, false);
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_POSTFIELDS, $post_data);
curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
$response = curl_exec($curl);
$status = curl_getinfo($curl, CURLINFO_HTTP_CODE);
curl_close($curl); 

Tuesday, September 19, 2023

How to Save a Mapbox GL Web Map Extent as an Image | Mapbox GL JS: Export map to PNG

  Our target is to export Mapbox as exported PNG format. Mapbox loaded into canvas, so our job get simplified.


  Give that map some time to load and fetch the image data when the load event is triggered. Usually there is a 'idle' event for map when we can determine that map event loaded fully.


<div id="map-panel">
  <div id="map"></div>
</div>
<div id="map-static">
  <img src="" alt="??" id="imgTag"/>
</div>
body { 
  margin: 0; 
  padding: 0;
}
#map-panel { 
  position: absolute; 
  top: 0; 
  bottom: 50%; 
  width: 100%;
  padding:10px;
}
#map-panel #map {
  width: calc(100% - 20px);
  height: calc(100% - 20px); 
}
#map-static { 
  position: absolute; 
  top: 50%;
  bottom: 0; 
  width: 100%; 
  padding:10px;
}
#imgTag {
  width: calc(100% - 20px);
  height: calc(100% - 20px);
}
mapboxgl.accessToken = 'pk.eyJ1IjoidGFrdX...fQ.57D0sXpw';
const map = new mapboxgl.Map({
    container: 'map',
    style: 'mapbox://styles/mapbox/standard-beta',
    center: [-96, 37.8],
    zoom: 7,
    preserveDrawingBuffer: true,
});
map.on('load', () => {
    console.log('Map.load');
    map.loadImage("https://docs.mapbox.com/mapbox-gl-js/assets/cat.png", (error, image) => {
        if (error) {
            alert("Could not load image");
            return;
        }
        map.addImage('cat_image', image);
        // Add a data source containing one point feature.
        map.addSource('point', {
            'type': 'geojson',
            'data': {
                'type': 'FeatureCollection',
                'features': [{
                    'type': 'Feature',
                    'properties': {'name': 'Cat 1'},
                    'geometry': {
                        'type': 'Point',
                        'coordinates': [-96, 37.8]
                    }
                }, {
                    'type': 'Feature',
                    'properties': {'name': 'Cat 2'},
                    'geometry': {
                        'type': 'Point',
                        'coordinates': [-96.40303, 37.8]
                    }
                }]
            }
        });
        // Add a layer to use the image to represent the data.
        map.addLayer({
            'id': 'layer-id-1',
            'type': 'symbol',
            'source': 'point', // reference the data source
            'layout': {
                'icon-image': 'cat_image', // reference the image
                'icon-size': 0.15,
                "icon-allow-overlap": true,
            }
        });
    });
    map.on('idle', function () {
        console.log('Map.idle');
        const canvasMap = map.getCanvas();
        const dataUrl = canvasMap.toDataURL();
        document.getElementById("imgTag").src = dataUrl;
    });
    map.on('click', (e) => {
        // recognize clicked feature type
        console.log('Map.click');
        const features = map.queryRenderedFeatures(e.point, {
            layers: ['layer-id-1'],
        });
        if (features.length > 0) {
            new mapboxgl.Popup()
                .setLngLat(e.lngLat)
                .setHTML('<p>' + features[0].properties.name + '</p>')
                .addTo(map);
        }
    });
    // Change the cursor to a pointer when the mouse is over the states layer.
    map.on('mouseenter', 'layer-id-1', () => {
        map.getCanvas().style.cursor = 'pointer';
    });
    // Change it back to a pointer when it leaves.
    map.on('mouseleave', 'layer-id-1', () => {
        map.getCanvas().style.cursor = '';
    });
    map.on('movestart', (e) => {
        console.log("Map.movestart");
    });
    map.on('moveend', (e) => {
        console.log("Map.moveend");
    });
});



Check this link for more options

  
  how to control mouse click event

Saturday, September 16, 2023

Create CSV file containing UTF-8 characters in PHP and Javascript | Illegal Character \ufeff Problem

If you create or read a CSV file and the file contains some characters such as ü or ş, then you will find the characters are not displayed correctly in Excel. This is because the default coding of Excel is not UTF-8. To force Excel to use UTF-8, we need to add BOM (Byte Order Mark) in the beginning of the file.

<?php

// When reading csv file using PHP

// BOM as a string for comparison.

$bom = "\xef\xbb\xbf";

// Read file from beginning.

$fp = fopen($path, 'r');

// Progress file pointer and get first 3 characters to compare to the BOM string.

if (fgets($fp, 4) !== $bom) {

    // BOM not found - rewind pointer to start of file.

    rewind($fp);

}

// Read CSV into an array.

$lines = array();

while(!feof($fp) && ($line = fgetcsv($fp)) !== false) {

    $lines[] = $line;

}
// When writting to csv file
$fp = fopen($myFile, 'w');
fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
fputcsv($fp, $otherdata);
// Javascript
var csvFormattedDataTable = '';
csvFormattedDataTable += "\uFEFF";
csvFormattedDataTable += "other stuff";
var encodedUri = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csvFormattedDataTable);
$(buttonName).attr("href", encodedUri);
$(buttonName).attr("download", 'table-data.csv');
$(buttonName).attr("target", '_blank');

Cropping Images in JavaScript | Crop an Image in JavaScript With HTML Canvas

Digital images are composed of a two dimensional array as well as grid of vertical and horizontal pixels, small color squares each called pixel. Image cropping is a way of image editing that involves selecting a portion of an image, hence reducing the number of pixels and changing the aspect ratio as well as size.

A canvas is a html element which is a white region in which you can display or draw graphical elements in modern web browsers. A common way to crop an image in JavaScript is with the HTML5 <canvas> element and then transform the images by calling  the drawImage() function respectively.

Step 1: Create a Canvas in HTML

  Create a <canvas> element in your HTML document using below code:
<canvas id="mycanvas" width="800px" height="400px"></canvas>
Step 2: Create a JavaScript File and a Crop Function

  Define a function named cropImage(), which you can call from within your code when an image loaded from some source or from base64 string.
  
 Add the onload() function to the Image object so that cropImage() runs only after an image is fully loaded on the viewer’s browser.
  
 onload() gets the <canvas> element from your HTML and then will prepare a 2D context object for the canvas.
  
  const canvas = document.getElementById('mycanvas');
const context = canvas.getContext('2d');
Step 3: Load the Image

  Create an image object from any source you want and load an image with the src property from a local drive or the internet or base64 string as per your requirement.
  
  var image = new Image();
image.src = "https://static.image.com/img.jpg";
Step 4: Call the drawImage() Function

  Here’s the syntax of the  drawImage() function when Image object loaded: 

drawImage(image, sx, sy, sWidth, sHeight, dx, dy, dWidth, dHeight);

Crop operations require all nine parameters described below:
  
  image, sx, sy — The image object, the x-coordinate, and the y-coordinate from which to start cropping the image.
sWidth, sHeight — The width of the cropped version, starting from sx and sy.
dx, dy — The point from which to start drawing the cropped version on the canvas.
dWidth, dHeight — The width and height of the cropped version to be displayed.

  Here is how to call the drawImage() function along with the image and context you loaded previously as per our requirement:

  image.onload = function() {
  context.drawImage(image, 100, 100, 200, 200, 50,50, 200, 200);
}

drawImage() performs two steps for further process:

Crop a 200×200-pixel square that starts from the coordinate 100:100 pixels on the original image.
Display the cropped version in the canvas with a top and left margin of 50 pixels.
Below is another solid example of how we can crop image:const image = new Image();
image.crossOrigin='anonymous';
image.onload = () => {
    const canvas: any = document.createElement('canvas');
    const ctx: any = canvas.getContext('2d');
    canvas.height = image.naturalHeight - 25;
    // above line will crop out 25px from bottom 
    canvas.width = image.naturalWidth;
    ctx.drawImage(image, 0, 0);
    const dataUrl: any = canvas.toDataURL();
    // dataUrl is base64 encoded value of cropped image
};
image.onerror = function() {
    alert("Failed to process image");
};
image.src = "https://static.image.com/hello.png";

Thursday, September 7, 2023

Lazy Loading in Angular | Lazy-loading feature modules | Chunk Loading of Components

NgModules are eagerly loaded by default, which means that as soon as the application loads, so do all of the NgModules, whether or not they are immediately required.
  
  


  
  Consider lazy loading in angular — a design strategy that loads NgModules as needed — for big apps with many routes. Lazy loading helps to keep initial bundle sizes smaller, which reduces load times.
  
  





As Angular generates a SPA (Single Page Application), all of its components are loaded at the same time. This implies that a large number of unneeded libraries or modules may also be loaded.
  
    



Lazy loading in angular is the process of loading website components, modules, or other assets when they are needed.





You can utilize lazy loading (or asynchronous loading) with the router if you're constructing your application and utilising feature modules to arrange code. This allows a whole module to load only when needed, reducing the file size of the core bundles and maybe limiting access to bundles to just those who are permitted to use it (like administrative modules).
  
    



Because there is no logical isolation if your application has not been split into various modules, lazily loading them into the application is not feasible. The core notion is that the Angular build process can examine code pathways and optimize code depending on how it's used to produce other files, but it relies heavily on Angular modules to know how code is connected.


Steps to Implement Lazy Loading


  You need to create your first angular project, you can get help from here how-to-create-multi-layout-application.html


  Create a directory under app directory named routes, then create a file under that directory lazy-users-routing.module.ts with below content:
  import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';

const routes: Routes = [];

@NgModule({
    imports: [RouterModule.forChild(routes)],
    exports: [RouterModule],
})
export class LazyUsersRoutingModule {}



  Now create another file in same directory named lazy-user.module.ts with below content:
  import { NgModule } from '@angular/core';
import { CommonModule } from '@angular/common';

@NgModule({
    declarations: [ ],
    imports: [
        CommonModule
    ]
})
export class LazyUserModule { }



  I assume you have already several component, if not you can create components by your own. Now lets check how we implement Lazy Loading with loadChildren.
  

  Our main routing file app-routing.module.ts will be look like below:
  import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';
import {HomeComponent} from "./modules/home/home.component";
import {DefaultLayoutComponent} from "./layouts/default/default.component";
import {LoginComponent} from "./modules/login/login.component";
import {StaticLayoutComponent} from "./layouts/static/static.component";

const routes: Routes = [
    {
        path: '',
        component: DefaultLayoutComponent,
        children: [
            {
                path: '',
                component: HomeComponent,
            }, {
                path: 'users',
                //component: UsersComponent,
                loadChildren: () => import('./routes/lazy-user.module').then(m => m.LazyUserModule)
            }
        ]
    }, {
        path: '',
        component: StaticLayoutComponent,
        children: [
            {
                path: 'login',
                component: LoginComponent,
            }
        ]
    },
];

@NgModule({
  imports: [RouterModule.forRoot(routes)],
  exports: [RouterModule]
})
export class AppRoutingModule { }



  This is the main part of app-routing.module.ts file where we enabled lazy loading of our components:
  {
    path: 'users',
    //component: UsersComponent,
    loadChildren: () => import('./routes/lazy-user.module').then(m => m.LazyUserModule)
}



  Now we will define UsersComponent in LazyUsersRoutingModule like below:
  import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';
import {UsersComponent} from "../modules/users/users.component";

// This route is prefixed by /users
const routes: Routes = [
    {
        path: '', component: UsersComponent
    }
];

@NgModule({
    imports: [RouterModule.forChild(routes)],
    exports: [RouterModule],
})
export class LazyUsersRoutingModule {}



  Our final lazy-user.module.ts is as below:
  import { NgModule } from '@angular/core';
import { CommonModule } from '@angular/common';
import {UsersComponent} from "../modules/users/users.component";
import {LazyUsersRoutingModule} from "./lazy-users-routing.module";

@NgModule({
    declarations: [
        UsersComponent,
    ],
    imports: [
        CommonModule,
        LazyUsersRoutingModule,
    ]
})
export class LazyUserModule { }



  We have enabled lazy loading of component, if we check our network tab we have evidence of that:
  



  GitHub link for this example is https://github.com/pritomkucse/angular-layout/tree/lazy-loading

Pages

Thursday, October 19, 2023

How to fix the MySQL error: Illegal mix of collations for operation union

Sunday, October 8, 2023

PHP image output and browser caching

How to Send files via POST with cURL and PHP (Example)

Tuesday, September 19, 2023

How to Save a Mapbox GL Web Map Extent as an Image | Mapbox GL JS: Export map to PNG

Saturday, September 16, 2023

Create CSV file containing UTF-8 characters in PHP and Javascript | Illegal Character \ufeff Problem

Cropping Images in JavaScript | Crop an Image in JavaScript With HTML Canvas

Thursday, September 7, 2023

Lazy Loading in Angular | Lazy-loading feature modules | Chunk Loading of Components