Insert MySQL data
Insert MySQL data
MySQL table using INSERT INTO SQL statements to insert the data.
You can mysql> command prompt window to insert data into the data table, or to insert data via PHP script.
grammar
Following is a table insert data to MySQL data common INSERT INTO SQL syntax:
INSERT INTO table_name ( field1, field2,/en.fieldN )
VALUES
( value1, value2,/en.valueN );
If the data is a character, you must use single or double quotes, such as: "value".
Command prompt window to insert data
Below we will use the SQL INSERT INTO statement to insert data MySQL data tables w3big_tbl
Examples
The following example we will want w3big_tbl sheet into three data:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> INSERT INTO w3big_tbl
->(w3big_title, w3big_author, submission_date)
->VALUES
->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO w3big_tbl
->(w3big_title, w3big_author, submission_date)
->VALUES
->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO w3big_tbl
->(w3big_title, w3big_author, submission_date)
->VALUES
->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>
Note: Use the arrow (->) is not part of the SQL statement, it only represents a new line, if a SQL statement too long, we can create a new line to write SQL statements by the Enter key to end the command SQL statement It is the semicolon (;).
In the example above, we do not provide w3big_id data, because the field we've set it to AUTO_INCREMENT (automatic increase) in property when creating the table. Therefore, the field is automatically incremented without the need for us to set up. Example NOW () is a MySQL function that returns the date and time.
Use PHP script to insert data
You can use PHP's mysql_query () function to execute the SQL INSERT INTO command to insert data.
This function has two parameters, in the implementation of successful returns TRUE, otherwise returns FALSE.
grammar
bool mysql_query( sql, connection );
| parameter | description |
|---|---|
| sql | Required. SQL query to send provisions. Note: The query string should not end with a semicolon. |
| connection | Optional. Provisions of SQL connection identifier. If not specified, the use of an open connection. |
Examples
The following example procedure for receiving user input data three fields, and insert data in the table:
<html>
<head>
<meta charset="utf-8">
<title>向 MySQL 数据库添加数据</title>
</head>
<body>
<?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() )
{
$w3big_title = addslashes ($_POST['w3big_title']);
$w3big_author = addslashes ($_POST['w3big_author']);
}
else
{
$w3big_title = $_POST['w3big_title'];
$w3big_author = $_POST['w3big_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO w3big_tbl ".
"(w3big_title,w3big_author, submission_date) ".
"VALUES ".
"('$w3big_title','$w3big_author','$submission_date')";
mysql_select_db('w3big');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="600" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="250">Tutorial Title</td>
<td>
<input name="w3big_title" type="text" id="w3big_title">
</td>
</tr>
<tr>
<td width="250">Tutorial Author</td>
<td>
<input name="w3big_author" type="text" id="w3big_author">
</td>
</tr>
<tr>
<td width="250">Submission Date [ yyyy-mm-dd ]</td>
<td>
<input name="submission_date" type="text" id="submission_date">
</td>
</tr>
<tr>
<td width="250"> </td>
<td> </td>
</tr>
<tr>
<td width="250"> </td>
<td>
<input name="add" type="submit" id="add" value="Add Tutorial">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
When we received the data submitted by the user, for the security of the data we need to use get_magic_quotes_gpc () function to determine whether to escape special characters is turned on. If this option is off (not turned on), returns 0, then we must call this function addslashes increase escaped string.
Righteousness.You can also add other methods to check data, such as mailbox format verification, phone number verification, whether the integer verification.
