Found 4219 Articles for MySQLi

To fetch rows where a field value is less than 5 chars, you need to use LENGTH() function. The syntax is as follows −SELECT *FROM yourTableName WHERE LENGTH(yourColumnName) < 5;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table fieldLessThan5Chars    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> yourZipCode varchar(10)    -> ); Query OK, 0 rows affected (0.52 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into fieldLessThan5Chars(yourZipCode) values('35801'); Query ... Read More

You can use INFORMATION_SCHEMA.COLUMNS to check if a database is empty or not. The syntax is as follows −SELECT COUNT(DISTINCT `TABLE_NAME`) AS anyAliasName FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `table_schema` = 'yourDatabaseName';The above syntax returns 0 if the database has notable otherwise it returns the number of tables. For our example, we are using the databases ‘sample’ and ‘test3’, which we created before.The first database ‘sample’ has more tables, therefore the above query will return a number of tables. The second database ‘test3’ does not have any tables, therefore the above query will return 0.Case 1 − Database sampleThe query is as follows ... Read More

You can easily add more than one column that does not exist in a query using multiple AS keywords.Let us first create a table. The query to create a table is as follows −mysql> create table ColumnDoesNotExists    -> (    -> UserId int,    -> UserName varchar(20)    -> ); 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 ColumnDoesNotExists(UserId, UserName) values(100, 'Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into ColumnDoesNotExists(UserId, UserName) values(101, 'Sam'); Query OK, 1 row affected (0.22 sec) mysql> ... Read More

Add a column that does not exist in a query, with the help of AS keyword. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ....N, yourValue AS yourColumnName, ....N' FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ColumnDoesNotExists     -> (     -> UserId int,     -> UserName varchar(20)     -> ); Query OK, 0 rows affected (0.67 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into ColumnDoesNotExists(UserId, UserName) values(100, 'Larry'); Query OK, ... Read More

You can select the first and last data row using MIN() and MAX(). The syntax is as follows −SELECT * FROM yourTableName WHERE yourColumnName = (SELECT MIN(yourColumnName) FROM yourTableName) UNION SELECT * FROM yourTableName WHERE yourColumnName = (SELECT MAX(yourColumnName) FROM yourTableName) ;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FirstAndLastDataDemo    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeName varchar(20),    -> EmployeeAge int    -> ); Query OK, 0 rows affected (0.59 sec)ExampleInsert some records in the table using ... Read More

You can select specific month with the help of MONTH() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE MONTH(yourColumnName) = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserLoginTimeInformation    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserLoginDatetime datetime    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserLoginTimeInformation(UserLoginDatetime) values(date_add(now(), interval 3 month)); Query OK, 1 row affected (0.14 sec) ... Read More

You need to use DateTime class if you want to extract the only date from datetime field. The syntax is as follows −DateTime::createFromFormat("Y-m-d H:i:s",yourDateTimeValue)->format("yourFormatSpecifier");Now you can implement the above syntax in your PHP code to extract the only date from datetime field. The PHP code is as follows −$MySQLDataBaseDateTime = "2018-02-13 13:10:15"; echo DateTime::createFromFormat("Y-m-d H:i:s",$MySQLDataBaseDateTime)->format("d/m/Y");Here is the screenshot of the PHP code −Output13/02/2018

You can add auto_increment to a column in MySQL database with the help of ALTER command.The syntax is as follows −ALTER TABLE yourTableName MODIFY yourColumnName INT NOT NULL AUTO_INCREMENT;To open PhpMyAdmin on localhost, you need to type the following on localhost and press enter −localhost/phpmyadminThe screenshot is as follows −Above, we already have a table “AutoIncrementDemo”. In that, we have a column “UserId” set as Primary key. Let’s say we need to add auto_increment to the same column.For auto_increment, check the A.I as shown above. The same is marked below as well −After that press the Save button.Let us also ... Read More

Yes, we can add a column to a table from another table. Let us first create two tables. The query to create a table is as follows −mysql> create table FirstTable    -> (    -> UserId int,    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (1.48 sec)Now create the second table. The query to create the second table is as follows −mysql> create table SecondTable    -> (    -> UserId int,    -> UserAge int    -> ); Query OK, 0 rows affected (1.57 sec)Now, add column Age to the first table. Firstly, ... Read More

First, you need to open the CMD with the help of shortcut key Windows+R key.After typing cmd, press the OK button. On pressing, you will get a command prompt. The screenshot is as follows −After that, you need to reach the /bin directory. Follow the below instructions. If you are a Windows user, then use the below query to reach the /bin directory.The query is as follows −mysql> select @@datadir;The following is the output displaying the path −+---------------------------------------------+ | @@datadir ... Read More