To add or subtract dates through MySql, here are both of the examples, where you will find how to add and how to subtract a date, from a given date, in MySql:

MySql Add Date

To add MySql date through a query, like if you want to get a date, for example:

MySql Add Day

If you want to find out what date will it be after two days, write the following query:
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY)

MySql Add Month

If you want to find out what date will it be after one month, write the following query:
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH)

MySql Add Year

Or if you want to find out what date will it be after one year, write the following query:
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)

MySql Subtract Date

To subtract MySql date through a query, like if you want to get a date, for example:

MySql Subtract Day

If you want to find out what date will it be after two days, write the following query:
SELECT DATE_SUB(NOW(), INTERVAL 2 DAY)

MySql Subtract Month

If you want to find out what date will it be after one month, write the following query:
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)

MySql Subtract Year

Or if you want to find out what date will it be after one year, write the following query:
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)

Also note that, if you want to get only date, and not the hours, minutes and seconds, then simple add the DATE() function at the start of the query, like:

SELECT DATE(DATE_SUB(NOW(), INTERVAL 2 DAY))

Are you looking for Ubuntu MySQL, or how to install Ubuntu in MySQL. Following post will help you install Ubuntu MySQL to let you able to use MySQL in your Ubuntu.

Ubuntu MySQL

If you are looking for a free database server, which will let you manage your websites for personal use or organizational use, then MySQL will be one of the best and free alternatives to the paid database servers. You can download and install MySQL for free, which you can use in different operating systems like Windows, Ubuntu (Linux) etc.

How To Install Ubuntu MySQL

To install MySQL in Ubuntu, you can visit the following post, which includes the installation of Ubuntu LAMP (Linux Apache MySQL PHP). However, if you are interested in installing MySQL only, then you can also take advantage from this post, as each of these programs and their installation are defined separately in this post:
Install PHP, MySQL and Apache (LAMP) on Ubuntu (Linux)

While going through a problem, I got a situation where I wanted to have a result set in a sorting order which is defined by me. Not ORDER BY ASC, not ORDER BY DESC, not ORDER BY RAND(), but ORDER BY FIELD i.e. the values which are defined by me, which could be random as well, but not ORDER BY RAND().

MySql Custom Order By Example

For example, if you have a result set of a table, containing the names of students, and you have set them as order by student names, and now you want the same ordering format of student names, in another table, which only only contains ids of the student. One easy way could be to make a join with the master table, containing the students names, and use simple Order By student name format, but there could be another way out, where you wont be needing to join the other table, just for the sake of ordering. You can use following type of query, where you already have the ordering student ids, i.e. you already know that which id will come first, and next, and so on.

ORDER BY FIELD

Following is the table tbl_test, where field id will be containing the student ids, through which we will be fetching 5 student ids, and we already know their sorting order, that which id will come first, and next and so on.

SELECT * FROM tbl_test
WHERE id IN (1,2,3,4,5)
ORDER BY FIELD(id, 1,3,5,4,2);

If you are looking for the query which will return you the values in the order of null or blank fields first and others next or vice versa, like if there is a table which includes the address of the people, but it contains blank or null values as well, and you have to make them order in ascending or descending order, like the blank or null fields come first and the others next, or the filled fields come first and the blank / null next, then the following queries may help:

Order By Null Fields

To make your results where you want to adjust the null fields first and others next or vice versa, try the following queries, where tbl_test is the table name, while the field name which contains the null and filled values is address:

Order By Null Fields In Ascending Order

SELECT *, IF(address IS NULL, 0, 1) AS status
FROM tbl_test
ORDER BY status

or

SELECT *, address IS NULL AS status
FROM tbl_test
ORDER BY status DESC

Order By Null Fields In Descending Order

SELECT *, IF(address IS NULL, 0, 1) AS status
FROM tbl_test
ORDER BY status DESC

or

SELECT *, address IS NULL AS status
FROM tbl_test
ORDER BY status

Order By Blank Fields

To make your results where you want to adjust the blank fields first and others next or vice versa, try the following query, where tbl_test is the table name, while the field name which contains blank and filled values is address:

Order By Null Fields In Ascending Order

SELECT *, IF(address = '',0,1) AS status
FROM tbl_test
ORDER BY status

Order By Null Fields In Descending Order

SELECT *, IF(address = '',0,1) AS status
FROM tbl_test
ORDER BY status DESC

If you are facing this error of Error establishing a database connection while installing WordPress or some other API, whether in Windows or Linux, then you would probably be missing the configuration of database connection in your config file, which is wp-config.php in WordPress.

Configuration File

For resolving this issue, you first have to make changes in your configuration file wp-config.php.

Open up wp-config.php file in your IDE, which will be existing in your wordpress folder, and see if the DB name, DB user and DB password variables are defined properly.

Note: You check check out the following URL for Free PHP IDEs in Ubntu (Linux):
http://www.webtechquery.com/index.php/2010/01/free-php-ides-for-ubuntu-linux/

Create Database

Or if you have not yet created the database for WordPress, then simply create one through any MySql Client, with the name of “wp” or whatever has been mentioned in the DB_NAME variable of wp-config.php file.

Hope this will help you in resolving the issue of Error establishing a database connection in Windows or Linux.

