Php

MySql

️ What is MySQL?

  • MySQL is an open-source Relational Database Management System (RDBMS).

  • It stores data in tables made of rows and columns.

  • Used for managing and organizing data efficiently.

  • Works great with web applications (PHP, Node.js, Python, etc.).

Basic MySQL Concepts

Term Description
Database A collection of related tables
Table Collection of rows (records) and columns (fields)
Row Single record (entry) in a table
Column Attribute or field of data
Primary Key Unique identifier for rows

Common MySQL Commands

1. Creating a Database

sql
CREATE DATABASE my_database;

2. Using a Database

sql
USE my_database;

3. Creating a Table

sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. Inserting Data

sql
INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');

5. Selecting Data

sql
SELECT * FROM users;

6. Updating Data

sql
UPDATE users SET email = 'alice_new@example.com' WHERE username = 'Alice';

7. Deleting Data

sql
DELETE FROM users WHERE username = 'Alice';

Connecting PHP with MySQL

Using MySQLi (Improved)

php
$mysqli = new mysqli("localhost", "root", "password", "my_database");

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

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "User: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}
} else {
echo "No results found.";
}

$mysqli->close();

Using PDO (PHP Data Objects) — Recommended

php
try {
$pdo = new PDO("mysql:host=localhost;dbname=my_database", "root", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->query("SELECT * FROM users");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "User: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}

Security Tips

  • Use prepared statements to prevent SQL injection.

  • Never expose database credentials.

  • Use strong passwords for your database users.

Related Posts

Leave a Reply

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