Php

MySql Table Interaction

MySQL Table Interaction: CRUD Operations

1️⃣ Create: Creating a Table

sql
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2),
hired_date DATE
);

2️⃣ Insert: Adding Data to a Table

sql
INSERT INTO employees (name, position, salary, hired_date)
VALUES ('John Doe', 'Developer', 75000, '2023-01-15');

You can insert multiple rows at once:

sql
INSERT INTO employees (name, position, salary, hired_date)
VALUES
('Jane Smith', 'Designer', 65000, '2022-11-20'),
('Mike Johnson', 'Manager', 90000, '2020-05-10');

3️⃣ Read: Querying Data from a Table

  • Select all rows and columns:

sql
SELECT * FROM employees;

  • Select specific columns:

sql
SELECT name, position FROM employees;

  • Filter rows with WHERE:

sql
SELECT * FROM employees WHERE salary > 70000;

  • Sort results:

sql
SELECT * FROM employees ORDER BY hired_date DESC;

  • Limit the number of rows:

sql
SELECT * FROM employees LIMIT 5;

4️⃣ Update: Modifying Data

sql
UPDATE employees
SET salary = 80000
WHERE name = 'John Doe';

5️⃣ Delete: Removing Data

sql
DELETE FROM employees WHERE name = 'Mike Johnson';

6️⃣ Alter Table: Modifying Table Structure

  • Add a new column:

sql
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

  • Drop a column:

sql
ALTER TABLE employees DROP COLUMN department;

7️⃣ Drop Table: Delete Entire Table

sql
DROP TABLE employees;

Bonus: Working with PHP to Interact with Tables

Here’s a quick PHP example using MySQLi to fetch and display data:

php
<?php
$conn = new mysqli("localhost", "root", "password", "my_database");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, name, position FROM employees";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Position: " . $row["position"] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *