Found 4219 Articles for MySQLi

You need to use FIND_IN_SET() function to select MySQL rows in the order of IN clause. The syntax is as follows −SELECT yourVariableName.* FROM yourTableName yourVariableName WHERE yourVariableName.yourColumnName IN(value1, value2, ...N) ORDER BY FIND_IN_SET( yourVariableName.yourColumnName, 'value1, value2, ...N');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InDemo -> ( -> CodeId int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command. The query is ... Read More

You need to use tinyint(1) unsigned NULL to store the value 0, 1 and null values. The syntax is as follows −yourColumnName TINYINT(1) UNSIGNED NULL;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StoreValue0and1orNULLDemo -> ( -> isDigit TINYINT(1) UNSIGNED NULL -> ); Query OK, 0 rows affected (0.63 sec)Now you can insert records 0, 1, and NULL in the table using insert command. The query is as follows −mysql> insert into StoreValue0and1orNULLDemo values(0); Query OK, 1 row ... Read More

You need to use DATE_FORMAT() for this. The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%k:%i') as anyAliasName FROM yourTableName;You can use ‘%H:%i’ for the same result. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table TimeDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> LastLoginTime time    -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into TimeDemo(LastLoginTime) values('09:30:35'); Query OK, 1 row affected ... Read More

You need to use date type to work with date before 1970 because date stores value from 1000 to 9999. A date type can be used when you need to work with date part only not for time purpose.MySQL gives the data in the following format. The format is as follows −‘YYYY-MM-DD’The starting date range is as follows −1000-01-01The ending date range is as follows −9999-12-31To understand what we discussed above, let us create two tables. The query to create first table is as follows −mysql> create table DateDemo -> ( -> Id int ... Read More

To insert a special character such as “ ‘ “ (single quote) into MySQL, you need to use \’ escape character. The syntax is as follows −insert into yourTableName(yourColumnName) values(' yourValue\’s ');To understand the above syntax, let us create two tables. The query to create first table is as follows −mysql> create table AvoidInsertErrorDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Sentence text -> ); Query OK, 0 rows affected (0.53 sec)Now you can insert special character such as ‘ in the table using insert command. The query is as follows −mysql> insert into AvoidInsertErrorDemo(Sentence) values('a ... Read More

To get the total number of rows when using LIMIT, use the following syntax −select SQL_CALC_FOUND_ROWS * FROM yourTableName LIMIT 0, yourLastValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RowsUsingLimit -> ( -> Id int NOT NULL, -> Name varchar(10) -> ); Query OK, 0 rows affected (3.50 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into RowsUsingLimit values(10, 'Larry'); Query OK, ... Read More

You can use ORDER BY ASC to order timestamp values in ascending order with TIMESTAMP() method.The following is the syntax using TIMESTAMP() −SELECT timestamp( yourTimestampColumnName ) as anyAliasName From yourTableName order by 1 ASCTo understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Timestamp_TableDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> yourTimestamp timestamp -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records in the table using insert ... Read More

You need to close the subquery in a parenthesis. The syntax is as follows −select if((select count(*) from yourTableName ), 'Yes', 'No') as anyAliasName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SelectIfDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(10) -> ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SelectIfDemo(Name) values('John'); Query OK, ... Read More

To disable the warning while connecting to a database in Java, use the below concept −autoReconnect=true&useSSL=falseThe complete syntax is as follows −yourJdbcURL="jdbc:mysql://localhost:yourPortNumber/yourDatabaseName?autoReconnect=true&useSSL=false";Here is the warning message if you do not include “useSSL=false” −Wed Feb 06 18:53:39 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore ... Read More

You can use IFNULL() property or simple IF() with IS NULL property. The syntax is as follows −INSERT INTO yourTableName(yourColumnName1, yourColumnName2) VALUES('yourValue’', IF(yourColumnName1 IS NULL, DEFAULT(yourColumnName2), 'yourMessage'));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Post -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(10), -> UserPostMessage varchar(50) NOT NULL DEFAULT 'Hi Good Morning !!!' -> ); Query OK, 0 rows affected (0.67 sec)Now you can ... Read More