MySQLi Articles - Page 173 of 388

To fetch a single date from a column, use “LIMIT 1. To order it, use ORDER BY clause. Let us first create a table −mysql> create table DemoTable -> ( -> DueDate varchar(100) -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10-06-2019'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable values('01-12-2016'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable values('31-01-2018'); Query OK, 1 row affected (0.58 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis ... Read More

Let us first create a table −mysql> create table DemoTable    -> (    -> Words text    -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Ever'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Forever'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Good'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Never'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+---------+ | Words ... Read More

We will first order by DESC and then fetch the value associated with maximum id −select *from yourTableName order by yourColumnName DESC LIMIT 1, 1;Let us first create a table −mysql> create table DemoTable    -> (    -> Alldata int    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(303); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(560); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.13 sec) mysql> insert ... Read More

Let us first create a table −mysql> create table DemoTable    -> (    -> Name varchar(100),    -> Code varchar(100)    -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', '0001'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Robert', '0002'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Robert', '0003'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris', '0001'); Query OK, 1 row affected (0.12 sec)Display all records from the table using ... Read More

To delete all rows older than 30 days, you need to use the DELETE with INTERVAL. Use < now() i.e. less than operator to get all the records before the current date.Let us first create a table −mysql> create table DemoTable    -> (    -> UserMessage text,    -> UserMessageSentDate date    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Hi', '2019-06-01'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Hello', '2019-07-02'); Query OK, 1 row affected (0.14 sec) mysql> insert ... Read More

You can use having clause. Let us first create a table −mysql> create table DemoTable    -> (    -> AdmissionDate varchar(100)    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10/12/2017'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('01/11/2018'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('31/01/2019'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('09/06/2019'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('19/04/2019'); Query OK, 1 ... Read More

For this, you can use DATEDIFF(). Let us first create a table −mysql> create table DemoTable -> ( -> ShippingDate datetime -> ); Query OK, 0 rows affected (0.90 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-07-01'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('2019-07-02'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-07-03'); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('2019-07-04'); Query OK, 1 row affected (0.15 sec)Display all records ... Read More

Yes, you can use json_extract(). Let us first create a table −mysql> create table DemoTable    -> (    -> Data json    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('{"Name": "John", "CountryName": "US"}'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable values('{"Name": "Chris", "CountryName": "UK"}'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+----------------------------------------+ | Data                               ... Read More

To get the highest value, use the GREATEST() method. Let us first create a table −mysql> create table DemoTable    -> (    -> Value1 int,    -> Value2 int,    -> Value3 int    -> ); Query OK, 0 rows affected (1.29 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 600, 400); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+--------+--------+--------+ | Value1 | Value2 | Value3 | +--------+--------+--------+ | 100 | 600 | 400    | ... Read More

For this, use IF(). Let us first create a table −mysql> create table DemoTable    -> (    -> PlayerName varchar(100),    -> PlayerScore int,    -> PlayerStatus varchar(100)    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 88, 'BAD'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Chris', 78, 'BAD'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Robert', 90, 'BAD'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 80, 'BAD'); Query ... Read More