Tech Solutions

Usage 1 SELECT CONCAT("This  ", "is  ", "test ", "concatenation") AS testConcatenation; Syntax concat(exp1, exp2, exp3, ..) - exp1 is required Usage 2 Concat table field and text together SELECT CONCAT(NAME, " is creted at  ", created_at, " ") AS Address FROM province; Enjoy...

LOAD DATA INFILE "C:/Users/USER/Desktop/test.csv" INTO TABLE test COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; Change the file name accordingly.   There may be some issues. 1. SQL Error (1290): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement You need to add LOCAL modifier LOAD DATA statement. Otherwise the following error occurred. So it looks as follows. LOAD DATA LOCAL INFILE "C:/Users/USER/Desktop/test.csv" INTO TABLE test COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; 2. Even if we add LOCAL modifier, there will be another issue as follows. SQL Error (3948): Loading local data is disabled; this must be enabled on both the client and server sides.   3. So you need to enable following SET @ @GLOBAL .local

There was a drop down which had a huge list of employees. But some of them hadn't name. It was empty. But not NULL in database.  Initial query was as follows.  SELECT emp_id, name from employees order by name asc But it shows empty options in select list first. Then I tried to replace name which was NULL using IFNULL function.  SELECT aemp_id, IFNULL(name, concat('Employee - ', emp_id)) as name from employees  where name="" order by name asc But it was failed due to empty values for name field. Then I checked for empty value for name field.              NULLIF(name, "") It will return NULL value if it is empty. Then I used  COALESCE function.It returns first NULL or NULL if there no-null values in the list. Complete query as follows. SELECT emp_id, COALESCE(NULLIF(name, ""), concat('Employee - ', emp_id)) as name from employees where name="" order by name asc enjoy. Chamath Gunasekara

I wanted to retrieve multiple counts in same table on different criteria. The sum of 1's works same as count where our expression is true. :-) SELECT sum(approved = 1) as visible_prop_count, sum(approved = 2) as private_prop_count, sum(approved = 3) as pending_app_prop_count, sum(featured_property=1) as featured_prop_count, sum(approved = 0) as draft FROM `jos_mypod_property` WHERE 1 visible_prop_count private_prop_count pending_app_prop_count featured_prop_count draft 4 0 0 4 1

Install the server as a service using this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --install Install the server as a service using this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --remove Start Mysql from command prompt using net command: NET START MySQL Stop Mysql from command prompt using net command: NET STOP MySQL -- Thanks & Regards, Chamath Gunasekara