Performing basic database operation (DML) (Insert, Delete, Update, Select)
Performing basic database operation (DML) (Insert, Delete, Update, Select)
To interact with a MySQL database using PHP, you'll need to use SQL queries for insertion, deletion, updating, and selection of data. Here are examples of how to perform these operations:
### 1. Insert Data into MySQL Database:
```php
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Sample data for insertion
$name = "John Doe";
$email = "john.doe@example.com";
// SQL query for insertion
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === TRUE) {
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
```
### 2. Delete Data from MySQL Database:
```php
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// ID of the record to be deleted
$id = 5;
// SQL query for deletion
$sql = "DELETE FROM users WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
```
### 3. Update Data in MySQL Database:
```php
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Sample data for update
$id = 3;
$newName = "Updated Name";
// SQL query for update
$sql = "UPDATE users SET name='$newName' WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>
```
### 4. Select Data from MySQL Database:
```php
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query for selection
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . ", Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
}
} else {
echo "No records found";
}
// Close the connection
$conn->close();
?>
```
Replace `"your_username"`, `"your_password"`, and `"your_database_name"` with your MySQL database credentials and database name. Additionally, adjust the table and column names according to your database structure. Always use prepared statements to prevent SQL injection for production code.
Comments
Post a Comment