WEB TECHNOLOGIES 2
Database in php
Lec 5
Mohammed Sultan
1
Outlines
• What is Mysql?
• PHP Connect to MySQL
• Database Queries
• Prepared Statements
• Stored Procedures
2
PHP
• you can connect to and manipulate databases.
• MySQL is the most popular database system
used with PHP.
3
What is MySQL?
• MySQL is a database system used on the web
• MySQL is a database system that runs on a
server
• MySQL is ideal for both small and large
applications
• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
4
What is MySQL?
• The data in a MySQL database are stored in
tables. A table is a collection of related data,
and it consists of columns and rows.
• Databases are useful for storing information
categorically.
– Employees
– Products
– Customers
– Orders
5
PHP + MySQL Database System
• PHP combined with MySQL are cross-
platform.
• PHP 5 and later can work with a MySQL
database using:
– MySQLi extension (the "i" stands for improved)
– PDO (PHP Data Objects)
6
Should I Use MySQLi or PDO?
• PDO will work on 12 different database
systems, whereas MySQLi will only work with
MySQL databases.
• So, if you have to switch your project to use
another database, PDO makes the process
easy.
7
Should I Use MySQLi or PDO?
• In this, and in the following chapters we
demonstrate three ways of working with PHP
and MySQL:
• MySQLi (object-oriented)
• MySQLi (procedural)
• PDO
8
Dual procedural and object-oriented
interface
• The mysqli extension features a dual interface. It supports the
procedural and object-oriented programming paradigm.
• Users migrating from the old mysql extension may prefer the
procedural interface.
• The procedural interface is similar to that of the old mysql
extension.
• In many cases, the function names differ only by prefix. Some
mysqli functions take a connection handle as their first
argument, whereas matching functions in the old mysql
interface take it as an optional last argument.
9
Database Queries
• A query is a question or a request.
• We can query a database for specific
information and have a recordset returned.
– SELECT * FROM students;
10
Dealing with forms
• HTML Forms (GET and POST)
• form is submitted to a PHP script
• information from that form is automatically made
available to the script
– forms.php
<form action="foo.php" method="POST">
Name: <input type="text" name="username"><br>
Email: <input type="text" name="email"><br> <input
type="submit" name="submit" value="Submit me!">
</form>
11
Steps for dealing with DB
• Connect to the MySQL server
– $connection = mysql_connect("localhost",
$username, $password);
• Access the database
– mysql_select_db("moh", $connection);
• Perform SQL operations
• Disconnect from the server
– mysql_close($connection);
12
Connect to the MySQL
(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";
?>
For closing the connection:
mysqli_close($conn); 13
Error Handling
• All mysql_ functions return NULL (or false) if
they fail.
• Several functions are helpful in graceful failure
– die(string) - halts and displays the string
– mysql_errno() - returns number of error
– mysql_error() - returns text of error
14
Error Handling examples
if (!($connection =
mysql_connect("localhost",$name,$passwd)))
die("Could not connect");
function showerror()
{
die("Error " . mysql_errno() . " : " . mysql_error());
}
if (!(mysql_select_db("winestor", $connection)))
showerror();
15
PHP Create a MySQL Database
• Grant special CREATE privileges to create or to delete a MySQL
database.
• The CREATE DATABASE statement is used to create a database
in MySQL.
// 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);
16
?>
Building a Query
• Directly
– $query = 'select * from wines’;
• Using input information
– $winery = $_POST['winery'];
– $query = “select * from students where
name=$winery”;
17
PHP MySQL Select Data
• The SELECT statement is used to select data
from one or more tables
• SELECT column_name(s) FROM table_name
18
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. "
" . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
19
PHP MySQL Select Data
• the function num_rows() checks if there are
more than zero rows returned.
• If there are more than zero rows returned, the
function fetch_assoc() puts all the results into
an associative array that we can loop through.
The while() loop loops through the result set
20
Running a Query
• mysql_query returns a result handle
– $result = mysqli_query($query, $connection)
• mysql_num_rows indicates the number of rows
returned
– $num_rows = mysqli_num_rows($result)
• mysql_fetch_array creates array/hash of result
For ($n=0; $n<$num_rows;$n++)
$row = mysqli_fetch_array($result)
21
Printing the Complete Row
• By number
for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
• By field
echo $row['surname'] . ' ' . $row['city'];
22
PHP MySQL Create Table
• The CREATE TABLE statement is used to create a table
in MySQL.
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
)
23
// 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);
?>
24
Inserting Into a Database
• Collect data from a form
• Validate data (JavaScript, PHP or both)
• Create a query
• Run the query
– mysql_query($query, $db);
25
Inserting Into a Database
$sql = "INSERT INTO MyGuests (firstname,
lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" .
mysqli_error($conn);
}
mysqli_close($conn);
?>
26
Inserting Into a Database
• Multiple SQL statements must be executed
with the mysqli_multi_query() function.
27
Prepared Statements
• The MySQL database supports prepared statements.
• A prepared statement or a parameterized statement is used
to execute the same statement repeatedly with high
efficiency and protect against SQL injections.
• The prepared statement execution consists of two stages:
– prepare
• At the prepare stage a statement template is sent to the database
server
– execute
• During execute the client binds parameter values and sends them to
the server
28
Prepared Statements
• The MySQL server supports using anonymous,
positional placeholder with ?.
• The server performs a syntax check and
initializes server internal resources for later
use.
29
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label
TEXT)");
/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label)
VALUES (?, ?)");
/* Prepared statement, stage 2: bind and execute */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" means that
$id is bound as an integer and $label as a string
$stmt->execute();
30
MySQL Limit Data Selections
• MySQL provides a LIMIT clause that is used to specify the number of records to
return.
• The LIMIT clause makes it easy to code multi page results or pagination with SQL,
and is very useful on large tables. Returning a large number of records can impact
on performance.
• Assume we wish to select all records from 1 - 30
– $sql = "SELECT * FROM Orders LIMIT 30";
• Assume we wish to select all records from 10 – 20 (use OFFSET)
– $sql = "SELECT * FROM Orders LIMIT 10 OFFSET 20";
– $sql = "SELECT * FROM Orders LIMIT 20, 10"; //reverse the numbers with comma
31
Stored Procedures
• The MySQL database supports stored procedures.
• A stored procedure is a subroutine stored in the database
catalog.
• Applications can call and execute the stored procedure.
The CALL SQL statement is used to execute a stored
procedure.
• Stored procedures can have IN, INOUT and OUT
parameters, depending on the MySQL version. The
mysqli interface has no special notion for the different
kinds of parameters.
32
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query("CREATE PROCEDURE p(IN id_val INT)
BEGIN INSERT INTO test(id) VALUES(id_val); END;");
$mysqli->query("CALL p(1)");
$result = $mysqli->query("SELECT id FROM test");
var_dump($result->fetch_assoc());
array(1) {
["id"]=>
string(1) "1"
} 33
• https://www.php.net/manual/en/mysqli.quick
start.stored-procedures.php
34
Any Questions?
35
