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