InternetProgramming Databases

Internet Programming : CSC2233
Databases (PHP /MySQLi)
H.D Supuni Shashikala

Outline
▪ Web database Architecture
▪ PHP Supported Databases
▪ PHP/MySQL Functions
▪ Connecting to MySQL
▪ Making MySQL Queries
▪ Fetching Datasets
▪ MySQL Functions
Department of Computer Science [email protected] 2

What is a Database?
▪ A database is a collection of data that is stored independently
▪ Collection of tables and associated indexes
Basic database concepts :

▪ Fields
▪ Records
▪ Table
▪ Queries

SQL
SQL is an ANSI (American National Standards Institute) standard computer
language
for accessing and manipulating databases.
▪ SQL stands for Structured Query Language
▪ using SQL can you can
▪ access a database
▪ execute queries, and retrieve data
▪ insert, delete and update records

SQL
▪ SQL works with database programs like MS Access, DB2, Informix, MS SQL Server,
Oracle, Sybase, MySQL etc.
▪ MySQL is a very fast, robust, relational database management system (RDBMS).
▪ A database enables you to efficiently store, search, sort, and retrieve data.
▪ MySQL uses SQL (Structured Query Language), the standard database query language
worldwide.
▪ MySQL is ideal for both small and large applications.

PHP
• PHP was conceived and developed in 1994 by Rasmus Lerdorf.
• PHP stands for Hypertext Preprocessor.
• PHP is a server-side scripting language.
• PHP code is executed on the server.
• Within an HTML page, you can embed PHP code.

PHP
• PHP is an Open Source product (i.e you have access to the source code - you can
use it, alter it, and redistribute it all without charge).
• PHP is a powerful tool for making dynamic and interactive Web pages e.g feedback
forms, guestbook etc.
• With PHP, you can access database and files.
• To use PHP, you require a Web Server.

3-tier Architecture

3-tier Architecture Overview
o Presentation tier – Browser / custom client, Client Side Scripting (JavaScript,
ActionScript, VBScript etc.), Applets.
o Logical Tier – Web Server (Apache, IIS, Websphere etc.); Scripting Languages (PHP,
Perl etc.), Programming Languages (Java, C, C# etc), Application Frameworks (Ruby
on Rails etc.)
o Data Tier – Database Management System (DBMS) (Oracle, MySQL, SQL Server, DB2
etc.), XMLDB

Web Database Architecture

Web database Architecture
1. A user’s web browser issues an HTTP request for a particular web page. For
example, using an HTML form, she might have requested a search for all the books at
Book-O-Rama written by Laura Thomson. The search results page is called
results.php.
2. The web server receives the request for results.php, retrieves the file, and passes
it to the PHP engine for processing.
3. The PHP engine begins parsing the script. Inside the script is a command to connect
to the database and execute a query (perform the search for books). PHP opens a
connection to the MySQL server and sends on the appropriate query.

Web database Architecture
4. The MySQL server receives the database query, processes it, and sends the results—a list of
books—back to the PHP engine.
5. The PHP engine finishes running the script, which usually involves formatting the query
results nicely in HTML. It then returns the resulting HTML to the web server.
6. The web server passes the HTML back to the browser, where the user can see the list of
books she requested.

PHP Supported databases
• MySQL
• PostgreSQL
• Oracle
• SQLite
• Sybase
• dBase
• MS-SQL

Connecting to MySQL DB
▪ There are three main API options when considering connecting to a MySQL database
server:
o PHP's MySQL Extension - original extension which provides a procedural interface

and is intended for use only with MySQL versions older than 4.1.3. Can be used
with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server
features will be available.
o PHP's mysqli Extension - MySQL improved extension takes advantage of new

features found in MySQL versions 4.1.3 and newer. The mysqli extension is
included with PHP versions 5 and later.

Connecting to MySQL DB
o PHP Data Objects (PDO) - PHP Data Objects, or PDO, is a database abstraction
layer that provides a consistent API regardless of the type of database server. In
theory, it allows for the switch of the database server, from say Firebird to MySQL,
with only minor changes to the PHP code.
** If using MySQL versions 4.1.3 or later it is strongly recommended that the mysqli
extension is used.

Advantages of mysqli
o Object-oriented interface
o Support for Prepared Statements
o Support for Multiple Statements
o Support for Transactions
o Enhanced debugging capabilities
o Embedded server support
o Mysqli package has improved speed, security and compatibility with

libraries.

19
PHP and MySQLi
Department of Computer Science [email protected]

Opening and Closing a MySQL
Connection
Open a connection to a MySQL database server with the mysqli_connect() function
• The mysqli_connect() function returns a positive integer if it connects to the database

successfully or FALSE if it does not
• Assign the return value from the mysqli_connect() function to a variable that can use
to access the database in the script

Opening and Closing a MySQL Connection
• The syntax for the mysqli_connect() function is:

$connection = mysqli_connect("host" [, "user", "password"[,”database”]]);
• The host argument specifies the host name where the MySQL database server is
installed
• The user and password arguments specify a MySQL account name and password
• You can optionally select the database when connecting.

