MySQL Data Insert Query DELETE statement detailed

Source: Internet
Author: User
Tags configuration settings numeric php language learn php php script

MySQL Data insertion

Inserting data into the MySQL table requires that you use the SQL INSERT INTO command. You can insert data into the MySQL table using the mysql> prompt or use any script, such as PHP.

Grammar:
The following is a generic SQL syntax insert INTO command to insert data into the MySQL table:

The code is as follows Copy Code
INSERT into table_name (field1, field2,... fieldn)
VALUES
(value1, value2,... Valuen);

The string data type to insert, all values require double or single quotes, such as:-"value".

To insert data from a command prompt:
This inserts the data into the MySQL table using the SQL INSERT INTO command tutorials_tbl

Instance:
The following example creates 3 entries to the TUTORIALS_TBL table:

The code is as follows Copy Code
root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
Mysql> INSERT into Tutorials_tbl
-> (Tutorial_title, Tutorial_author, Submission_date)
->values
-> ("Learn PHP", "John Poul", now ());
Query OK, 1 row affected (0.01 sec)
Mysql> INSERT into Tutorials_tbl
-> (Tutorial_title, Tutorial_author, Submission_date)
->values
-> ("Learn MySQL", "Abdul S", now ());
Query OK, 1 row affected (0.01 sec)
Mysql> INSERT into Tutorials_tbl
-> (Tutorial_title, Tutorial_author, Submission_date)
->values
-> ("JAVA Tutorial", "Sanjay", ' 2007-05-06 ');
Query OK, 1 row affected (0.01 sec)
Mysql>

NOTE: All arrow symbols (->) are not part of the SQL command they represent a new row, they will automatically create a MySQL prompt, press ENTER at the same time there is no semicolon at the end of each line command.

In the above example, we did not provide tutorial_id because when you create a table, you define a auto_increment option for this field. So MySQL is responsible for automatically inserting these IDs. Here now () is a MySQL function that returns the current date and time.

To insert data using the PHP language:
You can use the same SQL INSERT INTO command PHP function mysql_query () to insert data into the MySQL table.

Example:
This example will take the user from three parameters and insert them into the MySQL table:

The code is as follows Copy Code

<title>add New record in MySQL database-by/mysql</title>
<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 ())
{
$tutorial _title = addslashes ($_post[' tutorial_title '));
$tutorial _author = addslashes ($_post[' Tutorial_author '));
}
Else
{
$tutorial _title = $_post[' Tutorial_title '];
$tutorial _author = $_post[' Tutorial_author '];
}
$submission _date = $_post[' submission_date '];

$sql = "INSERT into Tutorials_tbl".
"(Tutorial_title,tutorial_author, Submission_date)".
"VALUES".
"(' $tutorial _title ', ' $tutorial _author ', ' $submission _date ')";
mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
Die (' could not enter data: '. mysql_error ());
}
echo "entered data Successfullyn";
Mysql_close ($conn);
}
Else
{
?>
<form method= "POST" action= "<?php $_php_self?>" >
<table width= "border=" 0 "cellspacing=" 1 "cellpadding=" 2 ">
<tr>
&LT;TD width= ">tutorial" title</td>
<td>
<input name= "Tutorial_title" type= "text" id= "Tutorial_title" >
</td>
</tr>
<tr>
&LT;TD width= ">tutorial" author</td>
<td>
<input name= "Tutorial_author" type= "text" id= "Tutorial_author" >
</td>
</tr>
<tr>
&LT;TD width= ">submission" Date [Yyyy-mm-dd]</td>
<td>
<input name= "Submission_date" type= "text" id= "Submission_date" >
</td>
</tr>
<tr>
&LT;TD width= "> </td>"
<td> </td>
</tr>
<tr>
&LT;TD width= "> </td>"
<td>
<input name= "Add" type= "Submit" id= "Add" value= "Add Tutorial" >
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>

Data insertion Its best practice is to use function get_magic_quotes_gpc () to check current configuration settings or references to magic functions. If the function returns False, the function addslashes () is used before the slash quotation mark is added.


MySQL data query


The SQL Select command is used to fetch data from the MySQL database. You can use this command at the mysql> prompt or using scripts such as PHP.

Grammar:
The following is the syntax of the generic SQL SELECT command to extract data from the MySQL table:

The code is as follows Copy Code
SELECT field1, Field2,... fieldn table_name1, table_name2 ...
[WHERE Clause]
[OFFSET M] [LIMIT N]

You can use one or more comma-delimited tables, including various use WHERE clause conditions. But the select command for the WHERE clause is an optional part.

You can read one or more fields in a single select command.

The field where the asterisk (*) can be specified. In this case, select returns all the fields

You can specify any condition by using the WHERE clause.

You can specify an offset to begin returning records with select. By default, the offset is 0

You can limit the number of uses of the Limit property returned.

To get data from a command prompt:
This will use the SQL SELECT command to get the data from the MySQL table tutorials_tbl

Example:
The following example returns all the records TUTORIALS_TBL tables:

The code is as follows Copy Code

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
Mysql> SELECT * from TUTORIALS_TBL
+-------------+----------------+-----------------+-----------------+
| tutorial_id | Tutorial_title | Tutorial_author | Submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-21 |
| 2 | Learn MySQL | Abdul S | 2007-05-21 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-21 |
+-------------+----------------+-----------------+-----------------+
3 rows in Set (0.01 sec)

Mysql>

To get data using a PHP script:
You can use the same SQL SELECT command to enter the PHP function mysql_query (). After this function is used to execute the SQL command, another PHP function, mysql_fetch_array (), can be used to get all the selected data. This function returns rows as an associative array/numeric array or both. If there are no more rows this function returns FALSE.

The following is a simple example of getting the record tutorials_tbl table.

Example:
Try the following example to show all the records of the Tutorials_tbl table.

The code is as follows Copy Code

<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' Rootpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
Die (' Could not connect: '. Mysql_error ());
}
$sql = ' SELECT tutorial_id, Tutorial_title,
Tutorial_author, Submission_date
From Tutorials_tbl ';

mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
Die (' could not get data: '. mysql_error ());
}
while ($row = Mysql_fetch_array ($retval, MYSQL_ASSOC))
{
Echo ' Tutorial ID: {$row [' tutorial_id ']} <br>.
"Title: {$row [' Tutorial_title ']} <br>".
"Author: {$row [' Tutorial_author ']} <br>".
' Submission Date: {$row [' submission_date ']} <br>.
"--------------------------------<br>";
}
echo "fetched data Successfullyn";
Mysql_close ($conn);
?>

The contents of these rows are assigned to the variable $row, the values in the row, and then printed.

Note: Be sure to remember when inserting a value into a string array directly with curly braces.

In the example above it is constantly mysql_assoc as the second parameter of the PHP function mysql_fetch_array () so that it returns the behavior of an associative array. An associative array that can use their names instead of using an indexed access field.

PHP provides another name called the MYSQL_FETCH_ASSOC () function that also returns the row as an associative array.

Instance:
Try the following example to show all the records from the Tutorial_tbl table, using the MYSQL_FETCH_ASSOC () function.

The code is as follows Copy Code

<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' Rootpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
Die (' Could not connect: '. Mysql_error ());
}
$sql = ' SELECT tutorial_id, Tutorial_title,
Tutorial_author, Submission_date
From Tutorials_tbl ';

mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
Die (' could not get data: '. mysql_error ());
}
while ($row = Mysql_fetch_assoc ($retval))
{
Echo ' Tutorial ID: {$row [' tutorial_id ']} <br>.
"Title: {$row [' Tutorial_title ']} <br>".
"Author: {$row [' Tutorial_author ']} <br>".
' Submission Date: {$row [' submission_date ']} <br>.
"--------------------------------<br>";
}
echo "fetched data Successfullyn";
Mysql_close ($conn);
?>

You can also use constant mysql_num as the PHP function for the second argument mysql_fetch_array (). This causes the function to return an array of numeric indices.

Instance:
Try the following example to display all the records in the table using the Mysql_num parameter tutorials_tbl.

The code is as follows Copy Code

<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' Rootpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
Die (' Could not connect: '. Mysql_error ());
}
$sql = ' SELECT tutorial_id, Tutorial_title,
Tutorial_author, Submission_date
From Tutorials_tbl ';

mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
Die (' could not get data: '. mysql_error ());
}
while ($row = Mysql_fetch_array ($retval, Mysql_num))
{
Echo ' Tutorial ID: {$row [0]} <br>.
"Title: {$row [1]} <br>".
"Author: {$row [2]} <br>".
' Submission Date: {$row [3]} <br> '.
"--------------------------------<br>";
}
echo "fetched data Successfullyn";
Mysql_close ($conn);
?>

All of the above three examples will produce the same results.

Free up Memory:
It is a good practice to release cursor memory in each SELECT statement. This can be done by using the PHP function Mysql_free_result (). The following example shows how it is used.

Instance:
Try the following example

The code is as follows Copy Code

<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' Rootpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
Die (' Could not connect: '. Mysql_error ());
}
$sql = ' SELECT tutorial_id, Tutorial_title,
Tutorial_author, Submission_date
From Tutorials_tbl ';

mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
Die (' could not get data: '. mysql_error ());
}
while ($row = Mysql_fetch_array ($retval, Mysql_num))
{
Echo ' Tutorial ID: {$row [0]} <br>.
"Title: {$row [1]} <br>".
"Author: {$row [2]} <br>".
' Submission Date: {$row [3]} <br> '.
"--------------------------------<br>";
}
Mysql_free_result ($retval);
echo "fetched data Successfullyn";
Mysql_close ($conn);
?>

While getting the data, you can write complex SQL as long as you like. Program will remain the same as above


MySQL Data deletion

If you want to delete a record from any MySQL table, you can use the SQL command to delete from. You can use this command at the mysql> prompt, or other scripts, such as PHP.

Grammar:
The following is the general SQL syntax of the delete command to delete data from a MySQL table:

The code is as follows Copy Code
DELETE from table_name [WHERE Clause]

If not specified, all records from the given MySQL table will be removed from the WHERE clause.

You can specify conditions by using the WHERE clause.

You can delete records in a table at once.

The WHERE clause is useful when you want to delete rows from the selected table.

To delete data from a command prompt:
This uses the WHERE clause SQL Delete command to delete the selected data MySQL table tutorials_tbl

Example:
The following example deletes a record of tutorial_id 3 in a tutorial_tbl table.

The code is as follows Copy Code

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
Mysql> DELETE from Tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

Mysql>

To delete data using a PHP script:
You can use the SQL Delete command to request mysql_query () with or without a WHERE clause PHP function. This feature will perform similar to the SQL commands executed at the mysql> prompt.

Example:
Try the following example, a tutorial_id=3 record in the TUTORIAL_TB table is deleted.

  code is as follows copy code

<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' roo Tpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
  die (' could not connect: '. Mysql_error ());
}
$sql = ' DELETE from tutorials_tbl
        WHERE tutorial_id=3 ';
//by/ MySQL
mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
  die ( ' Could not delete data: '. Mysql_error ());
}
Echo "Deleted data Successfullyn";
Mysql_close ($conn);

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.