Found 4219 Articles for MySQLi

If your cast does not work, then you can use yourColumnName*1 with ORDER BY clause.Using yourColumnName*1. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourColumnName*1 DESC;You can also use CAST() operator. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY CAST(yourColumnName as DECIMAL(8, 2)) DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharColumnAsFloatDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Amount varchar(20), -> PRIMARY KEY(Id) ... Read More

To find rows and update with new value where column value ends with specific substring you need to use LIKE operator.The syntax is as follows:UPDATE yourTableName SET yourColumnName=’yourValue’ WHERE yourColumnName LIKE ‘%.yourString’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RowEndsWithSpecificString -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FileName varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.50 sec)Now you can insert some records in the table using ... Read More

To fetch rows where first character is not alphanumeric, you can use the following regular expression.Case 1 − If you want those rows that starts from a digit, you can use the following syntax −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '^[0-9]';Case 2 − If you want those rows that start from an alphanumeric, use the following syntax −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '^[^0-9A-Za-z]' ;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table getRowsFirstNotAlphanumeric -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserPassword varchar(20), -> PRIMARY ... Read More

If you want to get only digits using REGEXP, use the following regular expression( ^[0-9]*$) in where clause.Case 1 − If you want only those rows which have exactly 10 digits and all must be only digit, use the below regular expression.SELECT *FROM yourTableName WHERE yourColumnName REGEXP '^[0-9]{10}$';Case 2 − If you want only those rows with the digit either 1 or more, the following is the syntax −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '^[0-9]*$';The above syntax will give only those rows that do not have any any characters.To understand the above syntax, let us create a table. The query ... Read More

Order by date and set the empty dates in the last with the help of ORDER BY clause and IS NULL property. The syntax is as follows:SELECT *FROM yourTableName ORDER BY (yourDateColumnName IS NULL), yourDateColumnName DESC;In the above syntax, we will sort the NULL first after that date. To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table DateColumnWithNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> LoginDateTime datetime, -> PRIMARY KEY(Id) -> ... Read More

The function TIME_TO_SEC() can be used in MySQL. If you want to convert datetime to seconds use the strtotime() from PHP. The MySQL syntax is as follows:SELECT TIME_TO_SEC(ABS(timediff(‘yourDateTimeValue’, now())));Now you can convert PHP datetime to seconds with the help of strtotime().First, you need to install XAMPP server to run your PHP program.After installing XAMPP successfully in C drive, here is the location wherein you need to include the PHP file. The snapshot is as follows:Note: Here, I have changed port of Apache to 8086 because default port was held by another program. This is done to begin running PHP program.Therefore, ... Read More

You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.The syntax is as follows to remove special characters from a database field.UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’yourSpecialCharacters’, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RemoveSpecialCharacterDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> PRIMARY Key(Id)    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using ... Read More

To find the nth highest value of a column, you need to use ORDER BY DESC with LIMIT clause. If you want the second highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1, 1;If you want the fourth highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 3, 1;If you want the first highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1;As discussed in the above syntax, you need to change only in LIMIT clause. To understand ... Read More

This error can occur if you try to set data higher than the allowed limit. As an example, you cannot store a string in a column of type bit because varchar or string takes size higher than bit data type.You need to use the following syntax for bit type column:anyBitColumnName= b ‘1’ OR anyBitColumnName= b ‘0’To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table IncasesensitiveDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> PRIMARY KEY(Id)    -> ); Query OK, ... Read More

As you know the DECIMAL() method takes two parameter. The first parameter tells about the total number of digits and second parameter tells about number of digits after decimal point. Therefore, if you use DECIMAL(10, 10) that means you can use only 10 fractional digit.For Example: Store 0.9999999999 with DECIMAL(20, 10).To understand what we discussed above, let us create a table. The query to create a table is as follows:mysql> create table Decimal_Demo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Price DECIMAL(20, 10), -> PRIMARY KEY(Id) ... Read More