Connecting PHP to MySQL Dataabse

<?php
$serverName = "localhost";
$userName = "root";
$password = "root";
$databaseName = "mysql";

// Create connection
$connection = new mysqli($serverName, $userName, $password, $databaseName);

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

// Close connection
$connection->close();
?>

Creating a Database

<?php
// Create connection
$connection = new mysqli("localhost", "root", "root");

// Check connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
echo "Connected successfully<br>";

$databaseName = "php";

// Check if database exists
$checkQuery = "SHOW DATABASES LIKE '$databaseName'";
$result = $connection->query($checkQuery);
if ($result->num_rows > 0) {
    // Drop existing database
    $connection->query("DROP DATABASE $databaseName");
    echo "Database dropped successfully<br>";
}

// Create database
if ($connection->query("CREATE DATABASE $databaseName")) {
    echo "Database created successfully<br>";
} else {
    echo "Error creating database: " . $connection->error;
}

$connection->close();
?>

Creating a Table

<?php
$connection = new mysqli("localhost", "root", "root", "php");

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
echo "Connected successfully<br>";

$createTableQuery = "CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

if ($connection->query($createTableQuery)) {
    echo "Table created successfully<br>";
} else {
    echo $connection->error;
}

$connection->close();
?>

Inserting Data

<?php
$connection = new mysqli("localhost", "root", "root", "php");

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
echo "Connected successfully<br>";

$insertQuery = "INSERT INTO users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";

if ($connection->query($insertQuery)) {
    echo "Data inserted successfully<br>";
} else {
    echo $connection->error;
}

$connection->close();
?>

Insetring Multiple Records

<?php
$connection = new mysqli("localhost", "root", "root", "php");

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
echo "Connected successfully<br>";

$multiInsertQuery = "INSERT INTO users (firstname, lastname, email) VALUES ('Paul', 'Bei', 'paul@example.com');";
$multiInsertQuery .= "INSERT INTO users (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');";
$multiInsertQuery .= "INSERT INTO users (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($connection->multi_query($multiInsertQuery)) {
    echo "Multiple records inserted successfully<br>";
} else {
    echo $connection->error;
}

$connection->close();
?>

Reading Data

mysqli_num_rows() returns the number of rows from a SELECT query. mysqli_fetch_assoc() fetches a result row as a associative array.

<?php
$connection = new mysqli("localhost", "root", "root", "php");

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
echo "Connected successfully<br>";

$selectQuery = "SELECT id, firstname, lastname FROM users";
$result = $connection->query($selectQuery);

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

$connection->close();
?>