PHP and MySQL Interactions
• PHP and MySQL interact with each other through PHP and
MySQL extensions.
• We connect to MySQL from PHP using PHP’s MySQL-related
extensions.
• There are two types of such extensions for PHP 5.5.0 and
higher or later versions since 2012, MySQLi ("i" stands from
"improved") and PDO (PHP Data Objects).
• Now, the question is "Which Should I Use? MySQLi or PDO?".
• You are free to use "whatever you like".
• I will be using MySQLi, however you may use PDO when
"multiple database support“ is needed since PDO offers
support for many databases (not only MySQL).
Wednesday, June 25, 2025 Lecturer : Zano C
MySQL Database creation
• Before we can interact with a database, it should have been
created.
• A MySQL database can be created either through the
phpMyAdmin interface for example in wamp server, using
terminal interface in linux to access the MySQL server or
using PHP code.
• With phpMyAdmin, we manually design and manipulate the
database while code is automatically created on the
background.
• You may need to familiarise yourself with this MySQL
environment before you concentrate much on database
manipulation from PHP.
Wednesday, June 25, 2025 Lecturer : Zano C
MySQL Database Creation Using PHP
• We can design and manipulate a database using the Structured
Query Language (SQL).
• These SQL statements can be executed in php.
• To create a database you need to first make a connection to the
MySQL server.
• A connection to MySQL is opened using the mysqli_connect()
function.
• The function takes three arguments, the server name, username
and password.
• If we have not changed configurations to the .ini files during php
and mysql installations, the default user is root with no password.
• It is advisable to change these settings for security reasons.
Wednesday, June 25, 2025 Lecturer : Zano C
MySQL Database Creation Example1
• <?php
// variable declaration
$servername = "localhost";
$username = “root";
$password = “ ";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Wednesday, June 25, 2025 Lecturer : Zano C
MySQL Database Creation Example1
• If our server is on the same machine we are using to connect, the we
use localhost or the IP address 127.0.0.1, which is a loopback address
used by a machine to address itself internally. Otherwise we have to
specify the database host server.
• It is also important to check for errors during connection and guide
users accordingly.
• Queries are executed by the mysqli_query() function.
• The function takes two arguments, a link or connection to the MySQL
server and the string for the query to be executed or just its variable.
• Example 1 is a procedural way of creating the database, those who are
more experienced with object oriented programming can used the
object oriented way as in example 2.
Wednesday, June 25, 2025 Lecturer : Zano C
MySQL Database Creation Example2
• <?php
$servername = "localhost";
$username = “root";
$password = “ ";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
$conn->close();
?>
Wednesday, June 25, 2025 Lecturer : Zano C
Connecting to the MySQL Database
• When a database is already created, we can connect to it using the mysqli_connect () function with four
arguments.
• Apart from opening a connection to MySQL server, the additional argument specifies the database we need
to connect to.
• <?php
$servername = "localhost";
$username = “root";
$password = “ ";
• $dbase =“myDB”;
// Create connection
$conn = new mysqli($servername, $username, $password,Sdbase);
// Check connection
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
} else {
echo “connection established “;
}
$conn->close();
?>
Wednesday, June 25, 2025 Lecturer : Zano C
Connecting to the MySQL Database
•
// Create connection
$conn = new mysqli($servername, $username,
$password,Sdbase);
// Check connection
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn-
>connect_error, E_USER_ERROR);
} else {
echo “connection established “;
}
$conn->close();
?>
Wednesday, June 25, 2025 Lecturer : Zano C
Connecting to the MySQL Database
• The procedural way of making a connection
• <?php
$servername = "localhost";
$username = “root";
$password = “ ";
$dbase =“myDB”;
// Create connection
$conn = new mysqli($servername, $username, $password,Sdbase);
// Check connection
if (mysqli_connect_errno()) {
trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
} else {
echo “connection established “;
}
mysqli_close($conn);
?>
Wednesday, June 25, 2025 Lecturer : Zano C
