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> if (! GET_MAGIC_QUOTES_GPC ()) $sql = "INSERT into Tutorials_tbl". |
|
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; 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 mysql_select_db (' tutorials '); |
|
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 mysql_select_db (' tutorials '); |
|
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 mysql_select_db (' tutorials '); |
|
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 mysql_select_db (' tutorials '); |
|
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; 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 |
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
