PHP and Databases

PHP is a server-side scripting language commonly used for web development, and it often interacts with databases to store and retrieve data. The most popular type of database used with PHP is MySQL, but PHP can also work with other databases like PostgreSQL, SQLite, and more.

What is database in PHP?
MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.

Database Connection:

Use the mysqli or PDO extension to connect to a database.

Example using mysqli:

$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

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

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

Executing SQL Queries

Use the mysqli_query function to execute SQL queries.
Example :

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);


Fetching Data:
Use functions like mysqli_fetch_assoc, mysqli_fetch_array, or mysqli_fetch_object to retrieve data from query results.

Example:  

while ($row = mysqli_fetch_assoc($result)) {
    echo "Name: " . $row["name"] . "<br>";
}

Inserting Data:

Use the INSERT INTO SQL statement to add data to a database.

Example:

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
mysqli_query($conn, $sql);

Updating Data:

Use the UPDATE SQL statement to modify existing data.
Example:

Deleting Data:

Use the DELETE FROM SQL statement to remove data from a database.

Example:

$sql = "DELETE FROM users WHERE id=1";
mysqli_query($conn, $sql);

Prepared Statements:

Use prepared statements to prevent SQL injection.
Example with mysqli:

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

$name = "John Doe";
$email = "john@example.com";

$stmt->execute();

Closing Connection:

Always close the database connection when done to free up resources.

Example:

mysqli_close($conn);