While using MySql OR and MySql AND operators, sometimes we face problems for not getting the desired results. Why ? May be because using OR with AND operators is complicated ? or otherwise buggy ? But its not like that. If we want to use MySql operators AND and OR, then we have to make sure what we are assuming is what actually MySql Query is executing.

For example, we have a table tbl_products, with the fields product_name and amount, and we want to fetch the results where:

• Amount contains values lesser than 50
• Product contains values ‘Nokia’ or ‘Samsung’

So, if we write the follwing query:

SELECT * FROM tbl_products
WHERE amount < 50
AND product_name = 'Nokia'
OR product_name = 'Samsung';

then our results may not match, becuase MySql will be taking the 1st two conditions

WHERE amount < 50
AND product_name = 'Nokia'

to be treated as one, and the other condition as second. Which means, it will match the Nokia products having amount less than 50, or, it will match the Samsung products only, with no amount check. Whereas, our need was to get the amount less than 50 for any Nokia or Samsung products.

So the following query will take the accurate results:

SELECT * FROM tbl_products
WHERE amount < 50
AND
(
	product_name = 'Nokia' OR product_name = 'Samsung'
);

or this query could also be written as:

SELECT * FROM tbl_products
WHERE
(
	product_name = 'Nokia' OR product_name = 'Samsung'
)
AND amount < 50;

Hope this will help you in using MySql AND and OR operators.

If you have a bulk amount of data in MySql, which includes the date or datetime field, and you want to view it as Yearly, Monthly or Daily basis, for example, if you want to view the total number of members, registered in a year, month or day, then the following queries might be helpful. In the following queries, we will be using the table as tbl_date, where id is the auto increment field, and inserted_date is the date / datetime field.

View Date Into Years In MySql

For viewing the date into years, the following query will be helpful:

SELECT count(id), DATE_FORMAT(inserted_date,"%Y") as years
FROM tbl_date
GROUP BY years
ORDER BY inserted_date;

View Date Into Months In MySql

For viewing the date into months, the following query will be helpful:

SELECT count(id), DATE_FORMAT(inserted_date,"%M") as months
FROM tbl_date
GROUP BY months
ORDER BY inserted_date;

View Date Into Days In MySql

For viewing the date into days, the following query will be helpful:

SELECT count(id), DATE_FORMAT(inserted_date,"%W") as days
FROM tbl_date
GROUP BY days
ORDER BY inserted_date;

If you want to count number of words in a field or column, in MySql, then this could be the right place for you. As far as I know, there is no MySql function which returns the total number of words used in a field, so we have to use some techniques or tricks for getting the words count in MySql.

Number Of Words In A Field For Each Row

For example, if there is a table tbl_test, having a field total_words, then the following query will be helpful:

SELECT LENGTH(total_words) - LENGTH(REPLACE(total_words, ' ', ''))+1
FROM tbl_test;

This will return the number of words in each row, which we have in the field total_words.

Number Of Words In A Field For All Rows

If there’s a requirement to get the sum of all rows, like how many words are there in the rows of total_words field, then the following query will be helpful:

SELECT SUM(LENGTH(total_words) - LENGTH(REPLACE(total_words, ' ', ''))+1)
FROM tbl_test;

It has been a long time using MySql, and getting the value of the fields is as simple as ABC.

Ever Imagined of using Field Names instead of Field Values

But have we ever imagined, if, in some scenario, we get to deal with the name of that table’s field, instead of the values. What if we have to print the field or column names in a row first, and then their values in the next coming rows?

How to Show Field Names ?

If we want to show the name of the fields or columns, like if there’s a table members, with the fields id, full_name, address and phone, and we want to display the field names of table members, then the following query will help:

SHOW FIELDS FROM members;

We can also use the WHERE clause in SHOW FIELDS, like if we want to display the id and name only, then the following query will help:

SHOW FIELDS FROM members
WHERE FIELD IN ('id', 'name');

Or if we dont want to display the id and address, then the following query will help:

SHOW FIELDS FROM members
WHERE FIELD NOT IN ('id', 'address');

If you are about to save an IP address into a MySql table of type char or varchar, then it is preferable to make use of the SQL function INET_ATON and save it in integer format instead of char or varchar.

Why to convert from IP Address to Numeric (INET_ATON) ?

Well, this will let you save the value into integer instead of character, and when later, you want to search your saved IP addresses, then you can assume that searching on an integer field is pretty much faster than the string i.e. char, varchar etc.
Even it may help you in creating integer indexes instead of string, for the searching purpose.

How to convert from IP Address To Numeric (INET_ATON) ?

While saving the IP address into numeric or integer field, you can simply use the function as follows:

INSERT INTO tbl_test (id, ip_to_numeric)
VALUES (1, INET_ATON('117.34.70.115'));

Where tbl_test is the table name, and ip_to_numeric is the numeric field.

How to convert back from Numeric to IP Address (INET_NTOA) ?

After successfully converting and saving the values from IP Address into Numeric, you can reconvert those converted values into IP Address through the following query:

SELECT INET_NTOA(ip_to_numeric)
FROM tbl_test;

This will dispolay all the converted numeric IP addresses into their original format.