In this PHP tutorial, you will learn all about the MySQL database connectivity in PHP. We will also discuss how to connect databases, create databases, create tables, insert into tables, fetch data from databases, update data, delete data, and close connections.
MySQL is a Relational Database Management System (RDBMS). It is the most widely used database system with PHP. Oracle Corporation creates, distributes, and supports MySQL. MySQL is one of the most widely used relational database systems on the web. It is freely downloadable and simple to set up.
MySQL, like other relational databases, stores data in tables format. A table is a collection of related data that is organized into rows and columns. Each row in a table represents a data record that is inherently linked to one another, such as information on a single person, and each column represents a specific field, such as id, first name, last name, email, and so on.
To store or access data in a MySQL database, you must first connect to the MySQL database server. PHP provides two methods for connecting to a MySQL server: MySQLi (Improved MySQL) and PDO (PHP Data Objects). While the PDO extension is more portable and supports over a dozen other databases, the MySQLi extension, as the name implies, only supports the MySQL database. The MySQLi extension, on the other hand, makes it easy to connect to and run queries on a MySQL database server. Both PDO and MySQLi provide an object-oriented API, but MySQLi also provides a procedural API that is very simple for newcomers to understand.
The mysqli_connect() function in PHP makes this simple. This connection is used for all communication between PHP and the MySQL database server. The following are the fundamental syntaxes for connecting to MySQL through the MySQLi and PDO extensions:
Syntax: using MySQLi procedural
$c = mysqli_connect("hostname", "username", "password", "database");
Syntax: using MySQLi object-oriented
$mysqli = new mysqli("hostname", "username", "password", "database");
Syntax: using PDO
$c = new PDO("mysql:host=hostname; dbname=database", "username", "password");
In the above syntax, the hostname parameter specifies the hostname (e.g. localhost) or IP address of the MySQL server, whereas the username and password parameters specify the credentials to access the MySQL server, and the database parameter, if provided, specifies the default MySQL database to be used when performing queries.
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = mysqli_connect($host, $username, $password);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$mysqli = new mysqli($host, $username, $password);
if ($mysqli === false) {
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
echo "Connect Successfully. Host info: " . $mysqli->host_info;
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
try {
$c = new PDO("mysql:host=$host", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
After we connect to a MySQL database from a PHP script, we should also detach or close the connection when our work is finished. The syntax to close the MySQL connection in all three methods is mentioned below.
Syntax: using MySQLi procedural
mysqli_close($c);
Syntax: using MySQLi object-oriented
$c->close();
Syntax: using PDO
$c = null;
Now that you've learned how to connect to the MySQL database server. Now we will look to run a SQL query to build a database. We must first establish a database before we can save or access the data. In MySQL, the Establish DATABASE command is used to create a new database. Let's create a SQL query using the CREATE DATABASE statement, then run it by sending it to the PHP mysqli_query() function to eventually create our database.
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = mysqli_connect($host, $username, $password);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "CREATE DATABASE db_pro";
if (mysqli_query($c, $sql_query)) {
echo "Database created successfully";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = new mysqli($host, $username, $password);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "CREATE DATABASE db_obj";
if ($c->query($sql_query) === true) {
echo "Database created successfully";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
try {
$c = new PDO("mysql:host=$host", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "CREATE DATABASE de_pdo";
$c->exec($sql_query);
echo "Database created successfully";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
We learned how to construct a database on the MySQL server. It's now time to create tables in the database where we will really store the data. A table arranges data into rows and columns. To build a table in a database, use the SQL CREATE TABLE command. Let's write a SQL query using the CREATE TABLE statement, then run it by passing it to the PHP mysqli_query() function to eventually create our table.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)";
if (mysqli_query($c, $sql_query)) {
echo "Table created successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)";
if ($c->query($sql_query) === true) {
echo "Table created successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)";
$c->exec($sql_query);
echo "Table created successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
You now understand how to create a database and tables in MySQL. Now we will see how to run a SQL query to put records into a table. To insert new entries into a database table, use the INSERT INTO command. Let's create a SQL query with acceptable values using the INSERT INTO statement, and then we'll run it by sending it to the PHP mysqli_query() function to insert data into the table. Here's an example of inserting a new entry into the person's database by providing values for the first name, last name, and email fields.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]')";
if (mysqli_query($c, $sql_query)) {
echo "Records inserted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]')";
if ($c->query($sql_query) === true) {
echo "Records inserted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]')";
$c->exec($sql_query);
echo "Records inserted successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
The UPDATE statement is used to alter or change existing records in a database table. This statement is generally used in conjunction with the WHERE clause to limit the modifications to records that meet particular criteria. Let's create a SQL query with the UPDATE statement and WHERE clause, then execute it by providing it to the PHP mysqli_query() function to update the table's entries.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "UPDATE student SET email='[email protected]' WHERE id=1";
if (mysqli_query($c, $sql_query)) {
echo "Records were updated successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c--->connect_error);
}
$sql_query = "UPDATE student SET email='[email protected]' WHERE id=1";
if ($c->query($sql_query) === true) {
echo "Records were updated successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "UPDATE student SET email='[email protected]' WHERE id=1";
$c->exec($sql_query);
echo "Records were updated successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
The SQL DELETE command is used to delete records from a table in the same way that it is used to insert records into tables. It is commonly used in conjunction with the WHERE clause to remove only entries that meet specified criteria or conditions. Let's create a SQL query with the DELETE statement and WHERE clause, then execute it by providing it to the PHP mysqli_query() function to delete the table's records.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "DELETE FROM student WHERE first_name='Roy'";
if (mysqli_query($c, $sql_query)) {
echo "Records were deleted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj"; $c mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "DELETE FROM student WHERE first_name='Roy'";
if ($c->query($sql_query) === true) {
echo "Records were deleted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "DELETE FROM student WHERE first_name='Roy'";
$c->exec($sql_query);
echo "Records were deleted successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
So far, you've learned how to construct a database and a table, as well as how to input data. It is now time to recover the data that was inserted into the table in the last tutorial. To fetch records from database tables, using the SQL SELECT command. Let's create a SQL query using the SELECT statement, then execute it by providing it to the PHP mysqli_query() function to obtain the table data.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "SELECT * FROM student";
if ($result = mysqli_query($c, $sql_query)) {
if (mysqli_num_rows($result) > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Email</th>";
echo "</tr>";
while ($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_free_result($result);
} else {
echo "No records matching your query were found.";
}
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "SELECT * FROM student";
if ($result = $c->query($sql_query)) {
if ($result->num_rows > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Email</th>";
echo "</tr>";
while ($row = $result->fetch_array()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
$result->free();
} else {
echo "No records matching your query were found.";
}
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "SELECT * FROM student";
$result = $c->query($sql_query);
if ($result->rowCount() > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Email</th>";
echo "</tr>";
while ($row = $result->fetch()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
unset($result);
} else {
echo "No records matching your query were found.";
}
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
