Found 4219 Articles for MySQLi

For this, let us first create a new table in MySQLmysql> create table useProcedure - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(20), - > LastName varchar(20) - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into useProcedure(FirstName, LastName) values('Adam', 'Smith'); Query OK, 1 row affected (0.27 sec)The following is your stored procedure to set two variables in a stored procedure with single select ... Read More

If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL. The syntax is as follows SELECT LAST_INSERT_ID(); To understand the above syntax, let us create a table. The query to create a table is as follows mysql> create table LastInsertedRow    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),      - > UserAge int    - > ); Query OK, 0 rows affected (0.56 sec) Insert some records in the table using insert command. The ... Read More

To change a MySQL column datatype from text to timestamp, you need to use ALTER command.The syntax is as followsALTER TABLE yourTableName MODIFY COLUMN yourColumnName TIMESTAMP;To understand the above syntax, let us create a table.The query to create a table is as followsmysql> create table textTotimestampdemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Source text    - > ); Query OK, 0 rows affected (0.44 sec)Here is the description of table using DESC command.The syntax is as followsDESC yourTableName;The query is as followsmysql> desc textTotimestampdemo;The following is the output+--------+---------+------+-----+---------+----------------+ | ... Read More

If you want the database location i.e. where it is created in MySQL, you can use system variable @@datadir.The syntax is as followsSELECT @@datadir;The following is the querymysql> select @@datadir;Here is the output. The above query returns the location+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the above directory in your system. The screenshot of the ... Read More

To increment all the rows of a particular ID column by 1, you need to use UPDATE command and update the table. The syntax of the query is as follows. We have also used ORDER BY hereUPDATE yourTableName SET yourIdColumnName=yourIdColumnName+1 ORDER BY yourIdColumnName DESC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table IdColumnadd1Demo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY - > ); Query OK, 0 rows affected (0.58 sec)Insert some records in the ... Read More

To avoid the incorrect datetime value error, you can use the STR_TO_DATE() method.As we know the datetime format is YYYY-MM-DD and if you won’t insert in the same format, the error would get generated.Let us see what actually lead to this error. For this, let us create a new table. The query to create a table is as followsmysql> create table CorrectDatetimeDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > ArrivalTime datetime   - > ); Query OK, 0 rows affected (0.63 sec)The occurs when we try to include a ... Read More

You need to use ALTER command to add a created at column to an already created table in MySQL.Let us first create a table. The query to create a table is as follows. Here is your table without the “created at” columnmysql> create table formDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Email varchar(128), - > PhoneNumber varchar(15), - > Country varchar(30), - > Platform varchar(40) - > ); Query OK, 0 ... Read More

Yes, you can concatenate strings with || in MySQL with the help of sql_mode. Set the sql_mode to PIPES_AS_CONCAT.The syntax is as followsset sql_mode=PIPES_AS_CONCAT;The following is the syntax to concat with the help of ||.SELECT ‘yourValue' || yourColumName AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table PipeConcatDemo    - > (    - > Name varchar(20)    - > ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into PipeConcatDemo values('Larry'); Query ... Read More

To know how many rows are in a ySQL database table, you need to use aggregate function COUNT(*).The syntax is as followsSELECT COUNT(*) FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table CountRowsDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20)    - > ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CountRowsDemo(Name) values(NULL); Query OK, 1 row affected (0.15 sec) mysql> ... Read More

Yes, for this MySQL comes with a NOT IN.The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName NOT IN(1, 2, 7);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table User_informations    - > (    - > UserId int,    - > UserName varchar(20)    - > ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into User_informations values(12, 'Maxwell'); Query OK, 1 row affected (0.17 sec) mysql> insert into User_informations values(7, 'David'); Query OK, 1 ... Read More