Upgrading mysql version 5.7

Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. JSON documents are normalized on creation, and can be compared using most comparison operators such as =, <, <=, >, >=, <>, !=, and <=>; for information about supported operators as well as precedence and other rules that MySQL follows when comparing JSON values

Here are steps to upgrade mysql to mysql5.7 version.

1. Step1 :-

Check the myql packages previously install:-

rpm -qa | grep -i mysql

2.Step2:-

Remove the previous install packages.in our case we have MySql5.5

sudo yum remove mysql-config-5.5.58.1.19.amz1*

it will remove all the packages of mysql5.5.

Once its over we move for next step

3.Step3:-

Install EL6

sudo yum localinstall http://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm

4.Step4:-

sudo yum clean all

5. Step5:-

sudo yum update

6.Step6:-

Sudo yum install mysql-community-server

7.Step7:-

checck the myql version

mysql --version

How to get XML/HTML result set with mysql

you can get directly HTML,XML format of your resultset by just executing the query by following syntax.

uttam@uttam:~$ mysql -u[userName] -p -H -e "[query]" [database]

where -H is to get HTML resultset.you can replace this with -X to get XML output.
-e is to execute the query specified.

go to the shell promt. and thry this example.
mysql -uroot -p -H -e "select * from user" mysql

ADODB with PHP and oracle

Oracle is the popular commercial database which is being used with the PHP.There are vaious ways to connect to oracle database.ADODB library is one of the fastest way to connect php with the oracle database.It has Has multi-tier design. Simple high-level design for beginners, and also lower-level advanced Oracle functionality.ADODB provide very high speed databse connectivity with catching and fastest database abstaction.It also allow multiple prepare statement.

An example to connect php with oracle using ADODB library is as follows.

include"/path/to/adodb.inc.php";
$db = NewADOConnection("oci8");
$db->Connect($tnsName, "scott", "tiger");

$rs = $db->Execute("select * from emp where empno>:emp order by empno",
                array('emp' => 7900));
while ($arr = $rs->FetchRow())
{
print_r($arr);

}

The Execute( ) function returns a recordset object, and you can retrieve the rows returned using $recordset->FetchRow( ).
You can also query the database using the standard Microsoft ADO MoveNext( ) metaphor. The data array for the current row is stored in the fields property of the recordset object, $rs. MoveNext( ) offers the highest performance among all the techniques for iterating through a recordset:

$rs = $db->Execute("select * from emp where empno>:emp", array('emp' => 7900));
while (!$rs->EOF) {
 print_r($rs->fields);
 $rs->MoveNext();
}

For easy pagination support, we provide the SelectLimit function. The following will perform a select query, limiting it to 100 rows, starting from row 201 (row 1 being the 1st row):

$offset = 200; $limitrows = 100;
$rs = $db->SelectLimit('select * from table', $limitrows, $offset);

Caching

You can define a database cache directory using $ADODB_CACHE_DIR, and cache the results of frequently used queries that rarely change. This is particularly useful for SQL with complex where clauses and group-by's and order-by's. It is also good for relieving heavily-loaded database servers.The following example will cache the following select statement for 7200 seconds

$ADODB_CACHE_DIR = '/var/tmp';
$rs = $db->CacheExecute(7200, "select names from allcountries order by 1");

Using Prepare statements

Prepare( ) is for compiling frequently used SQL statement for reuse. For example, suppose we have a large array which needs to be inserted into an Oracle database. The following will result in a massive speedup in query execution (at least 20-40%), as the SQL statement only needs to be compiled once:

MYSQL String functions makes life easier

MYSQL strings function can make your life easier for you.You can use combination of string function to get the complex result needed in some project.String function in mysql helps us to extract what we want from a string and that can avoid lot of code.The common String functions available in MYSQL can be found on the link http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Use of MYSQL String function.

consider the following table which contains caseId and you have to extract maximum number at the last of caseId, those belong to id 1.

caseId	id	Value
JET/2005-2006/1	1	15
JET/2005-2006/2	1	25
JET/2005-2006/1	2	15
JET/2005-2006/2	1	2
JET/2005-2006/2	2	12
JET/2005-2006/3	1	5

you can use the following query to get the result.

SELECT max(substring(caseId,locate(”/”,caseId,locate(”/”,caseId)+1)+1)) as maxCaseId FROM `test` WHERE id=1

The result of the query is as follows.

maxCaseId
3

This query work as follows.

inner locate function get’s first occurrence of “/” [let's 4].and outer locate function get first occurrence of “/” [let's 17] in the string starting from position 4+1=5.so finally in subString function will give all the string values after position 17+1=18.So by this we will have all numbers those belongs to id 1.now finally max function will get max number within all the numbers found by substring function.

if you want to get the maximum integral value, you got to cast it to an integer - otherwise, on having a caseId value like JET/2005-2006/23, it would still return 3 as maximum & not 23.

SELECT
MAX(0 + SUBSTRING(caseId, LOCATE(’/', caseId, LOCATE(’/', caseId) + 1) + 1)) AS maxCaseId
FROM `test` WHERE id = 1;

SELECT
MAX(0 + RIGHT(caseId, LOCATE(”/”, REVERSE(caseId)) - 1)) AS maxCaseId
FROM `test` WHERE id = 1;

even reversing strings can avoid looping of locate.

SELECT MAX(RIGHT(caseId, LOCATE(”/”, REVERSE(caseId)) - 1)) AS maxCaseId FROM `test` WHERE id = 1

So This is just an example to explain uses of string functions available in MySql. There may be lot’s of other situation where you can use string function provided by mysql and get your work done rather than getting result by writing complex logic in programming.