These mysql_* functions are deprecated and other functions are available that gives better security and functionality. Note: As an alternative, use either _assoc or _row though. mysql_fetch_assoc The function returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The associativity array tells us about the key value pair, whereas the key tells about any column name and the value tells about the row value. Here we can map the column name as key and value as row. For example. Key is ID and value is corresponding name. ... Read More

To display all table names with ENGINE as InnoDB, implement the following query mysql> SELECT TABLE_SCHEMA as DATABASENAME ,TABLE_NAME as AllTABLENAME ,ENGINE as ENGINETYPE FROM information_schema.TABLES -> WHERE ENGINE = 'innoDB' -> AND TABLE_SCHEMA NOT IN('mysql', 'information_schema', 'performance_schema'); The following is the output. +--------------+------------------------------------------------------------------+------------+ | DATABASENAME | AllTABLENAME ... Read More

We can convert JS date time to MySQL datetime with the help of toISOString() function.Let us see an example of JavaScript.Example Live Demo           Web Page Design                document.writeln(new Date().toISOString().slice(0, 19).replace('T', ' '));              Current Date is displayed above... OutputThe following is the output.2018-11-23 11:14:38 Current Date is displayed above...

We can add 1 day to the date with the help of DATE_ADD() function. Creating a table. mysql> create table Add1DayDemo -> ( -> id int, -> MyDate datetime not null -> ); Query OK, 0 rows affected (1.06 sec) Insert some records. mysql> insert into Add1DayDemo values(1, now()); Query OK, 1 row affected (0.08 sec) mysql> insert into Add1DayDemo values(2, date_add(now(), interval 5 day)); Query OK, 1 row affected (0.16 sec) To display all records. mysql> select *from Add1DayDemo; ... Read More

The following is the syntax to get the current time zone of MySQL. mysql> SELECT @@global.time_zone, @@session.time_zone; The following is the output. +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec) The above just returns “SYSTEM” because MySQL is set for system time zones. Alternately, we can get the current time zone with the help of now() function. Let us first ... Read More

To make an existing field unique in MySQL, we can use the ALTER command and set UNIQUE constraint for the field. Let us see an example. First, we will create a table. mysql> create table AddingUnique -> ( -> Id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.44 sec) Syntax to add UNIQUE to an existing field. alter table yourTableName add UNIQUE(yourColumnName); Applying the above syntax in order to add UNIQUE to column ‘name’. mysql> alter table AddingUnique add ... Read More

Whenever a table is present in a project with a single database, we can do database schema changes using schema version or migration. It aims to keep track of database schema changes or structural changes. The table creation to keep track of schema changes. mysql> create table SchemaDatabaseMethodDemo -> ( -> `WhenTime` timestamp not null default CURRENT_TIMESTAMP, -> `TheKey` varchar(200) not null, -> `Version` varchar(200), -> primary key(`TheKey`) -> )ENGINE=InnoDB; Query OK, 0 rows affected (0.45 sec) Inserting records into ... Read More

Whenever we renumber, there might be a problem. There is a need to declare a unique ID for a column. In MySQL version 5.6 InnoDB, we can reuse the auto_increment ID by including the ID column in an INSERT statement and we can give any specific value that we want. The situations are as follows − Whenever we delete the ID with the highest number Whenever we start and stop MySQL server Whenever we insert a new record Example of ID auto increment using auto_increment variable. mysql> create table UniqueAutoId -> ( ... Read More

We need to create a stored procedure to display how to pass array to MySQL stored routine. Let us first create a table for our example. Creating a table mysql> create table FindDemo -> ( -> name varchar(100) -> ); Query OK, 0 rows affected (0.46 sec) Inserting some records into the table. mysql> insert into FindDemo values('John'), ('Smith'); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 To display all records. mysql> select *from FindDemo; The following is ... Read More

The EXISTS tells if the query returned results or not while IN can be used for comparing one value with other. IN uses literal values. Note − IN is preferred in a subquery whenever a subquery result is very small. If the subquery result is very large then EXISTS is used. Since IN is a preferred choice for subquery results that are smaller, we are considering an example of IN. To create a table. mysql> create table InSubQueryDemo -> ( -> PNumber int, -> PName varchar(100) -> ... Read More