A Prepared Statement is a programming technique, especially used when working with databases, to make SQL queries more secure and efficient.

1. How does a Prepared Statement work?

It consists of two steps:

1. Prepare the SQL query with placeholders
   Example in SQL:

SELECT * FROM users WHERE username = ? AND password = ?

• (Some languages use :username or other types of placeholders.)

• Bind parameters and execute
  The real values are bound later, for example:

$stmt->bind_param("ss", $username, $password);
$stmt->execute();

2. Advantages

✅ Protection against SQL injection:
User input is treated separately and safely, not directly inserted into the SQL string.

✅ Faster with repeated use:
The SQL query is parsed once by the database server and can be executed multiple times efficiently (e.g., in loops).

3. Example in PHP using MySQLi

$conn = new mysqli("localhost", "user", "pass", "database");
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email); // "s" stands for string
$email = "example@example.com";
$stmt->execute();
$result = $stmt->get_result();

In short:

A Prepared Statement separates SQL logic from user input, making it a secure (SQL Injection) and recommended practice when dealing with databases.

An Outer Join is a type of database join (commonly used in SQL) that returns records from one or both tables even if there’s no matching record in the other table.

Types of Outer Joins:

1. LEFT OUTER JOIN (or simply: LEFT JOIN):
   → Returns all records from the left table, and the matching ones from the right table.
   → If there’s no match, the result is filled with NULL values from the right table.

2. RIGHT OUTER JOIN (or: RIGHT JOIN):
   → Returns all records from the right table, and the matching ones from the left table.
   → If there’s no match, NULL is used for the left side.

3. FULL OUTER JOIN:
   → Returns all records from both tables, with NULL where no match exists on either side.

Example:

Suppose you have two tables:

• Customers

  CustomerID	Name
  1	Anna
  2	Bernd
  3	Clara

• Orders

  OrderID	CustomerID	Product
  101	2	Book
  102	4	Lamp

LEFT JOIN (Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID)

PDO stands for PHP Data Objects and it's a database abstraction layer in PHP. It provides an object-oriented interface to access different types of databases—such as MySQL, PostgreSQL, or SQLite—without needing to change much of your code.

Key Features of PDO:

✅ Consistent API
Same code style regardless of which database you use.

✅ Prepared Statements
Helps prevent SQL injection via bound parameters:

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => $userId]);

✅ Transactions Support
Great for use cases like banking systems or other operations that need rollback/commit.

✅ Exception-based Error Handling

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

✅ Easy Database Switching
Switching from MySQL to PostgreSQL? Usually only the DSN and driver need to be changed.

Example: Connecting to MySQL with PDO

$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$user = 'root';
$pass = '';

try {
    $pdo = new PDO($dsn, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Summary:

PDO is the recommended way to work with databases in modern PHP applications—especially for its security, flexibility, and ease of use.

Data Control Language (DCL) is a subset of SQL that focuses on managing access rights and permissions within a database. DCL commands are used to control who can do what in the database.

Main DCL Commands:

GRANT SELECT, INSERT ON Customers TO User123;
REVOKE INSERT ON Customers FROM User123;

Common Privileges:

• SELECT – Read data

• INSERT – Add new data

• UPDATE – Modify existing data

• DELETE – Remove data

• ALL – Grant all available privileges

Key Characteristics:

• DCL handles security and access control in the database.

• Typically used by a database administrator (DBA).

• Permissions can be granted at the table, column, or database level.

• DCL operations are often transaction-dependent, requiring a COMMIT to take effect.

Comparison with Other SQL Subsets:

• DDL (Data Definition Language) – Defines the database structure (e.g., tables)

• DML (Data Manipulation Language) – Works with the data itself (e.g., insert or update)

• TCL (Transaction Control Language) – Manages transactions (COMMIT, ROLLBACK)

• DCL (Data Control Language) – Manages permissions and user access

DQL stands for Data Query Language, and it's a subset of SQL (Structured Query Language). It is used specifically to query data from a database without modifying it.

Key Characteristics of DQL:

• Read-only: DQL is used to retrieve data, not to insert, update, or delete it.

• The primary command used is:

SELECT

Example:

SELECT name, birthdate FROM customers WHERE city = 'Berlin';

This command retrieves the names and birthdates of all customers living in Berlin — without changing any data.

DQL vs Other SQL Sub-languages:

💡 What is an Entity Manager?

An Entity Manager is a core component of ORM (Object-Relational Mapping) frameworks, especially in Java (JPA – Java Persistence API), but also in other languages like PHP (Doctrine ORM).

📦 Responsibilities of an Entity Manager:

1. Persisting:

   • Saves a new object (entity) to the database.

   • Example (Doctrine): $entityManager->persist($user);

2. Finding/Loading:

   • Retrieves an object by its ID or other criteria.

   • Example: $entityManager->find(User::class, 1);

3. Updating:

   • Tracks changes to objects and writes them to the database (usually via flush()).

4. Removing:

   • Deletes an object from the database.

   • Example: $entityManager->remove($user);

5. Managing Transactions:

   • Begins, commits, or rolls back transactions.

6. Handling Queries:

   • Executes custom queries, often using DQL (Doctrine Query Language) or JPQL.

🔁 Entity Lifecycle:

The Entity Manager tracks the state of entities:

• managed (being tracked),

• detached (no longer tracked),

• removed (marked for deletion),

• new (not yet persisted).

🛠 Example with Doctrine (PHP):

$user = new User();
$user->setName('Max Mustermann');

$entityManager->persist($user); // Mark for saving
$entityManager->flush();        // Write to DB

✅ Summary:

The Entity Manager is the central component for working with database objects — creating, reading, updating, deleting. It abstracts SQL and provides a clean, object-oriented way to interact with your data layer.

Doctrine DBAL (Database Abstraction Layer) is a PHP library that provides an abstraction layer for database access. It is part of the Doctrine project (a popular ORM for PHP), but it can be used independently of the ORM.

Purpose and Benefits of Doctrine DBAL:

Doctrine DBAL offers a unified API to interact with different databases (such as MySQL, PostgreSQL, SQLite, etc.) without writing raw SQL specific to each database system.

Key Features of Doctrine DBAL:

• Connection Management

• • Easily configure and manage connections to various database systems.

  • Supports connection pooling, transactions, and more.

• SQL Query Builder

• • Build SQL queries programmatically using an object-oriented API:

$qb = $conn->createQueryBuilder();
$qb->select('u.id', 'u.name')
   ->from('users', 'u')
   ->where('u.age > :age')
   ->setParameter('age', 18);
$stmt = $qb->executeQuery();

• Database Independence

  • The same code works with different database systems (e.g., MySQL, PostgreSQL) with minimal changes.

• Schema Management

  • Tools to create, update, and compare database schemas.

  • Useful for migrations and automation.

• Data Type Conversion

  • Automatically converts data between PHP types and database-native types.

use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection([
    'dbname' => 'test',
    'user' => 'root',
    'password' => '',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
]);

$result = $conn->fetchAllAssociative('SELECT * FROM users');

When to Use DBAL Instead of ORM:

You might choose DBAL without ORM if:

• You want full control over your SQL.

• Your project doesn't need complex object-relational mapping.

• You're working with a legacy database or custom queries.

Summary:

Doctrine DBAL is a powerful tool for clean, portable, and secure database access in PHP. It sits between raw PDO usage and a full-featured ORM like Doctrine ORM, making it ideal for developers who want abstraction and flexibility without the overhead of ORM logic.

Kirby CMS is a flexible, file-based Content Management System (CMS) designed for developers and designers who value maximum control over their projects. Created by Bastian Allgeier, it is known for its minimalist approach and high adaptability. Here are the key features of Kirby CMS:

1. File-Based System

Kirby stores content in simple text files (usually Markdown or YAML) instead of relying on a database like MySQL. This makes it ideal for small to medium-sized projects where setting up and maintaining a database is unnecessary.

2. Flexibility

Kirby doesn’t come with pre-built themes, giving developers complete freedom to create templates and layouts from scratch. It’s PHP-based, allowing you to design dynamic websites tailored to your needs.

3. Kirby Panel

The Panel is an intuitive interface for editors to manage content. It provides a clear structure and can be customized to meet the specific requirements of each project, ensuring a user-friendly experience.

4. Developer-Friendly

Kirby is particularly appealing to web developers because it:

• Has no strict conventions: You define the structure, design, and functionality of your site.
• Is API-centric: It comes with a built-in PHP and REST API for programmatically managing and delivering content.
• Has minimal overhead: It’s lightweight and avoids unnecessary features that might slow down your site.

5. Licensing Model

Kirby isn’t free. While you can test it without cost, a license is required for live, production use. This ensures high-quality, ad-free development, making it a popular choice for professional projects.

6. Use Cases

Kirby is suitable for:

• Portfolio websites
• Blogs
• Corporate sites
• Documentation
• Custom projects with low resource requirements

Conclusion

Kirby CMS is perfect for projects that demand maximum flexibility and control. It combines straightforward content management with powerful developer tools, making it a favorite among designers and developers who want to build bespoke websites from scratch.

MariaDB is a relational database management system (RDBMS) developed as an open-source alternative to MySQL. It was created in 2009 by the original MySQL developers after MySQL was acquired by Oracle. The goal was to provide a fully open, compatible version of MySQL that remains independent.

Key Features of MariaDB:

1. Open Source:

   • MariaDB is distributed under the GPL (General Public License), ensuring it is free to use, modify, and distribute.

2. MySQL Compatibility:

   • MariaDB is largely compatible with MySQL. Many applications that work with MySQL can migrate to MariaDB with little to no adjustments.
   • It uses the same command syntax, APIs, and configuration files.

3. Enhanced Features:

   • New Storage Engines: MariaDB offers additional storage engines like Aria, TokuDB, and ColumnStore.
   • Improved Performance: Optimizations for queries and indexing provide better speed and scalability.
   • Encryption: Advanced security features like table- and column-level encryption.
   • JSON and Virtual Columns: Supports modern data types for flexible application development.

4. Active Development:

   • MariaDB is actively developed by the community and the MariaDB Foundation, ensuring regular updates and new features.

Common Use Cases:

• Web Applications: For example, content management systems (CMS) like WordPress.
• Enterprise Solutions: ERP, CRM, and data warehouse applications.
• Cloud Services: Many cloud providers support MariaDB.

Differences from MySQL:

• While MySQL includes proprietary extensions under Oracle's management, MariaDB remains fully open source.
• MariaDB offers additional features, making it appealing for users who want complete control over their database.

Conclusion:

MariaDB is a powerful and flexible database solution, highly valued for its openness, security, and compatibility with MySQL. It is an excellent choice for developers and organizations looking for a reliable open-source database.

The LEMP stack is a collection of software commonly used together to host dynamic websites and web applications. The acronym "LEMP" represents the individual components of the stack:

1. Linux: The operating system that serves as the foundation for the stack. It supports the other software components.

2. Nginx (pronounced "Engine-X"): A high-performance, resource-efficient web server. Nginx is often preferred because it scales better for handling simultaneous connections compared to Apache.

3. MySQL (or MariaDB): The relational database used to store data. MySQL is commonly paired with PHP to generate dynamic content. Modern setups often use MariaDB, a fork of MySQL.

4. PHP, Python, or Perl: The scripting language used for server-side programming. PHP is particularly popular in web development for rendering database-driven dynamic content on web pages.

Why use the LEMP stack?

• Performance: Nginx offers better performance for static content and highly scalable applications compared to Apache (used in the LAMP stack).
• Flexibility: The stack is modular, and each component can be replaced with alternatives (e.g., MariaDB instead of MySQL, Python instead of PHP).
• Open Source: All components are open-source software, reducing costs and increasing flexibility.
• Popular for modern web applications: Many developers use the LEMP stack to build powerful and scalable applications.

The LEMP stack is a modern alternative to the better-known LAMP stack, which uses Apache as the web server.