Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 240 of 547
Change date format (in DB or output) to dd/mm/yyyy in PHP MySQL?
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 MoreMySQL LIMIT to select a single row
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 MoreConvert dd/mm/yyyy string to Unix timestamp in MySQL?
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 MoreMySQL get hash value for each row?
Get hash value of each row using MD5() function from MySQL. The syntax is as follows −SELECT MD5(CONCAT(yourColumnName1, yourColumnName2, yourColumnName3, .......N)) 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 getHashValueForEachRow -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Age int, -> Marks int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.25 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into getHashValueForEachRow(Name, Age, ...
Read MoreHow to map keys to values for an individual field in a MySQL select query?
You can use CASE statement in MySQL to map keys to values for an individual field in select query. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, .........N ( CASE WHEN yourColumnName = 1 THEN 'ENABLED' ELSE 'DISABLED' END ) AS anyVariableName FROM yourTableName;You can use IF() function also for the same purpose. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, .........N ,IF(yourColumnName, 'ENABLED', 'DISABLED') 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 MapKeys -> ( ...
Read MoreMySQL order by string with numbers?
To MySQL order string with numbers, the following is the syntax, wherein we have used ORDER BY, SUBSTR() and CAST() −SELECT *FROM yourTableName ORDER BY SUBSTR(yourColumnName FROM 1 FOR 2), CAST(SUBSTR(yourColumnName FROM 2) AS UNSIGNED);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByStringWithNumbers -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Words varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert ...
Read MoreHow can I update the boolean values in MySQL?
You can update boolean value using UPDATE command. If you use the BOOLEAN data type, MySQL internally convert it into tinyint(1). It can takes true or false literal in which true indicates 1 to tinyint(1) and false indicates 0 to tinyint(1).The syntax is as follows −UPDATE yourTableName SET yourColumnName = yourValue WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UpdateBooleans -> ( -> Id int NOT NULL AUTO_INCREMENT, -> isSuccessful BOOLEAN, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows ...
Read MoreIs `definer` required when creating a MySQL stored procedure?
No, definer part is not compulsory when you are creating a stored procedure. It is used when you want to create a definer.Check all the user and host from the MySQL.user table −mysql> select user, host from mysql.user;The following is the output −+------------------+-----------+ | user | host | +------------------+-----------+ | Manish | % | | User2 | % | | mysql.infoschema | % | | mysql.session | % ...
Read MoreMySQL select * with distinct id?
You can use GROUP BY command for select with distinct id. The syntax is as follows −SELECT *FROM yourTableName GROUP BY yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DistinctIdDemo -> ( -> Id int, -> Name varchar(20), -> Age int -> ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command. Here, we have added ID with duplicate values.The query is as follows −mysql> insert into DistinctIdDemo values(1, 'Mike', 23); Query OK, 1 row ...
Read MoreMySQL BETWEEN without the beginning and endpoints?
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