MySQL INSERT INTO statement
has average rating
9
out of 10.
Total 6 users rated.
Description
MySQL INSERT INTO statement is used to insert record(s) or row(s) into a table.
The insertion of records or rows in the table can be done in two ways. Insert a single row at a time, and inserting multiple rows at a time.
Syntax
INSERT INTO table_name ([column_name],[...column_name],...)
VALUES( [column_value],[..column_value]);
Arguments
| Name | Description |
|---|---|
| table_name | Name of the table. |
| column_name | Name of the column. |
| column_value | Value to be inserted. |
Example with PHP code to insert data into a mysql table
HTML code (say form.html)
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>form to insert data</title> </head> <body> <form method="post" action="insert.php"> <input type="text" name="name" /> <input type="text" name="email" /> <input type="text" name="address" /> <input type="submit" value="Submit"> </form> </body> </html>
PHP code (say insert.php)
<?php
$host="localhost"; //yout host name
$username="root"; //yout user name
$password=""; // your password
$db_name="test"; // your database name
$con=mysql_connect("$host", "$username", "$password")or die("cannot connect"); //mysql connection
mysql_select_db("$db_name")or die("can not select DB"); //select your database
$name = $_POST['name'];
$email = $_POST['email'];
$address = $_POST['address'];
$query = "INSERT INTO test (name,email,address) VALUES ('$name', '$email', '$address')";
mysql_query($query) or die('Query "' . $query . '" failed: ' . mysql_error());
// name, email and address are fields of your fields; test your table. $name, $email and $address are values collected from the form
?>
MySQL INSERT one row in a table
Description
Here we have discussed how to insert a single row into a table using MySQL INSERT INTO statement.
Example
Code
INSERT INTO newcate
VALUES ("CA006","Sports");
Explanation
The above statement will insert one row in the table 'newcate'. We have not mentioned any column name here. That is why, all of the columns will be effected.
To see the inserted record, write the following code -
SELECT * FROM newcate;
Output
MySQL INSERT values for specific columns
Description
Here we have discussed how to insert values for specific columns using MySQL INSERT INTO statement.
Example
Sample table : newpurchase
Code
INSERT INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV001","ORD006",”An advance book of Computer”);
Explanation
The above statement will insert one(1) row in the table 'newpurchase' for the columns 'invoice_no', 'ord_no', and 'book_name'.
To see the inserted row here is the code below -
SELECT invoice_no,ord_no,book_name FROM newpurchase;
Output
MySQL INSERT NULL values
Description
Here we have discussed how to insert NULL values into one or more columns using MySQL INSERT INTO statement.
Example :
Sample table : newpurchase
Code
INSERT INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV002","ORD007",NULL);
Explanation
The above statement will insert one(1) row in the table 'newpurchase'. Columns 'invoice_no', 'ord_no', and 'book_name' got populated with values where as column 'book_name' got populated with the NULL value.
To see the inserted row here is the code below -
Output
Inserting multiple rows in a single SQL query
In MySQL you can insert multiple rows in a single SQL query. Here is the syntax :
INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 );
