MySQL inserts data

MySQL inserts data

The Insert INTO SQL statement is used in the MySQL table to insert data.

You can insert data into the data table through the mysql?command prompt window, or you can insert data through a PHP script.

Grammar

The following is the INSERT INTO SQL syntax common to inserting data into the MySQL data table:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

If the data is character type, you must use single or double quotes, such as :value.

Insert the data through the command prompt window

Below we will use the SQL INSERT INTO statement to insert data into the MySQL w3cschool_tbl data table

Instance

In the following example, we w3cschool_tbl data to the table:

root@host# mysql -u root -p password;
Enter password:*
mysql> use W3CSCHOOL;
Database changed
mysql> INSERT INTO w3cschool_tbl 
     ->(w3cschool_title, w3cschool_author, submission_date)
     ->VALUES
     ->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO w3cschool_tbl
     ->(w3cschool_title, w3cschool_author, submission_date)
     ->VALUES
     ->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO w3cschool_tbl
     ->(w3cschool_title, w3cschool_author, submission_date)
     ->VALUES
     ->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>

Note: Using an arrow tag (--gt;) is not part of an SQL statement, it simply represents a new line, and if an SQL statement is too long, we can write the SQL statement by creating a new line by entering the key, and the command end character of the SQL statement is a half sign (; ） 。

In the above example, we didn't provide w3cschool_id data because we set it to the AUTO_INCREMENT (auto-add) property when we created the table. T herefore, the field will automatically increment without us having to set it. N OW() in the instance is a MySQL function that returns a date and time.

Use phP scripts to insert data

You can use PHP'mysql_query() function to execute the SQL INSERT INTO command to insert data.

The function has two arguments that return TRUE if executed successfully, otherwise FALSE is returned.

Grammar

bool mysql_query( sql, connection );

Instance

In the following example, the program receives three field data entered by the user and inserts it into the data table:

<?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() )
{
   $w3cschool_title = addslashes ($_POST['w3cschool_title']);
   $w3cschool_author = addslashes ($_POST['w3cschool_author']);
}
else
{
   $w3cschool_title = $_POST['w3cschool_title'];
   $w3cschool_author = $_POST['w3cschool_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO w3cschool_tbl ".
       "(w3cschool_title,w3cschool_author, submission_date) ".
       "VALUES ".
       "('$w3cschool_title','$w3cschool_author','$submission_date')";
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
}
else
{
?>

Tutorial Title

Tutorial Author

Submission Date [ yyyy-mm-dd ]

<?php } ?>

When we receive data submitted by the user, for the security of the data we need to use the get_magic_quotes_gpc() function to determine whether the escape of a particular character is turned on. If this option is off (not turned on) and returns 0, then we must call add escape to the string by calling added adslashes.

You can also add other ways to check your data, such as mailbox format verification, phone number verification, integer verification, and so on.