PHP MySQL: Insert Data Into a Table

PHP MySQL: Insert Data Into a Table

PHP MySQL: Insert Data Into a Table

Inserting data into a MySQL database using PHP involves executing an SQL INSERT INTO statement. This allows you to add new records to a specified table.


Syntax for SQL INSERT

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
  • table_name: The name of the table where data will be inserted.
  • column1, column2, ...: The columns in which the values will be inserted.
  • value1, value2, ...: The corresponding values to insert.

Code Example: Insert Data

1. Establish a Database Connection

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "example_db"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } ?>

2. Execute an SQL INSERT INTO Query

Insert a new record into the users table:

<?php $sql = "INSERT INTO users (name, email, age) VALUES ('John Doe', 'john.doe@example.com', 30)"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } ?>

Using Prepared Statements

Prepared statements are more secure and protect against SQL injection, especially when inserting user inputs.

<?php $stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)"); $stmt->bind_param("ssi", $name, $email, $age); // Set parameters and execute $name = "Jane Smith"; $email = "jane.smith@example.com"; $age = 25; if ($stmt->execute()) { echo "New record created successfully"; } else { echo "Error: " . $stmt->error; } // Close statement and connection $stmt->close(); $conn->close(); ?>

Insert Multiple Records

You can insert multiple records in a single query by separating each set of values with a comma:

<?php $sql = "INSERT INTO users (name, email, age) VALUES ('Alice Brown', 'alice.brown@example.com', 22), ('Bob Green', 'bob.green@example.com', 29)"; if ($conn->query($sql) === TRUE) { echo "Multiple records inserted successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } ?>

Fetch the Last Inserted ID

To retrieve the ID of the last inserted record (for tables with an AUTO_INCREMENT column):

<?php $sql = "INSERT INTO users (name, email, age) VALUES ('Emily White', 'emily.white@example.com', 35)"; if ($conn->query($sql) === TRUE) { echo "New record created successfully. Last inserted ID is: " . $conn->insert_id; } else { echo "Error: " . $sql . "<br>" . $conn->error; } ?>

Complete Example

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "example_db"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Insert query $sql = "INSERT INTO users (name, email, age) VALUES ('David Black', 'david.black@example.com', 40)"; if ($conn->query($sql) === TRUE) { echo "New record created successfully. Last inserted ID is: " . $conn->insert_id; } else { echo "Error: " . $sql . "<br>" . $conn->error; } // Close connection $conn->close(); ?>

Key Notes

  1. Escape User Input: Use prepared statements or mysqli_real_escape_string to prevent SQL injection.
  2. Handle Errors: Always handle errors to debug issues effectively.
  3. Check for Required Fields: Ensure all required columns have values to avoid SQL errors.
  4. Use AUTO_INCREMENT: Use an AUTO_INCREMENT column for unique IDs if needed.
  5. Backup Data: Regularly back up your database to avoid data loss during updates.

Let me know if you need more examples or advanced usage!

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close