Mysql trigger for checking duplicate record in Table before insert using trigger

I am to creating a trigger for my table User which checks for duplicates (Mobile number) in the User table before inserting a new row. Assume we do not have any unique index added hence we have to manage uniqueness by programatically.

The insert trigger executes prior to the insertion process. Only if the code succeeds, will the insert take place. To prevent the row from being inserted, an error must be generated. Below is the trigger

DROP TRIGGER if EXISTS before_user_insert; DELIMITER $$ CREATE TRIGGER before_user_insert BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE v1 BIGINT(20); DECLARE m1 VARCHAR(400); SELECT id INTO v1 FROM users WHERE id<>NEW.id AND `mobile`=NEW.`mobile`; IF (v1 IS NOT NULL) THEN SELECT CONCAT('Duplicate mobile number at record - ', v1) INTO m1; SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = m1; END IF; END$$ DELIMITER ;

Which will output as below: SQLSTATE[45000]: <>: 1644 Duplicate mobile number at record - 43

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.

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);

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

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";