Connecting to MySQL

MySQL is a server. Your application connects to it over a host and port with a username and password, then chooses a database.

The Command Line

The client is usually installed with the server. Use -u for the user and -p to be prompted for a password.

mysql -u root -p
mysql -u root -p -h 127.0.0.1 -P 3306

Choose a Database

After connecting, list databases and select one.

SHOW DATABASES;
USE courselordfusion;
SELECT DATABASE();

Connect from PHP

PDO gives one interface for MySQL and other databases. Always set the error mode to exceptions so failures are not silent.

<?php

$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=courselordfusion;charset=utf8mb4';
$pdo = new PDO($dsn, 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$rows = $pdo->query('SELECT id, name FROM students')->fetchAll();
print_r($rows);

Practice

1. Connect to the server and list the databases.
2. Select a database and show its tables.
3. Connect from PHP and print one row.

Hands-on exercise

Write a PHP script that:
1. Connects to a database named school with charset utf8mb4.
2. Throws exceptions on error.
3. Runs SELECT 1 AS ok and prints the result.

Recap

MySQL is a server. A connection needs a host, port, user, password and database, and PDO should always throw exceptions.