Opening and Closing a MySQL Connection
• The database connection is assigned to the $DBConnect variable

$DBConnect = mysqli_connect("localhost", “root ", "");
• Close a database connection using the mysql_close() function

mysqli_close($DBConnect);

Important functions in mysqli

Important functions in mysqli

Reporting MySQL Errors
• Reasons for not connecting to a database server include:
– The database server is not running
– Insufficient privileges to access the data source
– Invalid username and/or password
▪ The mysqli_errno() function returns the error code from the last attempted MySQL
function call or 0 if no error occurred
▪ The mysqli_error() — Returns the text of the error message from previous MySQL
operation
▪ The mysqli_errno() and mysqli_error() functions return the results of the previous
mysqli*() function

Selecting a database
▪ The syntax for the mysqli_select_db() function is:
mysqli_select_db(connection, database);
• The function returns a value of TRUE if it successfully selects a database or FALSE if

it does not

Executing SQL Statements
• Use the mysqli_query() function to send SQL statements to MySQL

• The syntax for the mysqli_query() function is: mysqli_query(connection, query);
• The mysqli_query() function returns one of three values:
– For SQL statements that do not return results (CREATE DATABASE and CREATE
TABLE statements) it returns a value of TRUE if the statement executes successfully

Executing SQL Statements
▪ For SQL statements that return results (SELECT and SHOW statements) the
mysqli_query() function returns a result pointer that represents the query results
• A result pointer is a special type of variable thatrefers to the currently selected row
in a resultset
– The mysqli_query() function returns a value of FALSE for any SQL statements that
fail, regardless of whether they return results

Adding/Deleting/Updating Records
▪ To add records to a table, use the INSERT and VALUES keywords with the
mysqli_query() function
▪ To add multiple records to a database, use the LOAD DATA statement with the name
of the local text file containing the records you want to add
▪ To update records in a table, use the UPDATE statement

▪ 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
$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', '[email protected]')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Adding/Deleting/Updating Records
▪ The UPDATE keyword specifies the name of the table to update
▪ The SET keyword specifies the value to assign to the fields in the records that match
the condition in the WHERE clause
▪ To delete records in a table, use the DELETE statement with the mysqli_query()
function
▪ Omit the WHERE clause to delete all records in a table

Retrieving Records into an Indexed Array
▪ The mysqli_fetch_row() function returns the fields in the current row of a resultset
into an indexed array and moves the result pointer to the next row

Fetching Results
▪ The mysqli_fetch_assoc() function returns the fields in the current row of a resultset
into an associative array and moves the result pointer to the next row
▪ The difference between mysqli_fetch_assoc() and mysqli_fetch_row() is that instead
of returning the fields into an indexed array, the mysqli_fetch_assoc() function

returns the fields into an associative array and uses each field name as the array key

Affected Rows
▪ Using the mysqli_affected_rows() Function :
▪ With queries that return results (SELECT queries), use the mysqli_num_rows()
function to find the number of records returned from the query
▪ With queries that modify tables but do not return results (INSERT, UPDATE, and
DELETE queries), use the mysqli_affected_rows() function to determine the number
of affected rows

Return Information on Query
▪ Using the mysqli_info() Function :
▪ For queries that add or update records, or alter a table’s structure, use the
mysqli_info() function to return information about the query
▪ The mysqli_info() function returns the number of operations for various types of
actions, depending on the type of query
▪ The mysqli_info() function returns information about the last query that was
executed on the database connection

Return Information on Query contd.
▪ The mysqli_info() function returns information about queries that match one of the
following formats:
▪ – INSERT INTO...SELECT...
▪ – INSERT INTO...VALUES (...),(...),(...)
▪ – LOAD DATA INFILE ...
▪ – ALTER TABLE ...
▪ – UPDATE
▪ For any queries that do not match one of these formats, the mysql_info() function
returns an empty string

<?php
$link = mysqli_connect("localhost", "user1", “abc", "hr");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TEMPORARY TABLE t1 LIKE City");
/* INSERT INTO .. SELECT */
mysqli_query($link, "INSERT INTO t1 SELECT * FROM City ORDER BY ID LIMIT 150");
printf("%s\n", mysqli_info($link));
/* close connection */
mysqli_close($link);
?>
Source : https://www.w3resource.com/
45
46
47
Closing Query Results
▪ When you are finished working with query results retrieved with the
mysqli_query() function, use the mysqli_free_result() function to close the
resultset.
▪ To close the resultset, pass to the mysqli_free_result() function the variable

containing the result pointer from the mysqli_query() function
48
Query Result Information
▪ The mysqli_num_rows() function returns the number of rows in a query result
▪ The mysqli_num_fields() function returns the number of fields in a query result
▪ Both functions accept a database connection variable as an argument
49
▪ <?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql="SELECT Name,City FROM Persons ORDER BY Name";
if ($result=mysqli_query($con,$sql))
{
// Return the number of rows in result set
$rowcount=mysqli_num_rows($result);
printf(“Your query returned %d rows.\n",$rowcount);
// Free result set

mysqli_free_result($result);
} Output :
mysqli_close($con);
?>
50
Escaping Dangerous characters
▪ The mysqli_real_escape_string() function escapes special characters in a string for
the use in an SQL query.
▪ Used to create a legal SQL string to be used in an SQL statement
▪ Makes a string safe to use by searching and escaping all dangerous characters(eg. `
or % ) that could lead to SQL errors and injection attacks
51
Source : https://www.w3schools.com/
52
Extra..
▪ SQL Injection Attacks
▪ MySQLi OOP-style syntax vs. MySQLi procedural-style syntax
53
Summary
▪ mysqli_connect() function : opens a connection to a MySQL database server
▪ mysqli_close() function : closes a database connection
▪ mysqli_errno() function : returns the error code from the last attempted MySQL
function call or zero if no error occurred
▪ mysqli_error() function : returns the error message from the last attempted
MySQL function call or an empty string if no error occurred
▪ mysqli_create_db() function : to create a new database
▪ mysqli_select_db() function : selects a database
▪ mysqli_drop_db() function : to delete a database
▪ mysqli_query() function : sends SQL statements to MySQL
54
Summary
▪ A result pointer is a special type of variable that refers to the currently selected
row in a resultset
▪ CREATE TABLE statement with the mysqli_query() function to create a table
▪ PRIMARY KEY clause indicates a field or fields that will be used as a referential
index for the table
▪ The AUTO_INCREMENT clause creates a field that is automatically updated with
the next sequential value for that column
▪ The NOT NULL clause creates a field that must contain data
▪ mysqli_info() function : returns the number of operations for various types of
actions, depending on the type of query.
▪ mysqli_fetch_row() function : returns the fields in the current row of a resultset
into an indexed array and moves the result pointer to the next row
55
Summary
▪ mysqli_fetch_assoc() function: returns the fields in the current row of a resultset
into an associative array and moves the result pointer to the next row
▪ mysqli_free_result() function closes a resultset
▪ mysqli_num_rows() function : returns the number of rows in a query result
▪ mysqli_num_fields() function : returns the number of fields in a query result
56
References
▪ https://alexwebdevelop.com/php-with-mysql/
▪ http://zetcode.com/php/mysqli/
▪ https://www.math.ucla.edu/~virtanen/40a.1.15s/schedule/lec21.pdf
▪ https://www.studocu.com/en/document/swinburne-university-of-technology/web-
application-development/lecture-notes/my-sql-lecture-notes-4/965610/view
▪ https://www.tjscott.net/websecurity/php.mysql.tutorial.pdf
▪ https://www.w3schools.com/

THANK YOU

InternetProgramming Databases

Uploaded by

Copyright:

Available Formats

InternetProgramming Databases

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

InternetProgramming Databases

Uploaded by

Copyright:

Available Formats

Internet Programming : CSC2233

Databases (PHP /MySQLi)

H.D Supuni Shashikala

Department of Computer Science [email protected] 2

Basic database concepts :

Department of Computer Science [email protected] 3

for accessing and manipulating databases.

▪ SQL stands for Structured Query Language

▪ using SQL can you can

▪ execute queries, and retrieve data

▪ insert, delete and update records

Department of Computer Science [email protected] 5

▪ MySQL is a very fast, robust, relational database management system (RDBMS).

▪ MySQL is ideal for both small and large applications.

Department of Computer Science [email protected] 6

Department of Computer Science [email protected] 7

• With PHP, you can access database and files.

• To use PHP, you require a Web Server.

Department of Computer Science [email protected] 8

Department of Computer Science [email protected] 9

Department of Computer Science [email protected] 11

Department of Computer Science [email protected] 12

Department of Computer Science [email protected] 13

Department of Computer Science [email protected] 14

Department of Computer Science [email protected] 15

o PHP's MySQL Extension - original extension which provides a procedural interface

o PHP's mysqli Extension - MySQL improved extension takes advantage of new

Department of Computer Science [email protected] 16

Department of Computer Science [email protected] 17

o Support for Prepared Statements

o Support for Multiple Statements

o Support for Transactions

o Enhanced debugging capabilities

o Embedded server support

o Mysqli package has improved speed, security and compatibility with

Department of Computer Science [email protected] 18

Department of Computer Science [email protected]

• The mysqli_connect() function returns a positive integer if it connects to the database

Department of Computer Science [email protected] 20

• The syntax for the mysqli_connect() function is:

Department of Computer Science [email protected] 21

• The database connection is assigned to the $DBConnect variable

• Close a database connection using the mysql_close() function

Department of Computer Science [email protected] 22

Department of Computer Science [email protected] 23

Department of Computer Science [email protected] 24

Department of Computer Science [email protected] 25

• The function returns a value of TRUE if it successfully selects a database or FALSE if

Department of Computer Science [email protected] 28

• Use the mysqli_query() function to send SQL statements to MySQL

Department of Computer Science [email protected] 30

Department of Computer Science [email protected] 31

▪ To update records in a table, use the UPDATE statement

Department of Computer Science [email protected] 32

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

Department of Computer Science [email protected] 33

▪ The UPDATE keyword specifies the name of the table to update

the condition in the WHERE clause

▪ Omit the WHERE clause to delete all records in a table