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:
mysql> 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 successfully\n";
mysql_close($conn);
}
else
{
?>
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.