MySQLi Articles - Page 268 of 341

To prepend a string to a column value in MySQL, we can use the function CONCAT. The CONCAT function can be used with UPDATE statement. Creating a table. mysql> create table PrependStringOnCOlumnName   -> (   -> Id int,   -> Name varchar(200)   -> ); Query OK, 0 rows affected (1.35 sec) Inserting some records. mysql> insert into PrependStringOnCOlumnName values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into PrependStringOnCOlumnName values(2, 'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into PrependStringOnCOlumnName values(3, 'Johnson'); Query OK, 1 row affected (0.45 sec) ... Read More

To get table names using SELECT statement, use “information_schema.tables”. Let us see an example, wherein we have a database that contains 3 tables. The syntax to get all table names with the help of SELECT statement. SELECT Table_name as TablesName from information_schema.tables where table_schema = 'yourDatabaseName'; Using database “test”, and applying the above syntax to get the table names using SELECT mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema.tables where table_schema = 'test'; Output with the name of the three tables. +--------------------+ | TablesName ... Read More

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

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...

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

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

We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format. Let us see an example. Creating a table. mysql> create table IPV4AddressDemo -> ( -> `IPV4Address` INT UNSIGNED -> ); Query OK, 0 rows affected (0.52 sec) Inserting IP address into the table, with INET_ATON. mysql> insert into IPV4AddressDemo values(INET_ATON("120.0.0.1")); Query OK, 1 row affected (0.17 sec) To display all records. mysql> select *from IPV4AddressDemo; The following ... Read More

In MySQL, the term cardinality refers to the uniqueness of data values that can be put into columns. It is a kind of property which influences the ability to search, cluster and sort data. Cardinality can be of two types which are as follows − Low Cardinality − All values for a column must be same. High Cardinality − All values for a column must be unique. The concept of high cardinality is used if we put a constraint on a column in order to restrict duplicate values. High Cardinality The following is an example of High Cardinality, ... Read More