MYSQL - Select statement to select multiple rows that contains multiple values

Usually if we have multiple value to look up in table we might use

SELECT * FROM <tablename> 

WHERE <field> LIKE '%parameter1%' OR <field> LIKE '%parameter2%' OR <field> LIKE '%parameter3%';

If we have a lot of parameters, we might find it difficult to write the statement.

Therefore instead of using OR, we can also use REGEXP as below

SELECT * FROM <TABLENAME>
WHERE <field> REGEXP 'parameter1|parameter2|parameter3|parameter4|parameter5';

Sending email from server using SMTP (SMTP Error, Permission denied)

I face below error for quite sometimes.

1	SMTP -> ERROR: Failed to connect to server: Permission denied (13)

After going through a lot of configuration and research, the problem occurred by SELinux is blocking the communication.

SELinux does not allow Apache (httpd,phpmailer) to use the sendmail function and make any sort of network connection.

However, the solution was found that we need to enable the sendmail protocol manually on Centos due to SELinux.

We can check the protocol setting by following below command

1	getsebool httpd_can_sendmail

And the solution is

1 2 3 4 5

setsebool -P httpd_can_sendmail 1 OR /usr/sbin/setsebool -P httpd_can_sendmail on

 Resource : http://blog.innovsystems.com/php/smtp-error-failed-connect-server-permission-denied-13

MYSQL - getting -0 for double field type

While using double data type, I face some problem where MYSQL will save 0.00 as  -0
Unable to find out what is the actual problem, there for I came out with temporary solution until i found the root cause.

Since we cannot use,

select <fieldname> from <table> where <fieldname> = -0;

You can retrieve the data by using below statement.

select <fieldname> from <table> where <fieldname> = cast(<fieldname> AS CHAR(15))="-0";

UPDATE <table> SET <fieldname> = 0 WHERE cast(<fieldname> AS CHAR(15))="-0";

Excel/Open Office date difference

You can get date difference between 2 dates on spreadsheet column by using below formula

=A1-B1

Screenshot

MYSQL - Add specific time/day to given date

UPDATE tablename
SET field2 = date_add(field1,INTERVAL xx DAY);
Example :

UPDATE item_order set duedate = DATE_ADD(dateorder, INTERVAL 30 DAY);