Found 4219 Articles for MySQLi

To select data where a field has min value, you can use aggregate function min(). The syntax is as follows.SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(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 MinValueDemo -> ( -> ProductId int, -> ProductName varchar(100), -> ProductPrice int -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into MinValueDemo values(1, 'product-1', 4500); Query OK, 1 row affected (0.14 sec) mysql> insert into MinValueDemo values(2, ... Read More

You can use select case statement for this. The syntax is as follows.select yourColumnName1, yourColumnName2, ...N, case when yourColumnName=1 then 'true' else 'false' 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 selectReturnDemo -> ( -> Id int, -> Name varchar(100), -> isGreaterthan18 tinyint(1) -> ); Query OK, 0 rows affected (0.62 sec)Now you can insert some records in the table using insert command. The query is as follows.mysql> insert into selectReturnDemo values(1, 'Carol', 0); Query OK, 1 row affected (0.23 sec) mysql> ... Read More

This works in MySQL version 8.0.12. The syntax is as follows.create table yourTableName ( yourColumnName enum(‘Value1’,Value2’,Value3’,......N) default Value1’ or Value2 or Value3,..N );Set the enum type in MySQL with the following query.mysql> create table EnumInMySQL -> ( -> WebCRUD enum('CREATE','READ','UPDATE','DELETE') -> default 'CREATE' -> ); Query OK, 0 rows affected (0.60 sec)The syntax of enum in SQL Server.create table yourTableName ( yourColumnName varchar(100) CHECK(yourColumnName IN (‘Value1’,Value2’,Value3’,......N)) DEFAULT ‘Value1’ or Value2’ or Value3’,......N );Now the query is as follows for enum in SQL Server.

You can use tinyint(1) or bool or boolean. All are synonym. If you use bool or boolean datatype, then it nternally change into tinyint(1).In PHP, the value 0 represents false and 1 represents true. Any other number except 0 is also true.Let us check the internal representation of bool or boolean using a table. The query to create a table is as follows.mysql> create table AddBoolDemo -> ( -> isToggle bool -> ); Query OK, 0 rows affected (1.24 sec)To check the DDL of the table, the following is the query.SHOW CREATE TABLE yourTableName;Let us check the representation of bool ... Read More

To cut only the first character, use the substr() function with UPDATE command. The syntax is as follows.UPDATE yourTableName set yourColumnName=substr(yourColumnName, 2);To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table CutStringDemo -> ( -> Value varchar(100) -> ); Query OK, 0 rows affected (0.66 sec)Now you can insert some records in the table using insert command. The query is as follows.mysql> insert into CutStringDemo values(', 12, 3456'); Query OK, 1 row affected (0.14 sec) mysql> insert into CutStringDemo values(', 23, 9867'); Query OK, 1 row affected ... Read More

To get the first 40 characters from a text field, use LEFT() function from MySQL. The syntax is as follows −SELECT LEFT(yourColumnName, 40) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table retrieveFirst40Characters −> ( −> AllWords text −> ); Query OK, 0 rows affected (0.59 sec)Now you can insert some record in the table with the help of insert command. The query is as follows −mysql> insert into retrieveFirst40Characters values('This is a query demo ... Read More

Firstly, get a list of MySQL user accounts, using MySQL.user table. You can use select user column from MySQL.user table to get a list of MySQL user accounts.The query is as follows −mysql> select user from MySQL.user;The following output displays all the users −+------------------+ | user             | +------------------+ | Manish           | | mysql.infoschema | | mysql.session    | | mysql.sys        | | root             | | am               | +------------------+ 6 rows in set (0.06 ... Read More

Yes, you can achieve this in the following two ways.First Approach At the time of creation of a table.Second Approach At the time of writing query.The syntax is as follows.CREATE TABLE yourTableName ( yourDateTimeColumnName datetime default current_timestamp );You can use alter command.The syntax is as follows.ALTER TABLE yourTableName ADD yourColumnName datetime DEFAULT CURRENT_TIMESTAMP;Implement both the syntaxes now.The first approach is as follows.mysql> create table CurrentTimeStampDemo -> ( -> CreationDate datetime default current_timestamp -> ); Query OK, 0 rows affected (0.61 sec)If you do not pass any parameter for the column ‘CreationDate’, MySQL by default stores the current timestamp.Insert record in ... Read More

You can achieve this with the help of update command. To understand the method, let us create a table. The query to create a table is as follows −mysql> create table AddDateTimeWithOther −> ( −> Id int, −> IssueDate datetime, −> DueDate datetime −> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table with insert statement. The query is as follows −mysql> insert into AddDateTimeWithOther values(100, now(), date_add(now(), interval -3 year)); Query OK, 1 row affected (0.13 sec) mysql> insert ... Read More

To return the field names of a table, you can use desc command. The syntax is as follows −desc yourTableName;Or you can use column_name field from information_schema.columns table. The syntax is as follows −select column_name from information_schema.columns where table_name = ’yourTableName’;To understand both the syntax, let’s say we have a table ‘ExtractCommentDemo1’.Using the first syntax −mysql> desc ExtractCommentDemo1;The following is the output displaying the fields −+----------+--------------+------+-----+---------+-------+ | Field    | Type         | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | UserId   | int(11)      | YES  |     | NULL ... Read More