MySQL INSERT INTO Query: How to add Row in Table (Example)

Basic syntax

Let’s look at the basic syntax of the INSERT INTO MySQL command:

INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);

HERE

• INSERT INTO `table_name` is the command that tells MySQL server to add a new row into a table named `table_name.`
• (column_1,column_2,…) specifies the columns to be updated in the new MySQL row
• VALUES (value_1,value_2,…) specifies the values to be added into the new row

When supplying the data values to be inserted into the new table, the following should be considered:

• String data types – all the string values should be enclosed in single quotes.
• Numeric data types – all numeric values should be supplied directly without enclosing them in single or double quotes.
• Date data types – enclose date values in single quotes in the format ‘YYYY-MM-DD’.

Inserting Multiple Rows at Once

A single INSERT statement can carry several rows. Separate each set of values with a comma. One statement is faster than many, because the index updates once.

INSERT INTO `categories`(`category_name`,`remarks`) VALUES
('Documentary', 'Factual films'),
('Musical', 'Song driven films');

MySQL INSERT INTO Query Example

Suppose that we have the following list of new library members that need to be added to the database.

Let’s INSERT the data one row at a time, starting with Leonard Hofstadter. We will treat the contact number as a numeric data type and not enclose the number in single quotes.

INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ('Leonard Hofstadter','Male','Woodcrest',0845738767);

Executing the above script drops the 0 from Leonard’s contact number, because the value is treated as numeric and a leading zero is not significant.

To avoid such problems, the value must be enclosed in single quotes as shown below.

INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ('Sheldon Cooper','Male','Woodcrest', '0976736763');

⚠️ Tip: Store phone numbers, postal codes and similar identifiers in a text column such as VARCHAR. Numeric columns cannot keep a leading zero.

Changing the Column Order

Changing the order of the columns has no effect on the INSERT query in MySQL, as long as the correct values have been mapped to the correct columns. The query below demonstrates this point.

INSERT INTO `members` (`contact_number`,`gender`,`full_names`,`physical_address`) VALUES ('0938867763','Male','Rajesh Koothrappali','Woodcrest');

The above queries skipped the date of birth column. By default, MySQL inserts NULL values into columns that are omitted from the INSERT query.

Let’s now insert the record for Leslie, which has the date of birth supplied. The date value must be enclosed in single quotes using the format ‘YYYY-MM-DD’.

INSERT INTO `members` (`full_names`,`date_of_birth`,`gender`,`physical_address`,`contact_number`) VALUES ('Leslie Winkle','1984-02-14','Male','Woodcrest', '0987636553');

Omitting the Column List

All of the above queries named the columns and mapped them to values. If we supply values for ALL the columns in the table, the column list can be omitted from the MySQL insert query.

INSERT INTO `members` VALUES (9,'Howard Wolowitz','Male','1981-08-24',
'SouthPark','P.O. Box 4563', '0987786553', 'lwolowitz@email.me');

Let’s now use the SELECT statement to view all the rows in the members table.

SELECT * FROM `members`;

Notice that Leonard Hofstadter’s contact number has dropped its leading zero, exactly as predicted. The quoted numbers retain it.

Inserting into a Table from another Table

The INSERT command can also be used to insert data into a table from another table. The basic syntax is as shown below.

INSERT INTO table_1 SELECT * FROM table_2;

We will now create a dummy table for movie categories, called categories_archive. The script below creates the table.

CREATE TABLE `categories_archive` ( `category_id` int(11) AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`))

Execute the above script to create the table. Let’s now insert all the rows from the categories table into the categories archive table.

INSERT INTO `categories_archive` SELECT * FROM `categories`;

The table structures have to be the same for that script to work. A more robust script maps the column names in the destination table to the ones in the table containing the data, as shown below.

INSERT INTO `categories_archive`(category_id,category_name,remarks) SELECT category_id,category_name,remarks FROM `categories`;

Executing the SELECT query below gives the following results.

SELECT * FROM `categories_archive`;

PHP Example: Insert into MySQL Table

The mysqli_query function is used to execute SQL queries from PHP. It runs Insert, Select, Update and Delete statements alike, and has the following syntax.

mysqli_query($db_handle,$query);

HERE,

• “mysqli_query(…)” is the function that executes the SQL queries.
• “$query” is the SQL query to be executed.
• “$db_handle” is the server connection link returned by mysqli_connect.

Example

$servername = "localhost";
$username = "alex";
$password = "yPXuPT";
$dbname = "afmznf";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO addkeyworddata(link, keyword) VALUES ('https://www.guru99.com/','1000')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully" . '<br>';
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

FAQs