Php

MySql Interaction from PHP

MySQL Interaction from PHP

1️⃣ Connecting to MySQL

Using MySQLi (Procedural)

php
$conn = mysqli_connect("localhost", "username", "password", "database");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

Using MySQLi (Object-Oriented)

php
$conn = new mysqli("localhost", "username", "password", "database");

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

Using PDO (PHP Data Objects) — Recommended

php
try {
$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}

2️⃣ Running Queries

Select Data

MySQLi Procedural

php
$sql = "SELECT id, name FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row["id"] . " Name: " . $row["name"] . "<br>";
}
} else {
echo "No results found";
}

MySQLi Object-Oriented

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

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

PDO

php
$stmt = $pdo->query("SELECT id, name FROM users");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "ID: " . $row["id"] . " Name: " . $row["name"] . "<br>";
}

3️⃣ Inserting Data

MySQLi Prepared Statement (Object-Oriented)

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

$name = "Alice";
$email = "alice@example.com";
$stmt->execute();

echo "New record inserted with ID: " . $stmt->insert_id;

$stmt->close();

PDO Prepared Statement

php
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");

$stmt->execute([
':name' => 'Alice',
':email' => 'alice@example.com'
]);

echo "New record inserted with ID: " . $pdo->lastInsertId();

4️⃣ Updating Data

MySQLi Prepared Statement

php
$stmt = $conn->prepare("UPDATE users SET email=? WHERE id=?");
$stmt->bind_param("si", $email, $id);

$email = "newalice@example.com";
$id = 1;
$stmt->execute();

echo "Record updated";

$stmt->close();

PDO Prepared Statement

php
$stmt = $pdo->prepare("UPDATE users SET email=:email WHERE id=:id");

$stmt->execute([
':email' => 'newalice@example.com',
':id' => 1
]);

echo "Record updated";

5️⃣ Deleting Data

MySQLi Prepared Statement

php
$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->bind_param("i", $id);

$id = 1;
$stmt->execute();

echo "Record deleted";

$stmt->close();

PDO Prepared Statement

php
$stmt = $pdo->prepare("DELETE FROM users WHERE id=:id");
$stmt->execute([':id' => 1]);

echo "Record deleted";

6️⃣ Closing Connection

  • MySQLi

php
$conn->close();

  • PDO

php
$pdo = null;

Why Use Prepared Statements?

  • Prevents SQL Injection attacks by separating query logic and data.

  • Safer and more reliable for user input.

Related Posts

MySql

️ What is MySQL? MySQL is an open-source Relational Database Management System (RDBMS). It stores…

Leave a Reply

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