MySQLi Articles - Page 30 of 341

For this, use INSERT ON DUPLICATE KEY UPDATE command. Let us first create a table −mysql> create table DemoTable1891    (    FirstName varchar(20),    UNIQUE KEY(FirstName)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1891 values('Chris') on duplicate key update  FirstName='Robert'; Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1891 values('David') on duplicate key update  FirstName='Robert'; Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1891 values('Chris') on duplicate key update  FirstName='Robert'; Query OK, 2 rows affected (0.00 sec)Display all records from the table ... Read More

For this, use IF(). Let us first see the current date −mysql> select curdate(); +------------+ | curdate()  | +------------+ | 2019-12-10 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1890    (    DueDate timestamp    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1890 values('2017-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 values('2021-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 values('2018-04-24'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 ... Read More

For this, you can use MD5(). Let us first create a table −mysql> create table DemoTable1887    (    Password text,    HashPassword text    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1887(Password) values('John@9089'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1887(Password) values('90987_Carol'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1887(Password) values('656464_David_4343'); Query OK, 1 row affected (0.00 sec)Display some records in the table using insert command −mysql> select * from  DemoTable1887;This will produce the following output −+-------------------+--------------+ | Password     ... Read More

To get whether a column is a primary key, use COLUMN_NAME and COLUMN_KEY='PRI'. With that, the entire syntax is as follows −select column_name, case when column_key= 'PRI' then 'yourMessage1' else ''yourMessage2' end as anyAliasName from information_schema.columns where table_schema =database() and `table_name` = yourTableName order by `table_name`, ordinal_position;To understand the above syntax, let us create a table −mysql> create table DemoTable1886    (    Id int NOT NULL,    FirstName varchar(20),    LastName varchar(20),    Age int,    DateOfBirth datetime,    Education varchar(40),    PRIMARY KEY(Id)    ); Query OK, 0 rows affected (0.00 sec)Here is the query to get whether ... Read More

For this, you can use GROUP BY HAVING clause along with IN(). Let us first create a table −mysql> create table DemoTable1885    (    FirstName varchar(20),    Subject varchar(50)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1885 values('John', 'MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('John', 'MongoDB'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('Carol', 'MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('David', 'Java'); Query OK, 1 row affected (0.00 sec)Display some ... Read More

Let us first create a table −mysql> create table DemoTable1884    (    Marks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1884 values(55); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1884 values(97); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1884 values(79); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1884 values(87); Query OK, 1 row affected (0.00 sec)Display some records in the table using insert command −mysql> select * from DemoTable1884;This will produce the following output −+-------+ | ... Read More

Yes, we can do that. The syntaxes are as follows −Syntax1: select * from yourTableName1, yourTableName2; Syntax2: select * from yourTableName1 cross join yourTableName2;Both the above syntaxes give the same result.Let us first create a table −mysql> create table DemoTable1882    (    Id int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1882 values(10); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1882 values(20); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1882 values(30); Query OK, 1 row affected (0.00 sec)Display all records ... Read More

To use comparison operator for numeric string, use the substring() method. Let us first create a table −mysql> create table DemoTable1881    (    UserId int,    UserEducationGap varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1881 values(101, '5-9'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1881 values(102, '2-4'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1881 values(103, '4-8'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1881 values(104, '7-12'); Query OK, 1 row affected (0.00 sec)Display all records ... Read More

For this, use INSERT INTO SELECT statement. Let us first create a table −mysql> create table DemoTable1879    (    Id int,    Name varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1879 values(101, 'Chris Brown'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1879 values(102, 'David Miller'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1879 values(103, 'Adam Smith'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1879;This will produce the ... Read More

Let us first create a table −mysql> create table DemoTable1877 ( DueDate datetime ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1877 values('2019-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1877 values('2019-12-05'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1877 values('2019-12-07'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1877 values('2019-12-09'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1877;This will ... Read More