Found 4219 Articles for MySQLi

The syntax for mass update with CASE WHEN/ THEN/ ELSE is as follows −UPDATE yourTableName set yourColumnName=case when yourColumnName=Value1 then anyUpdatedValue1 when yourColumnName=Value2 then anyUpdatedValue2 when yourColumnName=Value3 then anyUpdatedValue3 when yourColumnName=Value4 then anyUpdatedValue4 else yourColumnName end;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table CaseUpdateDemo -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CaseUpdateDemo values(1, 'John'); Query OK, 1 row affected ... Read More

Whenever your column has an auto incremented primary key then there is an advantage that you do not need to give value for that column in the INSERT command. This means MySQL will give the value for that column.To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table AutoIncrementedPrimary    -> (    -> Id int auto_increment,    -> Name varchar(100),    -> Age int,    -> Primary key(Id)    -> ); Query OK, 0 rows affected (0.56 sec)Now insert records for the column Name and Age ... Read More

The MySQL CASE works like a switch statement. The syntax of CASE is as follows −Case 1 − Compare StatementCase when anyCompareStatement then value1 when anyCompareStatement then value2 . . N else anyValue end as anyVariableName;Case 2 − ConditionsThe second syntax can be used when you are selecting only one column. The syntax is as follows −case yourColumnName when condition1 then result1 when condition1 then result2 . . N else anyValue end;To understand the above concept, let us use select statement.Case 1The query is as follows −mysql> select    -> case when 45 < 55 then '55 is greater than ... Read More

You can use date-format() function from MySQL to get the first day of every corresponding month. The syntax is as follows −select DATE_FORMAT(yourDatetimeColumnName ,'%Y-%m-01') as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table getFirstDayOfMonth -> ( -> DueDatetime datetime -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into getFirstDayOfMonth values(date_add(now(), interval 3 month)); Query OK, 1 row affected ... Read More

To encode base64, you can use two functionalities −TO_BASE64()FROM_BASE64()The syntax for base64 encode is as follows −SELECT TO_BASE64(anyValue) as AnyVariableName;The syntax for base64 decode is as follows −SELECT FROM_BASE64(encodeValue) as anyVariableNameTo understand the above concept, let us use the above syntax −Case 1 − EncodeTo encode the value, use the to_base64(). The query is as follows −mysql> select TO_BASE64('Password') as Base64EncodeValue;Output+-------------------+ | Base64EncodeValue | +-------------------+ | UGFzc3dvcmQ=      | +-------------------+ 1 row in set (0.00 sec)Case 2 − DecodeTo decode the value, use the from_base64(). The query is as follows −mysql> select FROM_BASE64('UGFzc3dvcmQ=') as Base64DecodeValue;Output+-------------------+ | Base64DecodeValue | +-------------------+ | ... Read More

You can use the in-built function from_unixtime() to convert INT to DATETIME. The syntax is as follows −SELECT FROM_UNIXTIME(yourColumnName, ’%Y-%m-%d') as AnyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table IntToDateDemo -> ( -> Number int -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query to insert record is as follows −mysql> truncate table IntToDateDemo; Query OK, 0 rows affected (4.11 sec) mysql> insert ... Read More

In BIGINT(8), the number 8 represents how the data will be displayed. It does not affect the storage. The number is used to display width.BIGINT takes 8 bytes i.e. 64 bits. The signed range is -9223372036854775808 to 9223372036854775807 and unsigned range takes positive value. The range of unsigned is 0 to 18446744073709551615.To understand bigint(8), let us create a table with BIGINT(8) and zerofill column −mysql> create table BigIntDemo8 -> ( -> Number1 BIGINT(8) not null, -> Number2 BIGINT(8) unsigned zerofill not null -> ); Query OK, 0 rows ... Read More

To get the index of last substring in a given string, use the char_length() function. First, we need to calculate string length and subtract the last sub string length from the entire length. The difference in length is index of substring.SyntaxThe syntax is as follows −select CHAR_LENGTH(yourColumnName) - LOCATE('yourDelimiter ', REVERSE(yourColumnName))+1 as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SubStringIndexDemo    -> (    -> Words varchar(200)    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table ... Read More

You can achieve with the help of timestampdiff() method from MySQL. The syntax is as follows −SyntaxSELECT ABS(TIMESTAMPDIFF(HOUR, yourColumnName1, yourColumnName2)) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DifferenceInHours    -> (    -> StartDateTime datetime,    -> EndDateTime datetime    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInHours values('2018-12-20 10:00:00', '2018-12-19 12:00:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DifferenceInHours ... Read More

To order by relevance, use the case statement. To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table OrderByRelevance    -> (    -> UserId int,    -> UserName varchar(200)    -> ); Query OK, 0 rows affected (0.51 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByRelevance values(101, 'Carol Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into OrderByRelevance values(102, 'Carol Adams'); Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More