Found 4219 Articles for MySQLi

To delete all the records from a MySQL table, you can use the TRUNCATE statement.The syntax is as follows −TRUNCATE TABLE yourTableName;The above syntax deletes all the records from the table. To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table DeleteAllFromTable −> ( −> PersonId int, −> PersonName varchar(200) −> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table with the help of insert command.The query is as follows −mysql> insert ... Read More

You can use IFNULL() function from MySQL to return a value even if there is not result. Let us create a table. Te query to create a table.mysql> create table IfNullDemo    −> (    −> Id int,    −> Name varchar(100)    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into IfNullDemo values(1, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into IfNullDemo values(200, 'Sam'); Query OK, 1 row affected (0.21 sec) mysql> insert into IfNullDemo ... Read More

You can use IN statement to delete more than one rows from a table using id in MySQL. The syntax is as follows −delete from yourTableName where yourColumnName in(value1, value2, .....valueN);To understand the above syntax, let us create a table. The following is the query to create a table.mysql> create table DeleteManyRows    −> (    −> Id int,    −> Name varchar(200),    −> Age int    −> ); Query OK, 0 rows affected (3.35 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into DeleteManyRows values(1, 'John', 23); ... Read More

To add minutes to a datetime you can use DATE_ADD() function from MySQL. In PHP, you can use strtotime().To add 30 minutes in MySQL, the DATE_ADD() function is as follows −select date_add(yourColumnName, interval 30 minute) from yourTableName;To use the above syntax, let us create a table. The following is the query to create a table.mysql> create table Add30MinutesDemo −> ( −> YourTime datetime −> ); Query OK, 0 rows affected (0.67 sec)Insert records in the table with the help of following query −mysql> insert into Add30MinutesDemo values(now()); Query OK, 1 row ... Read More

Use DATEDIFF() function from MySQL to get the difference between two timestamps in days.The syntax is as follows −select datediff(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table DifferenceTimestamp −> ( −> IssueTime timestamp, −> DueTime timestamp −> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table with the help of insert command. We are setting dates here. The query is as follows −mysql> insert into ... Read More

Increase the varchar size of an existing column in a database with the help of CHANGE command. The syntax is as follows −alter table yourTableName change yourColumnName yourColumnName dataType;Here, we are creating a table with a single column and varchar size 200 −mysql> create table IncreaseVarcharDemo    −> (    −> StudentId varchar(200)    −> ); Query OK, 0 rows affected (0.60 sec)Now insert record in the table. The query is as follows −mysql> insert into IncreaseVarcharDemo values('John123'); Query OK, 1 row affected (0.16 sec)Displaying all records from the table with the help of the following query −mysql> select *from ... Read More

This error occurs when you try to use a reserved word as a table or column name. It can occur due to −Case 1: Whenever you use reserved word as a table name −mysql> create table insert −> ( −> Id int −> );The error is as follows −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert ( Id int )' at line 1The above error occurred because the word ‘insert’ is a keyword in MySQL.Case 2 − Whenever you ... Read More

To extract last word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, -1) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is as follows −mysql> insert into FirstWordDemo values('This is the first MySQL Query'); Query OK, 1 row affected (0.11 ... Read More

The best type for price column should be DECIMAL. The type DECIMAL stores the value precisely.For Example - DECIMAL(10, 2) can be used to store price value. It means the total digit will be 10 and two digits will be after decimal point.To understand the type DECIMAL, let us create a table.mysql> create table PriceDemo    −> (    −> ProductPrice DECIMAL(10, 2)    −> ); Query OK, 0 rows affected (0.60 sec)Now insert some records in the table in the form of price. The query to insert records is as follows −mysql> insert into PriceDemo values(12345.67); Query OK, 1 row ... Read More

You can strip last two characters with the help of SUBSTRING() and CHAR_LENGTH() methods. The syntax is as follows −select yourColumnName, SUBSTRING(yourColumnName, 1, CHAR_LENGTH(yourColumnName) - 2) AS anyVariableName from yourTableName;To understand the above syntax, let us create a table −mysql> create table LastTwoCharacters −> ( −> Words varchar(200) −> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table with the help of select statement. The query to insert records is as follows −mysql> insert into LastTwoCharacters values('Hellooo'); Query OK, 1 row affected (0.23 sec) ... Read More