Found 4219 Articles for MySQLi

You can use in-built function STR_TO_DATE() from MySQL. The syntax is as follows −SELECT STR_TO_DATE(CONCAT(yourYearColumName, '-', LPAD(yourMonthColumName, 2, '00'), '-', LPAD(yourDayColumName, 2, '00')), '%Y-%m-%d') 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 DateCreateDemo    −> (    −> `Day` varchar(2),    −> `Month` varchar(2),    −> `Year` varchar(4)    −> ); Query OK, 0 rows affected (1.68 sec)Insert values for all fields using insert command. The query is as follows −mysql> insert into DateCreateDemo values('15', '12', '2018'); Query OK, 1 row affected (0.09 ... Read More

You can use subquery to order randomly and display name in asending order. The rand() is used for random, whereas ORDER BY is used to display name records in ascending order. The syntax is as follows −select *from (    select *from yourTableName order by rand() limit anyIntegerValue; ) anyVariableName order by yourColumnName;To understand the above concept, let us create a table. We have an ID as sell as Name, which we want in Ascending order. The query to create a table is as follows −mysql> create table OrderByRandName    −> (    −> Id int,    −> Name varchar(100) ... Read More

To compare dates in MySQL except time portion of a datetime field, you can use DATE() function. The syntax is as follows −select *from yourTableName where date(yourColumName) = yourDate;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table ComparingDate    −> (    −> Name varchar(100),    −> Login datetime    −> ); Query OK, 0 rows affected (0.50 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ComparingDate values('John', '2014-04-06 22:50:45'); Query OK, 1 row affected ... Read More

To adjust display settings of MySQL command line, use the /G at the end of MySQL queries instead of semicolon(;).The syntax is as follows −SELECT *FROM yourTableName \GThe above syntax adjusts the display settings. Here we will display records in row format from our sample ‘studenttable’ table which we created using CREATE −mysql> create table StudentTable    −> (    −> Id int,    −> Name varchar(100)    −> ); Query OK, 0 rows affected (0.65 sec)To get all the records −mysql> select *from StudentTable;The following displays the records −+------+---------+ | Id   | Name    | +------+---------+ | ... Read More

To perform echo print statements while executing SQL scripts, use the following syntax.The syntax is as follows −SELECT ‘anyStringValue as’ ‘;The query is as follows −mysql> select 'This is a SQL Script' AS' ';The following is the output −+----------------------+ | | +----------------------+ | This is a SQL Script | +----------------------+ 1 row in set, 1 warning (0.00 sec)You can add dynamic data to your status like insert, update and delete with the help of concat() function. The query is ... Read More

Use aggregate function sum() to sum the elements of a column in MySQL. The syntax is as follows −select sum(yourColumnName1) as anyVariableName1, sum(yourColumnName2) as anyVariableName2, sum(yourColumnName3) as anyVariableName3, ............N from yourTableName;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table SumDemoOnColumns −> (    −> First int,   −> Second int, −> Third int −> ); Query OK, 0 rows affected (0.56 sec)Insert some data in the table using insert command. The query is as follows ... Read More

The Sum() is an aggregate function in MySQL. You can use sum query with if condition. To understand the sum query with if condition, let us create a table.The query to create a table −mysql> create table SumWithIfCondition    −> (    −> ModeOfPayment varchar(100)    −> ,    −> Amount int    −> ); Query OK, 0 rows affected (1.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumWithIfCondition values('Offline', 10); Query OK, 1 row affected (0.21 sec) mysql> insert into SumWithIfCondition values('Online', 100); Query OK, 1 row affected ... Read More

To combine date and time column into a timestamp, you can use cast() function with concat().The syntax is as follows −select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName;In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table DateAndTimeToTimestamp −> ( −> Duedate date, −> DueTime time −> ); ... Read More

You can use in-built function UNIX_TIMESTAMP() from MySQL to get the timestamps and the difference between two timestamps. The syntax is as follows −SELECT UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table DifferenceInSeconds −> ( −> FirstTimestamp TIMESTAMP, −> SecondTimestamp TIMESTAMP −> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInSeconds values('2012-12-12 ... Read More

You can create a function using create function command. The syntax is as follows −delimiter // DROP FUNCTION if exists yourFunctionName; CREATE FUNCTION yourFunctionName(Parameter1, ...N) returns type BEGIN # declaring variables; # MySQL statementns END // delimiter ;First, here we will create a table and add some records in the table. After that, a simple function will be created. The following is the query to create a table −mysql> create table ViewDemo −> ( −> Id int, −> Name varchar(200), −> Age int −> ); ... Read More