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

MYSQL - Remove character/blank space in table fields.

UPDATE table_name
SET field_name= REPLACE(field_name,'<character to find>', '<character to replace>');

MYSQL - Join Table

SELECT a.field1,a.field2,b.field3,b.field4
FROM table1 a, table2 b
where a.field1 = b.field3

MYSQL - Insert Select Statement

i use this statement when i want to copy data in selected fields in a table.

INSERT INTO student_subject (subjectid, noofsem)
SELECT subjectid, noofsem FROM study_plan
WHERE student_subject.intakeid = study_plan.intakeid

MYSQL - Update Select Statement

UPDATE subject_section
SET facultyid =
(SELECT facultyid FROM ref_subject
WHERE subjectid = subject_section.subjectid) 

MYSQL - count the same value (occurrences) in table

Count number of occurrences in DB 

for example, you want to get how many of users with the same DOB in your DB

SELECT dob,
COUNT(dob) AS NumOccurrences
FROM users
GROUP BY dob
HAVING ( COUNT(dob) > 0 );

PHP - array function

Check value exist in array

in_array($value_to_check, $array_to_check);

Check key exist in array

array_key_exists($key_to_check, $array_to_check);