PHP | Inserting into MySQL database (original) (raw)
Last Updated : 26 Aug, 2024
**Inserting data into a MySQL database using **PHP is a crucial operation for many **web applications. This process allows developers to dynamically manage and store data, whether it be user inputs, content management, or other data-driven tasks.
In this article, We will learn about **How to Inserting into MySQL Database Using PHP in detail
Inserting into MySQL Database Using PHP
- Inserting data into a **MySQL database using PHP is a fundamental operation for any web application. By using **PHP, we can dynamically add records to our **database, allowing our application to store user inputs, manage content or handle various **data-driven tasks.
- The process involves establishing a connection to the MySQL database, preparing the **SQL **INSERT statement, executing the query and handling any potential errors to ensure **data integrity and application **stability.
**Creating Table using MySQLi Object-oriented Procedure:
Suppose we have to insert a new record into a MySQL database table named **mytable**using **PHP. The goal is to add the first name, last name, and age of an individual to the table.
PHP `
connect_error); } $sql = "INSERT INTO mytable (first_name, last_name, age) VALUES('ram', 'singh', '25') "; if ($mysqli->query($sql) == = true) { echo "Records inserted successfully."; } else { echo "ERROR: Could not able to execute $sql. " .$mysqli->error; } // Close connection $mysqli->close(); ? > ` **Output** Parse error: syntax error, unexpected '=' in /home/guest/sandbox/Solution.php on line 4 ****Output:**  ****Explanation:** This PHP script connects to a MySQL database and inserts a new row into the `mytable` table with the values `'ram'` for `first_name`, `'singh'` for `last_name`, and `'25'` for `age`. If the insertion is successful, a success message is displayed; otherwise, an error message is shown. ## ****Creating Table using MySQLi Procedural Procedure:** Suppose we have to insert a new record into a MySQL database table named `mytable` using PHP and MySQLi. The script should connect to the database and add the first name, last name, and age of an individual. PHP ` ` ****Output:**  ****Explanation:** This PHP script connects to a MySQL database using MySQLi and attempts to insert a new row into the `mytable` table. The values being inserted are `'ram'` for `first_name`, `'singh'` for `last_name`, and `'25'` for `age`. If the operation is successful, it displays a success message; otherwise, it provides an error message explaining what went wrong. Finally, the script closes the database connection. ## ****Table using MySQLi PDO Procedure:** Suppose we have to insert a new record into a MySQL database table using PHP and PDO. The script should handle potential connection and execution errors while ensuring that the database connection is properly closed after the operation. PHP ` setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { die("ERROR: Could not connect. ".$e->getMessage()); } try { $sql = "INSERT INTO mytable (first_name, last_name, age) VALUES('ram', 'singh', '25') "; pdo−>exec(pdo->exec(pdo−>exec(sql); echo "Records inserted successfully."; } catch (PDOException $e) { die("ERROR: Could not able to execute $sql. " .$e->getMessage()); } // Close connection unset($pdo); ? > ` ****Output:**  ****The values of the table can be viewed in MySql database as –**  ****Explanation:** This PHP script connects to a MySQL database using PDO and inserts a new row into the `mytable` table. The script starts by attempting to establish a database connection. If the connection is successful, it proceeds to execute an `INSERT` statement to add a new record with the first name `'ram'`, last name `'singh'`, and age `'25'`. The script includes error handling to catch and display any exceptions that occur during the connection or query execution. Finally, the connection is closed using `unset($pdo)` to release the resources. ## Inserting Multiple Rows into a Table One can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma. ### ****Creating table using MySQLi Object-oriented Procedure:** PHP ` connect_error); } $sql = "INSERT INTO mytable (first_name, last_name, age) VALUES('raj', 'sharma', '15'), ('kapil', 'verma', '42'), ('monty', 'singh', '29'), ('arjun', 'patel', '32') "; if ($mysqli->query($sql) == = true) { echo "Records inserted successfully."; } else { echo "ERROR: Could not able to execute $sql. " .$mysqli->error; } $mysqli->close(); ? > ` ****Output:**  ### ****Creating Table using MySQLi Procedural Procedure:** PHP ` ` ****Output :**  ### ****Creating Table using MySQLi PDO Procedure:** PHP ` setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { die("ERROR: Could not connect. ".$e->getMessage()); } try { $sql = "INSERT INTO mytable (first_name, last_name, age) VALUES('raj', 'sharma', '15'), ('kapil', 'verma', '42'), ('monty', 'singh', '29'), ('arjun', 'patel', '32') "; pdo−>exec(pdo->exec(pdo−>exec(sql); echo "Records inserted successfully."; } catch (PDOException $e) { die("ERROR: Could not able to execute $sql. " .$e->getMessage()); } unset($pdo); ? > ` ****Output:**  ****The values of the table can be viewed in MySql database as:**  ## Conclusion Inserting data into a MySQL database using PHP can be done using various methods, including MySQLi and PDO. Each approach offers different ways to handle database connections and query execution, allowing developers to choose the method that best suits their needs.