In this chapter, we will learn how to insert data in a table.

Inserting data into a table requires the INSERT command. The general syntax of the command is INSERT followed by the table name, fields, and values.

Review its general syntax given below −

INSERT INTO tablename (field,field2,...) VALUES (value, value2,...);

The statement requires the use of single or double quotes for string values. Other options for the statement include “INSERT…SET” statements, “INSERT…SELECT” statements, and several other options.

Note − The VALUES() function that appears within the statement, only applies to INSERT statements and returns NULL if used elsewhere.

Two options exist for performing the operation: use the command line or use a PHP script.

The Command Prompt

At the prompt, there are many ways to perform a select operation. A standard statement is given below −

belowmysql>
INSERT INTO products_tbl (ID_number, Nomenclature) VALUES (12345,“Orbitron 4000”);
mysql> SHOW COLUMNS FROM products_tbl;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ID_number   | int(5)      |      |     |         |       |
| Nomenclature| char(13)    |      |     |         |       |
+-------------+-------------+------+-----+---------+-------+

You can insert multiple rows −

INSERT INTO products VALUES (1, “first row”), (2, “second row”);

You can also employ the SET clause −

INSERT INTO products SELECT * FROM inventory WHERE status = ''available

PHP Insertion Script

Employ the same “INSERT INTO…” statement within a PHP function to perform the operation. You will use the mysql_query() function once again.

Review the example given below −

<?php
   if(isset($_POST[''add''])) {
      $dbhost = ''localhost:3036
      $dbuser = ''root
      $dbpass = ''rootpassword
      $conn = mysql_connect($dbhost, $dbuser, $dbpass);

      if(! $conn ) {
         die(''Could not connect: '' . mysql_error());
      }

      if(! get_magic_quotes_gpc() ) {
         $product_name = addslashes ($_POST[''product_name'']);
         $product_manufacturer = addslashes ($_POST[''product_name'']);
      } else {
         $product_name = $_POST[''product_name''];
         $product_manufacturer = $_POST[''product_manufacturer''];
      }
      $ship_date = $_POST[''ship_date''];
      $sql = "INSERT INTO products_tbl ".
         "(product_name,product_manufacturer, ship_date) ".
         "VALUES"."(''$product_name'',''$product_manufacturer'',''$ship_date'')";

      mysql_select_db(''PRODUCTS'');
      $retval = mysql_query( $sql, $conn );
      
      if(! $retval ) {
         die(''Could not enter data: '' . mysql_error());
      }

      echo "Entered data successfullyn";
      mysql_close($conn);
   }
?>

On successful data insertion, you will see the following output −

mysql> Entered data successfully

You will also collaborate validation statements with insert statements such as checking to ensure correct data entry. MariaDB includes a number of options for this purpose, some of which are automatic.