PHP : MySQL Insert Data

To insert data into a MySQL database table using PHP, you can use SQL INSERT INTO queries. Below are examples using both mysqli and PDO to insert data into a users table with columns name and email .

Using mysqli :

Here's an example using mysqli to insert a new row into the users table:

// Database credentials $servername = "localhost"; $username = "your_username"; $password = "your_password"; $database = "your_database";

// Create connection $conn = new mysqli($servername, $username, $password, $database);

// Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }

// Data to be inserted $name = "John Doe"; $email = "john@example.com";

// SQL query to insert data $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }

// Close connection $conn->close();

Using PDO:

Here's the same example using PDO :

// Database credentials $servername = "localhost"; $username = "your_username"; $password = "your_password"; $database = "your_database";

try { // Create connection $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Data to be inserted $name = "John Doe"; $email = "john@example.com";

// SQL query to insert data $sql = "INSERT INTO users (name, email) VALUES (:name, :email)"; $stmt = $conn->prepare($sql); $stmt->bindParam(':name', $name); $stmt->bindParam(':email', $email);

// Execute the query $stmt->execute();

echo "New record created successfully"; } catch (PDOException $e) { echo "Error: " . $e->getMessage(); } finally { // Close connection $conn = null; }

Explanation:

Replace "your_username", "your_password", and "your_database" with your actual database credentials.
The INSERT INTO query inserts a new row into the users table with the specified name and email values.
In the PDO example, placeholders ( :name and :email ) are used for the values to be inserted. This helps prevent SQL injection attacks.
bindParam is used in PDO to bind the variables $name and $email to the placeholders.

Multiple Insertions:

You can also insert multiple rows at once using INSERT INTO with multiple value sets:

// SQL query to insert multiple rows $sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com')";

// Execute the query $conn->query($sql);

Important Notes:

Always sanitize user input and use prepared statements to prevent SQL injection attacks, especially when user input is involved.
Verify that the connection is successful before executing queries.
Ensure that the table structure matches the data being inserted, such as data types and constraints.