PHP
Unit 5:Database Handling Using PHP and MYSQL
Database Terms:
a. Table: it contains data in structured format. In form of rows and columns.
b. Key: It is a unique value that is sufficient to identify any record in a table.
c. Join: When we want to access information from more than one table, we should join
two tables using join operation.
d. Query: In SQL this is a statement that is written to gather information from a
database.
e. Schema: Schema is the way to relate or map multiple tables.
Using Mysql Client and Using PhpMyAdmin:
phpMyAdmin is the most trusted and user-friendly database managers and mostly used for
web-based applications or programs. In the following article, we will be learning about the
importance of the phpMyAdmin tool in the web world.
phpMyAdmin | Pre-requisites: To install phpMyAdmin software, you need a server
running platform like Windows or Linux supports operating systems.
Web Browser: You need a web browser interface to run the tool.
PHP scripting language: You need a server side language.
Apache Web server: You need a web server to store phpMyAdmin files.
MySQL or MariaDB Database: You need a database to manage application data.
PhpMyAdmin Features : Let us now look into some of the features of
phpMyAdmin tool.
Manages all user level permissions.
Executes SQL queries, batch queries, triggers, events, stored procedures, functions to
display relevant data results.
It performs all data related operations like create, read, edit, delete, browse, change
structure, schema and search.
It also provide ways to import, export and load text file data.
It export data in various formats like CSV, XML, PDF, Word document, Latex,
Spreadsheet, Excel file and many more.
It supports complex queries and bookmarking of SQL queries.
It can also optimize, repair, change tables and views.
It also supports InnoDB, foreign keys and MySQLi.
It provides user friendly graphical interface to access website related data.
It can provide PDF graphics layout of database.
It is very flexible with different operating system.
It can handle complex queries using Query by example.
It provides multiple-server administration.
It can control several servers at the same time.
phpMyAdmin Configuration:
Download the latest version of phpMyAdmin
Once the files are extracted during the download process, they are stored in
location C:\xampp\phpMyAdmin
Dept Of BCA,USMR College,Shankarghatta Page 1
PHP
The configuration settings can be changed in config.inc.php file which is present in
the above location. Other system settings can also be changed in the file.
Administrators have full rights for creating new users, remove users and modify
privileges for old users.
For a specific database, you can define the permissions of any user and manage
accounts of user groups.
phpMyAdmin Logging in: Whenever a user logs in to phpMyAdmin server
with the username and password, these values are passed directly to MySQL server.
For logging in, you need the valid credentials of MySQL users.
Database server
Database username
Database password
Once all the credentials are entered correctly, you can see the phpMyAdmin home page in
your browser. When you are in phpMyAdmin page, the center section is the
default General Settings and Appearance Settings and in the right side, you can
see Database server and Web server information.
phpMyAdmin Access of Database information:
You can see information_schema database in the left side of the screen. It is the database
which stores all the information of all other databases like name of databaseS, name of
tables, column data type, access privileges and so on. It is a built-in database created with
the purpose of giving information about the whole database system.
In information_schema tables are automatically populated by the MySQL DB server.
phpMyAdmin Create database: A user can create a database in
phpMyAdmin server. Just click on the Databases tab, you have a Create
database input option. Give a relevant name for the database in the entry box and
click the Create button. The phpMyAdmin will create tables which will be added
under the newly created database.
You can add as many numbers of tables as required in one particular selected database by
using the section Create table and then the select number of columns needed for any table.
phpMyAdmin Execute a SQL query : Let us look into the steps to execute a
SQL query on a selected database.
In the left side of the screen, select the database to work with. The Structure of the
database can be seen after the selection.
Click SQL tab from top bar to open editor in which you can write the queries.
Write the query into the SQL editor.
You can also write multiple queries, with semicolon (;) separating different queries.
Click Go button to execute the query. If the query is executed successfully, you will get
a success message.
Another way to run a SQL query in the database is by using Query tab. Using this
you can give many search conditions, query more than one table and also sort the
results. You can even easily pick all the columns needed for MySQL query by
using Column drop down menus and Show checkbox.
For sorting the result set in a query in an ascending or descending order, you can use
the Sort dropdown. You can also enter the criteria for the search query.
The Ins and Del check boxes or Add/Delete criteria rows options are provided to
add or delete text rows for any search conditions. The logical operators And or
the Or can also be used to define relations between the fields in the Modify section.
Dept Of BCA,USMR College,Shankarghatta Page 2
PHP
After entering all the relevant conditions, you need to click on Update
Query button for doing the SQL operation. Next step is to select all the tables
from Use Tables section used in the query and then click on Submit Query.
phpMyAdmin Operations: In the phpMyAdmin tool, different operations can
be performed on the database or on a separate table. Select the database for which
you want to perform any operation and click the Operations tab. It is allowed to
create a new table under the selected database by using Create table section. You
can also rename the selected database by using Rename database to section.
Similarly you also have Remove database to delete database and Copy database
to sections which can be used as required for any SQL operation.
phpMyAdmin Generate a search query: We can also generate a search
query using phpMyAdmin GUI, rather than writing the search query manually for a
selected table. This can be done by clicking the Search tab in the top menu of the
screen.
phpMyAdmin Insert query: We can also insert query using phpMyAdmin
GUI, rather than manually writing it. Select the table for which insert query will be
executed. Then click the Insert tab in the top menu of the screen. Once the relevant
values are entered, the new record will be inserted in the table. The newly inserted
record can be viewed using the Browse tab under the selected table name.
MySql Commands:SQL, Structured Query Language, is a programming language
designed to manage data stored in relational databases. SQL commands are
instructions, coded into SQL statements, which are used to communicate with the
database to perform specific tasks, functions and queries with data. I would like to
share a list of the most important commands which are vital for a good developer.
1. SELECT — extracts data from a database
SELECT column_name
FROM table_name;
SELECT statements fetch data from a database.
2. UPDATE — updates data in a database
UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;
Dept Of BCA,USMR College,Shankarghatta Page 3
PHP
UPDATE statements allow us to edit rows in a table.
3. DELETE — deletes data from a database
DELETE FROM table_name
WHERE some_column = some_value;
DELETE statements remove rows from a table.
4. INSERT INTO — inserts new data into a database
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, ‘value_2’, value_3);
INSERT statements add a new row to a table.
5. CREATE DATABASE — creates a new database
CREATE DATABASE databasename;
CREATE DATABASE statements create a new SQL database.
6. ALTER DATABASE — modifies a database
ALTER DATABASE database_name
[COLLATE collation_name ]
ALTER DATABASE statements change the characteristics of a database.
7. CREATE TABLE — creates a new table
Dept Of BCA,USMR College,Shankarghatta Page 4
PHP
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype
);
CREATE TABLE statements create a new table in the database.
8. ALTER TABLE — modifies a table
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE statements add, delete, or modify columns in an existing table.
9. DROP TABLE — deletes a table
DROP TABLE table_name;
DROP TABLE statements drop an existing table in a database.
10. CREATE INDEX — creates an index
CREATE INDEX index_name
ON table_name (column_name1, column_name2…);
Index statements create on existing tables to retrieve the rows quickly.
11. DROP INDEX — deletes an index
Dept Of BCA,USMR College,Shankarghatta Page 5
PHP
ALTER TABLE table_name
DROP INDEX index_name;
PHP MYSqli Functions:
1)The affected_rows / mysqli_affected_rows():function returns the number of
affected rows in the previous SELECT, INSERT, UPDATE, REPLACE, or DELETE query.
Object oriented style:
$mysqli -> affected_rows
Procedural style:
mysqli_affected_rows(connection)
Example:
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Perform queries and print out affected rows
$mysqli -> query("SELECT * FROM Persons");
echo "Affected rows: " . $mysqli -> affected_rows;
$mysqli -> query("DELETE FROM Persons WHERE Age>32");
echo "Affected rows: " . $mysqli -> affected_rows;
$mysqli -> close();
?>
2) The autocommit() / mysqli_autocommit()function :turns on or off auto-
committing database modifications.
Object oriented style:
$mysqli -> autocommit(mode)
Procedural style:
mysqli_autocommit(connection, mode)
Example:
Dept Of BCA,USMR College,Shankarghatta Page 6
PHP
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Turn autocommit off
$mysqli -> autocommit(FALSE);
// Insert some values
$mysqli -> query("INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Peter','Griffin',35)");
$mysqli -> query("INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Glenn','Quagmire',33)");
// Commit transaction
if (!$mysqli -> commit()) {
echo "Commit transaction failed";
exit();
}
$mysqli -> close();
?>
3) The character_set_name() / mysqli_character_set_name() function :returns the
default character set for the database connection.
Syntax
Object oriented style:
$mysqli -> character_set_name()
Procedural style:
mysqli_character_set_name(connection)
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$charset = $mysqli -> character_set_name();
echo "Default character set is: " . $charset;
Dept Of BCA,USMR College,Shankarghatta Page 7
PHP
$mysqli -> close();
?>
4)The commit() / mysqli_commit() function: commits the current transaction for the specified
database connection.
Object oriented style:
$mysqli -> commit(flags, name)
Procedural style:
mysqli_commit(connection, flags, name)
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Turn autocommit off
$mysqli -> autocommit(FALSE);
// Insert some values
$mysqli -> query("INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Peter','Griffin',35)");
$mysqli -> query("INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Glenn','Quagmire',33)");
// Commit transaction
if (!$mysqli -> commit()) {
echo "Commit transaction failed";
exit();
}
$mysqli -> close();
?>
5)The error / mysqli_error() function:returns the last error description for the most recent
function call.
Object oriented style:
$mysqli -> error
Procedural style:
mysqli_error(connection)
Dept Of BCA,USMR College,Shankarghatta Page 8
PHP
Example:
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Perform a query, check for error
if (!$mysqli -> query("INSERT INTO Persons (FirstName) VALUES ('Glenn')")) {
echo("Error description: " . $mysqli -> error);
}
$mysqli -> close();
?>
6)The fetch_all() / mysqli_fetch_all() function:fetches all result rows and returns the result-
set as an associative array, a numeric array, or both.
Object oriented style:
$mysqli_result -> fetch_all(resulttype)
Procedural style:
mysqli_fetch_all(result, resulttype)
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$sql = "SELECT Lastname, Age FROM Persons ORDER BY Lastname";
$result = $mysqli -> query($sql);
// Fetch all
$result -> fetch_all(MYSQLI_ASSOC);
// Free result set
$result -> free_result();
$mysqli -> close();
?>
7) The fetch_assoc() / mysqli_fetch_assoc() function: fetches a result row as an associative
array.
Dept Of BCA,USMR College,Shankarghatta Page 9
PHP
Object oriented style:
$mysqli_result -> fetch_assoc()
Procedural style:
mysqli_fetch_assoc(result)
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$sql = "SELECT Lastname, Age FROM Persons ORDER BY Lastname";
$result = $mysqli -> query($sql);
// Associative array
$row = $result -> fetch_assoc();
printf ("%s (%s)\n", $row["Lastname"], $row["Age"]);
// Free result set
$result -> free_result();
$mysqli -> close();
?>
8) The get_charset() / mysqli_get_charset() function :returns a character set object with
several properties for the current character set.
Object oriented style:
$mysqli -> get_charset()
Procedural style:
mysqli_get_charset(connection);
Example:
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
var_dump($mysqli -> get_charset());
Dept Of BCA,USMR College,Shankarghatta Page 10
PHP
$mysqli -> close();
?>
9) The stmt_init() / mysqli_stmt_init() function :initializes a statement and returns an object
suitable for mysqli_stmt_prepare().
Object oriented style:
$mysqli -> stmt_init()
Procedural style:
mysqli_stmt_init(connection)
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$city="Sandnes";
// Create a prepared statement
$stmt = $mysqli -> stmt_init();
if ($stmt -> prepare("SELECT District FROM City WHERE Name=?")) {
// Bind parameters
$stmt -> bind_param("s", $city);
// Execute query
$stmt -> execute();
// Bind result variables
$stmt -> bind_result($district);
// Fetch value
$stmt -> fetch();
printf("%s is in district %s", $city, $district);
// Close statement
$stmt -> close();
}
Dept Of BCA,USMR College,Shankarghatta Page 11
PHP
$mysqli -> close();
?>
10) The rollback() / mysqli_rollback() function rolls back the current transaction for the
specified database connection.
Object oriented style:
$mysqli -> rollback(flags, name)
Procedural style:
mysqli_rollback(connection, flags, name)
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Turn autocommit off
$mysqli -> autocommit(FALSE);
// Insert some values
$mysqli -> query("INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Peter','Griffin',35)");
$mysqli -> query("INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Glenn','Quagmire',33)");
// Commit transaction
if (!$mysqli -> commit()) {
echo "Commit transaction failed";
exit();
}
// Rollback transaction
$mysqli -> rollback();
$mysqli -> close();
?>
Connection To Mysql and Selecting the Database:
PHP 5 and later can work with a MySQL database using:
MySQLi extension (the "i" stands for improved)
PDO (PHP Data Objects)
Dept Of BCA,USMR College,Shankarghatta Page 12
PHP
MySQL Examples in Both MySQLi and PDO Syntax
In this, and in the following chapters we demonstrate three ways of working with PHP and
MySQL:
MySQLi (object-oriented)
MySQLi (procedural)
PDO
Open a Connection to MySQL
Before we can access data in the MySQL database, we need to be able to connect to
the server:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Dept Of BCA,USMR College,Shankarghatta Page 13
PHP
Close the Connection
The connection will be closed automatically when the script ends. To close the
connection before, use the following:
*Mysqli Object oriented:
$conn->close();
*MySQLi Procedural:
mysqli_close($conn);
A database consists of one or more tables.
You will need special CREATE privileges to create or to delete a MySQL database.
Create a MySQL Database Using MySQLi and PDO
The CREATE DATABASE statement is used to create a database in MySQL.
The following examples create a database named "myDB":
Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "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();
?>
Dept Of BCA,USMR College,Shankarghatta Page 14
PHP
A database table has its own unique name and consists of columns and rows.
Create a MySQL Table Using MySQLi and PDO
The CREATE TABLE statement is used to create a table in MySQL.
We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname",
"email" and "reg_date":
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " .$conn->error;
Dept Of BCA,USMR College,Shankarghatta Page 15
PHP
$conn->close();
?>
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Insert Data Into MySQL Using MySQLi and PDO
After a database and a table have been created, we can start adding data in them.
Here are some syntax rules to follow:
Dept Of BCA,USMR College,Shankarghatta Page 16
PHP
The SQL query must be quoted in PHP
String values inside the SQL query must be quoted
Numeric values must not be quoted
The word NULL must not be quoted
*The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
*Select Data From a MySQL Database
The SELECT statement is used to select data from one or more tables:
SELECT column_name(s) FROM table_name
or we can use the * character to select ALL columns from a table:
SELECT * FROM table_name
Select Data With MySQLi
Example (MySQLi Object-oriented)
The following example selects the id, firstname and lastname columns from the MyGuests
table and displays it on the page:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Dept Of BCA,USMR College,Shankarghatta Page 17
PHP
Update Data In a MySQL Table Using MySQLi and PDO
The UPDATE statement is used to update existing records in a table:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Example (MySQLi Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
Executing Simple Queries:
In any case, the PHP function for executing a query is mysql_query(): $result =
mysql_query($query); For simple queries like INSERT, UPDATE, DELETE, etc. (which do
not return records), the $result variable will be either TRUE or FALSE based upon the
successful execution of the query on the database.
Example - Object Oriented style
Perform query against a database:
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
// Check connection
if ($mysqli -> connect_errno) {
Dept Of BCA,USMR College,Shankarghatta Page 18
PHP
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Perform query
if ($result = $mysqli -> query("SELECT * FROM Persons")) {
echo "Returned rows are: " . $result -> num_rows;
// Free result set
$result -> free_result();
}
$mysqli -> close();
?>
The query() / mysqli_query() function performs a query against a database.
Syntax
Object oriented style:
$mysqli -> query(query, resultmode)
Procedural style:
mysqli_query(connection, query, resultmode)
Retrieving Query Results:
PHP $result = mysqli_query($connection, $query); The mysqli_query() function takes
two parameters: the database connection and the SQL query. It returns a result object
containing the retrieved record. To retrieve the single record from the result object, you need
to use a fetch function.{Refer Class notes}.
Dept Of BCA,USMR College,Shankarghatta Page 19
