MySQLi Articles - Page 234 of 341

You need to use CASE statement to conditional select between dates to find the minimum and maximum price. Wrap up the CASE statement with aggregate function MIN() and MAX(). The syntax is as follows:SELECT MIN(CASE WHEN CURDATE() BETWEEN yourStartDateColumnName AND yourEndDateColumnName THEN yourLowPriceColumnName ELSE yourHighPriceColumnName END) AS anyVariableName, MAX(CASE WHEN CURDATE() BETWEEN yourStartDateColumnName AND yourEndDateColumnName THEN yourLowPriceColumnName ELSE yourHighPriceColumnName END) 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 ConditionalSelect    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> StartDate datetime, ... Read More

We are using MySQL version 8.0.12. Let us first check the MySQL version:mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.00 sec)To deal with Boolean in MySQL, you can use BOOL or BOOLEAN or TINYINT(1). If you use BOOL or BOOLEAN, then MySQL internally converts it into TINYINT(1).In BOOL or BOOLEAN data type, if you use true literal then MySQL represents it as 1 and false literal as 0 like in PHP/ C/ C++ language.To proof that MySQL convert the BOOL or BOOLEAN to TINYINT(1), let us create a table with ... Read More

The local variable has the scope for only a set of statements or block of statement. Whenever a set of statements or block of statement has completed then local variable goes out of scope.For ExampleLocal variable can be used in stored procedure, function etc. It is used with the DECLARE keyword.The syntax is as follows to local variables.DECLARE yourVariableName dataType;The global variables or system variables has the scope across connections until server restart. It is set using GLOBAL keyword. The syntax is as follows −SET GLOBAL max_connections=value; OR SET @@global.max_connections=value;If you know port number then you can use system variable ... Read More

You do not need to add NOT NULL to primary key field because it gets NOT NULL automatically. Primary key is combination of both NOT NULL and Unique Key.Here is the demo of primary key field. Let us first create a table. The query to create a table is as follows:mysql> create table NotNullAddDemo    -> (    -> Id int AUTO_INCREMENT,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.81 sec)In the above table, you do not need to add NOT NULL to primary key field because MySQL internally converts it into NOT NULL. To ... Read More

You can change year of dates from 2020 to 2011 using SUBDATE() with INTERVAL of 9 year because there is a difference of 9 years between 2020 to 2011.The syntax is as follows:UPDATE yourTableName SET yourDateColumnName=SUBDATE(yourDateColumnName, INTERVAL 9 YEAR);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ChangeYearFrom2020To2011    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ExpiryDate date,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query to insert ... Read More

You can convert varchar to double using CAST() function. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ......N, CAST(yourColumnName AS DECIMAL(TotalDigit, DigitAfterDecimalPoint)) anyVariableName FROM yourtableName ORDER BY anyVariableName DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharToDouble    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> Amount varchar(10) ,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into VarcharToDouble(Name, Amount) values('John', ... Read More

You can use CASE from MySQL to change value from 1 to Y. Let us first create a table. The query to create a table is as follows:mysql> create table changeValuefrom1toY    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValidAddress tinyint(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(0); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More

You can use IF() from MySQL to change value from 1 to Y. The syntax is as follows:SELECT IF(yourColumnName, ’Y’, 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 changeValuefrom1toY    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValidAddress tinyint(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 ... Read More

You need to use GROUP BY command with aggregate function count(*) from MySQL to achieve this. The syntax is as follows:SELECT yourColumnName, COUNT(*) AS anyVariableNameFROM 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 selectDistinct_CountDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> AppearanceId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into selectDistinct_CountDemo(Name, AppearanceId) values('Larry', ... Read More

If your table name or column name are any reserved words then you need to use quotes around table name and column name in a MySQL query. You need to use backticks around table name and column name. The syntax is as follows:SELECT *FROM `table` where `where`=condition;Here is the query to create a table without quotes with reserved words. You will get an error since they are predefined reserved words. The error is as follows:mysql> create table table    -> (    -> where int    -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check ... Read More