MySQL Connect with PHP

Connecting PHP to MySQL Database

Establishing a MySQL Connection in PHP

Before using MySQL, you must establish a connection between PHP and the database. If MySQL and PHP are installed on your system, you can use localhost as the hostname, and typically, the default password is none.

The function mysqli_connect() is used to connect to MySQL. It returns a resource, which is a pointer to the database connection.

Syntax:

mysqli_connect(hostname, username, password, dbname);

Arguments:

Argument	Description
hostname	Either a hostname or an IP address
username	The MySQL username
password	MySQL user password
dbname	The default database used when performing queries

Note: If MySQL and PHP are running on the same machine, you can use localhost as the hostname. If the MySQL service runs on a separate server, use its IP address or URL instead.

Example:

<?php
// Database connection establishment
$con = mysqli_connect("example.com", "alex", "qwerty", "my_database");

// Check connection
if (mysqli_connect_errno($con)) {
    echo "MySQL database connection failed: " . mysqli_connect_error();
}
?>

Closing a Connection

To close the database connection manually before the script ends, use mysqli_close():

Example:

<?php
// Create connection
$con = mysqli_connect("example.com", "alex", "qwerty", "my_database");

// Check connection
if (mysqli_connect_errno($con)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Close Connection
mysqli_close($con);
?>

Ways to Connect to MySQL in PHP

PHP offers two ways to connect to a MySQL server:

1. MySQLi (MySQL Improved Extension)

2. PDO (PHP Data Objects)

While PDO is more versatile, supporting multiple database systems, MySQLi is optimized for MySQL databases and provides both procedural and object-oriented APIs.

Connecting to MySQL Using Different Methods

1. MySQLi (Procedural)

$link = mysqli_connect("localhost", "root", "");

2. MySQLi (Object-Oriented)

$mysqli = new mysqli("localhost", "root", "");

3. PDO (PHP Data Objects)

$pdo = new PDO("mysql:host=localhost;dbname=database", "root", "");

Example: Connecting and Closing MySQL Database

<?php
// Attempt MySQL server connection (default settings: user 'root' with no password)
$link = mysqli_connect("localhost", "root", "");

// Check connection
if ($link === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Print host information
echo "Connected successfully. Host info: " . mysqli_get_host_info($link);

// Close connection
mysqli_close($link);
?>

Creating a MySQL Database Table

Step 1: Creating the Table

Run the following SQL query to create a users table:

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Step 2: Creating the Database Configuration File

Create a config.php file to handle the database connection:

<?php
/* Database credentials - assuming default MySQL settings (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'demo');

/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
?>

This setup ensures that your PHP application can securely connect to and interact with a MySQL database.