Transcript PHP Part 2 - WordPress.com

PHP Part 2
(1)
Client – Server Model
Client Browser
Web Server
HTTP Request
index.php
Render HTML
&CSS
Run JavaScript
(2)
HTTP Response
Process
PHP script
Splitting PHP Code
PHP code does not have to be contiguous
<?php
if ($x<5) {
?>
<p>There are less than five items.</p>
<?php
}
?>
This is very powerful, yet can be hard to read
(3)
PHP and Databases
PHP has built in support for over 20 databases
• Both SQL and NoSQL
http://www.php.net/manual/en/refs.database.vend
ors.php
We’ll be using MySQL an Open Source RDBMS
(4)
PHP MySQL Extension
MySQL Improved
• Both procedural and Object Oriented
- $mysqli = mysqli_connect(“example.com”,
“user”, “password”, “database);
$res = mysqli_query($mysqli, “SELECT *
FROM People”);
- $mysqli = new mysqli(“example.com”,
“user”, “password”, “database);
$res = $mysqli->query(“SELECT * FROM
People”);
(5)
Connecting to DB
$mysqli = new mysqli(“localhost”, “user”, “passwd”,
“database”);
if ($mysqli->connect_errno) {
echo “Failed to connect to MySQL: (“ . $mysqli>connect_errno . “) “ . $mysqli->connect_error;
}
(6)
Executing SQL Statements
$res = $mysql->query(SQL);
if (!mysqli->query(“DROP TABLE IF EXISTS test”) ||
!mysqli->query(“CREATE TABLE test(id INT)”) ||
!mysqli->query(“INSERT INTO test(id) VALUES (1)”)) {
echo “Table creation failed: (“ . $mysqli->errno . “) “ . $mysqli>error;
}
Drops the table test,
Creates a table test with one column id,
Inserts a row with the value id==1
(7)
Important SQL Commands
SELECT – extracts data
UPDATE – updates data
DELETE – deletes data
INSERT INTO – inserts new data
CREATE DATABASE – creates new database
ALTER DATABASE – modifies database
CREATE TABLE – creates new table
ALTER TABLE – modifies table
DROP TABLE – deletes table
(8)
PHP Select Query
Selects records from a table
SELECT col1,col2,… FROM table
$res = $mysqli->query(“SELECT name, age
FROM People”);
while ($row = $res->fetch_assoc()) {
echo “Name: “ . $row[‘name’] . “ is “ .
$row[‘age’];
}
$res->free();
(9)
Select Query
SELECT col1,col2,… FROM table WHERE col
oper value [AND | OR] col oper value
Filters the records returned
Operators:
• =, <>, >, <, >=, <=, BETWEEN, LIKE, IN
(10)
SELECT ORDER BY
Orders the records returned
SELECT col1,col2,…
FROM table
ORDER BY col1,col2,… ASC|DESC
$res = $mysqli->query(“SELECT * FROM Persons
ORDER BY age”);
(11)
INSERT Query
Inserts a record into the table
INSERT INTO table (col1,col2,…) VALUES
(val1,val2,…)
• Column names are optional
• Must have a value for each column
$res = $mysqli->query(“INSERT INTO test
VALUES (1, ‘fred’)”);
(12)
UPDATE Query
Updates record(s) in the table
UPDATE table SET col1=val1,col2=val2,…
WHERE some_col=some_val
WHERE clause can have AND OR statements
WHERE clause chooses which records to change
$res = $mysqli->query(“UPDATE test SET
name=‘fred’ WHERE id=3”);
(13)
DELETE Query
Deletes records from a table
DELETE FROM table WHERE
some_col=some_val
$res = $mysqli->query(“DELETE FROM test
WHERE name=‘fred’”);
(14)
SQL Injection
It is common to allow web users to input their
own values
<?php
$stmt = “SELECT * FROM Users WHERE id = “ .
$_POST[‘user_id’];
?>
What if they typed ‘3 or 1=1’?
SELECT * FROM Users WHERE id = 3 or 1=1
What if they typed ‘5; DROP TABLE Sales’?
(15)
Solution Use Prepared Statements
Prepared statements have place holders ‘?’
They are bound before execution
<?php
if(!($stmt = $mysqli->prepare(“INSERT INTO test(id) VALUES (?)”))) {
echo “Prepare failed”;
}
$id = 2;
if (!stmt->bind_param(“i”, $id)) {
echo “Bind failed”;
}
for($id = 1; $id < 5; $id++) {
if (!stmt->execute()) {
echo “Execute failed”;
}
}
$stmt->close();
?>
(16)
(17)