Found 4219 Articles for MySQLi

Convert dd/mm/yyyy string to Unix timestamp with the help of UNIX_TIMESTAMP(). The syntax is as follows −SELECT UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, '%d/%m/%Y')) 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 ConvertddmmyyyyInUnixTimeStamp    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Created_at varchar(30),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('10/11/2012'); Query OK, 1 row affected (0.21 sec) mysql> ... Read More

To select a single row in MySQL, you can use LIMIT. At first, let us create a table. The query to create a table is as follows −mysql> create table selectWithPrimaryKey    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> Marks int,    -> PRIMARY KEY(Id)    -> ); 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 selectWithPrimaryKey(Name, Age, Marks) values('Larry', 24, 98); Query OK, 1 row affected (0.15 sec) mysql> insert into ... Read More

You can change the date format in PHP using date() fucntion. The syntax is as follows −date(d/m/Y, yourDateTimeVariable);In PHP, convert string to date using strtodate(). Here is the PHP code used to format the datetime −$LogintDate = strtotime('2019-01-12'); echo date('d/m/Y', $LogintDate);The snapshot of code is as follows −The following is the output −12/01/2019You can achieve in MySQL with the help of date_format() function. The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, ’%d/%m/%Y’) as anyVariableName FROM yourTableName;To understand the above syntax, let us create a table −mysql> create table Date_FormatDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ... Read More

To find out a varchar contains a percent sign in MySQL, you can use LIKE operator. The syntax is as follows −SELECT * FROM yourTableName WHERE yourColumnName like '%|%%' escape '|';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindPercentInVarcharDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Words varchar(30),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.51 sec)Insert some records with % sign using insert command. The query is as follows −mysql> insert into FindPercentInVarcharDemo(Words) values('This is ... Read More

To convert the date time to a number in MySQL, the syntax is as follows −SELECT UNIX_TIMESTAMP(yourColumnName) 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 DateTimeToNumberDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> releasedDate datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DateTimeToNumberDemo(releasedDate) values(now()); Query OK, 1 row affected (0.19 sec) mysql> insert into ... Read More

You can use NULLIF() from MySQL to replace 0 with NULL. The syntax is as follows −SELECT *, NULLIF(yourColumnName, 0) 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 Replace0WithNULLDemo    -> (    -> Id int NOT NULL auto_increment,    -> Name varchar(20),    -> Marks int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.53 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into Replace0WithNULLDemo(Name, Marks) values('John', ... Read More

You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function.Case 1:The syntax is as follows:yourDateColumnName date default ‘yourDateValue’;Case 2:The syntax is as follows:yourDateColumnName datetime default now();To understand the above, let us create a table. The query to create a table is as follows:mysql> create table DefaultCurrentdateDemo    -> (    -> LoginDate datetime default now()    -> ); 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 DefaultCurrentdateDemo values(); Query OK, 1 ... Read More

To find lowest Date(custom) in MySQL, let us first create a table. The query to create a table is as follows:mysql> create table FindMinimumDate    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> yourDay varchar(2),    -> yourMonth varchar(2),    -> yourYear varchar(4),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into FindMinimumDate(yourDay, yourMonth, yourYear) values('21', '11', '2019'); Query OK, 1 row affected (0.10 sec) mysql> insert into FindMinimumDate(yourDay, yourMonth, yourYear) values('20', '10', '2020'); Query OK, ... Read More

To select distinct combinations from two columns, you can use CASE statement. Let us create a table with some columns.The query to create a table is as follows −mysql> create table select_DistinctTwoColumns    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FirstValue char(1),    -> SecondValue char(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.57 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into select_DistinctTwoColumns(FirstValue, SecondValue) values('s', 't'); Query OK, 1 row affected (0.12 sec) mysql> insert into select_DistinctTwoColumns(FirstValue, SecondValue) ... Read More

If you do not want to include start and end value in between, then use the following syntax −SELECT * FROM yourTableName WHERE yourColumnName BETWEEN yourStartingValue and yourEndingValue and    yourColumnName not in (yourStartingValue , yourEndingValue );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table BetweenWithoutEndPoints    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.54 sec)Now you can insert some records in the table using ... Read More