YouTip

The INSERT INTO statement is used to insert data into MySQL tables. You can insert data into a MySQL table via the mysql> command prompt window or through a PHP script. ### Syntax Here is the general **INSERT INTO** SQL syntax for inserting data into a MySQL data table: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); **Parameter Description:** * `table_name` is the name of the table where you want to insert data. * `column1`, `column2`, `column3`, ... are the column names in the table. * `value1`, `value2`, `value3`, ... are the specific values to be inserted. If the data is character type, it must be enclosed in single quotes ' or double quotes ", for example: 'value1', "value1". A simple example, inserting a row of data into a table named users: INSERT INTO users (username, email, birthdate, is_active) VALUES ('test', 'test@.com', '1990-01-01', true); * `username`: Username, string type. * `email`: Email address, string type. * `birthdate`: User's birthdate, date type. * `is_active`: Whether activated, boolean type. If you want to insert data into all columns, you can omit the column names: INSERT INTO users VALUES (NULL,'test', 'test@.com', '1990-01-01', true); Here, NULL is a placeholder for an auto-increment column, indicating that the system will generate a unique value for the **id** column. If you want to insert multiple rows of data, you can specify multiple sets of values in the VALUES clause: INSERT INTO users (username, email, birthdate, is_active) VALUES ('test1', 'test1@.com', '1985-07-10', true), ('test2', 'test2@.com', '1988-11-25', false), ('test3', 'test3@.com', '1993-05-03', true); The above code will insert three rows of data into the users table. * * * ## Inserting Data via Command Prompt Below, we will use the INSERT INTO statement to insert data into the MySQL data table tutorial_tbl. ### Example In the following example, we will insert three pieces of data into the tutorial_tbl table: ## Example root@host# mysql -u root -p password; Enter password: ******* mysql>USE ; DATABASE changed mysql>INSERT INTO tutorial_tbl ->(tutorial_title, tutorial_author, submission_date) ->VALUES ->("Learn PHP","", NOW()); Query OK,1 ROWS affected,1 warnings (0.01 sec) mysql>INSERT INTO tutorial_tbl ->(tutorial_title, tutorial_author, submission_date) ->VALUES ->("Learn MySQL","", NOW()); Query OK,1 ROWS affected,1 warnings (0.01 sec) mysql>INSERT INTO tutorial_tbl ->(tutorial_title, tutorial_author, submission_date) ->VALUES ->("JAVA Tutorial",".COM",'2016-05-06'); Query OK,1 ROWS affected (0.00 sec) mysql> **Note:** The arrow marker -> is not part of the SQL statement; it merely indicates a new line. If an SQL statement is too long, we can press the Enter key to create a new line to write the SQL statement. The command terminator for an SQL statement is a semicolon ;. In the above example, we did not provide data for **tutorial_id** because we set this field to **AUTO_INCREMENT** (auto-increment) when creating the table. Therefore, this field will automatically increment without us needing to set it. In the example, **NOW()** is a MySQL function that returns the current date and time. Next, we can view the data table data with the following statement: ## Read Data Table: select * from tutorial_tbl; Output: !(#) * * * ## Using PHP Script to Insert Data You can use PHP's mysqli_query() function to execute the **INSERT INTO** command to insert data. This function takes two parameters and returns TRUE on success, otherwise FALSE. ### Syntax mysqli_query(connection,query,resultmode); | Parameter | Description | | --- | --- | | _connection_ | Required. Specifies the MySQL connection to use. | | _query_ | Required. Specifies the query string. | | _resultmode_ | Optional. A constant. Can be one of the following values: * MYSQLI_USE_RESULT (Use this if you need to retrieve large amounts of data) * MYSQLI_STORE_RESULT (default) | ### Example In the following example, the program receives three field values from user input and inserts them into the data table: ## Add Data <?php$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn){die('Connection failed: ' . mysqli_error($conn)); }echo'Connection successful
'; mysqli_query($conn , "set names utf8"); $tutorial_title = 'Learn Python'; $tutorial_author = '.COM'; $submission_date = '2016-03-06'; $sql = "INSERT INTO tutorial_tbl ". "(tutorial_title,tutorial_author, submission_date) ". "VALUES ". "('$tutorial_title','$tutorial_author','$submission_date')"; mysqli_select_db($conn, ''); $retval = mysqli_query($conn, $sql); if(! $retval){die('Unable to insert data: ' . mysqli_error($conn)); }echo"Data insertion successfuln"; mysqli_close($conn); ?> For inserting data containing Chinese characters, you need to add the statement mysqli_query($conn , "set names utf8");. Next, we can view the data table data with the following statement: ## Read Data Table: select * from tutorial_tbl; Output: !(#)