module 4 php and mysql
TRANSCRIPT
-
8/8/2019 Module 4 Php and Mysql
1/119
1
PHP and MySQL
2 weeks coverage
-
8/8/2019 Module 4 Php and Mysql
2/119
2
Course outline3 Days:
1. PHP IntroductionPHP SyntaxPHP VariablesPHP StringPHP OperatorsPHP If...ElsePHP SwitchPHP ArraysPHP While Loops
PHP For LoopsPHP FunctionsPHP FormsPHP $_GETPHP $_POST
3 Days
2. PHP Advanced
PHP DatePHP IncludePHP FilePHP File UploadPHP CookiesPHP SessionsPHP E-mailPHP Secure E-mailPHP ErrorPHP ExceptionPHP Filter
2 Days
3. PHP Database
MySQL IntroductionMySQL ConnectMySQL CreateMySQL InsertMySQL SelectMySQL Where
MySQL Order ByMySQL UpdateMySQL DeletePHP ODBC
-
8/8/2019 Module 4 Php and Mysql
3/119
3
y Topics to be covered in the first three days
Day1:PHP Introduction
PHP Syntax
PHP Variables
PHP StringAssignment: creationofweb
pageto display studentnameand agesubmitthesame day ( 3
marks)
Day 2: PHP Operators
PHP If...Else
PHP Switch* PHP Arrays
PHP While Loops* studentsto read onthereown
and presentthefollowing day
Day3: PHP For
*Loops
PHP
*Functions
PHP Forms
PHP $_GET
PHP $_POST
Assignment: createsimple
program which will determine
thegradeforthestudentsbased onthemarksscored:
Submitbeforeend ofclass
( 5 marks)
-
8/8/2019 Module 4 Php and Mysql
4/119
4
y Topics to be covered in day 4 , 5 and 6
Day4:PHP Date
PHP Include
PHP FileAssignment: creationofphp
pageto display the current date
( 3 marks)
Day 5: PHP File
Upload
PHP Cookies
*PHP SessionsPHP E-mail
* studentsto read onthereown
and presentthefollowing day
Day6:
PHP Secure E-mail
PHP Error
PHP Exception
PHP Filter
Create php file called txt and
storethefollowing parameters
ofemployeename, IdNo,
PayRollNo andAge
-
8/8/2019 Module 4 Php and Mysql
5/119
5
y Topics to be covered on day 7 and 8
Day 7:MySQL Introduction
MySQL Connect
MySQL Create
*MySQL Insert
MySQL Select
Assignment: usingonly SQL
commands create databasecalled Company assets with
threetables
* studentsto read onthereown
and presentthefollowing day
Day 8: MySQL WhereMySQL Order By
*MySQL Update
MySQL Delete
PHP ODBC
Assignment: using databaseyou created insert records andupdate records and deletesome recordsfromthedatabase ( 10 marks)
-
8/8/2019 Module 4 Php and Mysql
6/119
6
Day1:
PHP Introduction
PHP Syntax
PHP Variables
PHP String
Assignment: creation of web page todisplay student name and age submit thesame day ( 3 marks)
-
8/8/2019 Module 4 Php and Mysql
7/119
7
Introduction
What is PHP?
y PHP stands for PHP: Hypertext Preprocessor
y PHP is a server-side scripting language, like ASP
y PHP scripts are executed on the server
y PHP supports many databases (MySQL, Informix, Oracle,Sybase, Solid, PostgreSQL, Generic ODBC, etc.)
y PHP is an open source software
y PHP is free to download and use
What is a PHP File?
y PHP files can contain text, HTML tags and scripts
y PHP files are returned to the browser as plain HTML
y PHP files have a file extension of ".php", ".php3", or ".phtml"
-
8/8/2019 Module 4 Php and Mysql
8/119
-
8/8/2019 Module 4 Php and Mysql
9/119
9
Introduction ContinuedWhy PHP?y PHP runs on different platforms (Windows, Linux, Unix, etc.)
y PHP is compatible with almost all servers used today (Apache, IIS, etc.)
y PHP is FREE to download from the official PHP resource:www.php.net
y PHP is easy to learn and runs efficiently on the server side
Where to Start?y To get access to a web server with PHP support, you can:
y Install Apache (or IIS) on your own server, install PHP, and MySQL
y Or find a web hosting plan with PHP and MySQL support
-
8/8/2019 Module 4 Php and Mysql
10/119
10
Basic PHP SyntaxA PHP scripting block always starts with
.
A PHP scripting block can be placed anywhere in the document.
On servers with shorthand support enabled you can start a scripting block with .
For maximum compatibility, we recommend that you use the standard form (
-
8/8/2019 Module 4 Php and Mysql
11/119
11
Comments in PHP
In PHP, we use // to make a single-line comment or /* and */ to make alarge comment block.
-
8/8/2019 Module 4 Php and Mysql
12/119
12
Variables in PHP
Variables are used for storing a values, like text strings, numbers orarrays.
When a variable is declared, it can be used over and over again inyour script.
All variables in PHP start with a $ sign symbol.
The correct way of declaring a variable in PHP:
$var_name = value;
New PHP programmers often forget the $ sign at the beginning of thevariable. In that case it will not work.
Let's try creating a variable containing a string, and a variablecontaining a number:
-
8/8/2019 Module 4 Php and Mysql
13/119
13
String Variables in PHP
y String variables are used for values that contains characters.
y After we create a string we can manipulate it. A string can be useddirectly in a function or it can be stored in a variable.
y Below, the PHP script assigns the text "Hello World" to a stringvariable called $txt:
The output of the code above will be:
Hello World
y
Now, lets try to use some different functions and operators tomanipulate the string.
-
8/8/2019 Module 4 Php and Mysql
14/119
14
The Concatenation Operator
y There is only one string operator in PHP.
y The concatenation operator (.) is used to put two string valuestogether.
y To concatenate two string variables together, use the concatenationoperator:
T
he output of the code above will be:Hello World! What a nice day!
y If we look at the code above you see that we used theconcatenation operator two times.This is because we had to inserta third string (a space character), to separate the two strings.
-
8/8/2019 Module 4 Php and Mysql
15/119
15
The strlen() function
The strlen() function is used to return the length ofa string.
Let's find the length of a string:
The output of the code above will be:12T
he length of a string is often used in loops orother functions, when it is important to knowwhen the string ends. (i.e. in a loop, we wouldwant to stop the loop after the last character inthe string).
-
8/8/2019 Module 4 Php and Mysql
16/119
16
The strpos() function
The strpos() function is used to search for characterwithin a string.
If a match is found, this function will return the positionof the first match. If no match is found, it will returnFALSE.
Let's see if we can find the string "world" in our string:
The output of the code above will be:6
The position of the string "world" in our string isposition 6.The reason that it is 6 (and not 7), is thatthe first position in the string is 0, and not 1.
-
8/8/2019 Module 4 Php and Mysql
17/119
17
Day 2:
PHP Operators
PHP If...Else
PHP Switch* PHP Arrays
PHP While Loops
* students to read on there own andpresent the following day
-
8/8/2019 Module 4 Php and Mysql
18/119
18
PHP Arithmetic Operators
Operator Description Example Result
+ Addition x=2x+2
4
- Subtraction x=25-x
3
* Multiplication x=4x*5
20
/ Division 15/55/2
32.5
% Modulus (divisionremainder)
5%210%8
10%2
12
0
++ Increment x=5x++
x=6
-- Decrement x=5x--
x=4
-
8/8/2019 Module 4 Php and Mysql
19/119
19
Comparison Operators
Operator Description Example
== is equal to 5==8 returns false
!= is not equal 5!=8 returns true
is not equal 58 returns true
> is greater than 5>8 returns false
< is less than 5= is greater than or equalto
5>=8 returns false
-
8/8/2019 Module 4 Php and Mysql
20/119
20
Logical Operators
Operator Description Example
&& and x=6y=3 (x < 10 && y > 1)
returns true
|| or x=6
y=3 (x==5 || y==5)returns false
! not x=6y=3 !(x==y) returns true
-
8/8/2019 Module 4 Php and Mysql
21/119
21
Logical Operators
Operator Description Example
&& and x=6y=3 (x < 10 && y > 1)
returns true
|| or x=6
y=3 (x==5 || y==5)returns false
! not x=6y=3 !(x==y) returns true
-
8/8/2019 Module 4 Php and Mysql
22/119
22
Conditional statementsy Conditional statements are used to perform different actions based
on different conditions.
y Conditional Statements
y Very often when you write code, you want to perform differentactions for different decisions.
y
You can use conditional statements in your code to do this.y In PHP we have the following conditional statements:
y if statement - use this statement to execute some code only if aspecified condition is true
y if...else statement - use this statement to execute some code if acondition is true and another code if the condition is false
y if...elseif....else statement - use this statement to select one ofseveral blocks of code to be executed
y switch statement - use this statement to select one of manyblocks of code to be executed
-
8/8/2019 Module 4 Php and Mysql
23/119
23
The if Statement
y Use the if statement to execute some code only if a specifiedcondition is true.
y Syntax
if (condition) code to be executed if condition is true;The following
example will output "Have a nice weekend!" if the current day isFriday:
-
8/8/2019 Module 4 Php and Mysql
24/119
24
The if...else Statement
y Use the if....else statement to executesome code if a condition is true and
another code if a condition is false.
y Syntax
y if (condition)
code to be executed if condition is true;
elsecode to be executed if condition is false;
-
8/8/2019 Module 4 Php and Mysql
25/119
25
Example
y The following example will output "Have a nice weekend!" if the
current day is Friday, otherwise it will output "Have a nice day!":y
-
8/8/2019 Module 4 Php and Mysql
26/119
26
Switchy Conditional statements are used to perform different actions based
on different conditions.
y The PHP Switch Statement
y Use the switch statement to select one of many blocks of code tobe executed.
y
Syntaxy switch (n)
{case label1:code to be executed if n=label1;break;
case label2:
code to be executed if n=label2;break;
default:code to be executed if n is different from both label1 and label2;
}
-
8/8/2019 Module 4 Php and Mysql
27/119
27
Example
y
-
8/8/2019 Module 4 Php and Mysql
28/119
28
PHP Arrays
y An array stores multiple values in one single variable.
y What is an Array?
y A variable is a storage area holding a number or text.The problem is, a variable will hold only one value.
y An array is a special variable, which can store multiplevalues in one single variable.
y If you have a list of items (a list of car names, forexample), storing the cars in single variables could looklike this:
y $cars1="Saab";$cars2="Volvo";$cars3="BMW";
-
8/8/2019 Module 4 Php and Mysql
29/119
29
Example
y In the following example you access the variablevalues by referring to the array name and index:
y
-
8/8/2019 Module 4 Php and Mysql
30/119
30
While loop
y PHP Loops
y Often when you write code, you want the same blockof code to run over and over again in a row.
y In PHP, we have the following looping statements:
y while - loops through a block of code while a specifiedcondition is true
y do...while - loops through a block of code once, andthen repeats the loop as long as a specified condition istrue
y for- loops through a block of code a specified numberof times
y foreach - loops through a block of code for eachelement in an array
-
8/8/2019 Module 4 Php and Mysql
31/119
31
Syntax
y The while Loop
y The while loop executes a block of code
while a condition is true.
y Syntax
y while (condition){
code to be executed;}
-
8/8/2019 Module 4 Php and Mysql
32/119
32
Example
y The example below defines a loop that starts with i=1.The loopwill continue to run as long as i is less than, or equal to 5. i willincrease by 1 each time the loop runs:
y
-
8/8/2019 Module 4 Php and Mysql
33/119
33
The do...while Statement
y The do...while statement will always execute theblock of code once, it will then check thecondition, and repeat the loop while thecondition is true.
y Syntaxy do
{code to be executed;
}y
while (condition);
-
8/8/2019 Module 4 Php and Mysql
34/119
34
Exampley The example below defines a loop that starts with i=1. It will then
increment i with 1, and write some output.Then the condition is checked,and the loop will continue to run as long as i is less than, or equal to 5:
y
-
8/8/2019 Module 4 Php and Mysql
35/119
35
Day3:
y PHP Fory *Loops
PHPy *Functionsy PHP Formsy PHP $_GETy PHP $_POST
y Assignment: create simple program which willdetermine the grade for the students based onthe marks scored: Submit before end of class
y ( 5 marks )
-
8/8/2019 Module 4 Php and Mysql
36/119
36
The for Loop
The for loop is used when you know inadvance how many times the script
should run.
y Syntaxy for (init; condition; increment)
{
code to be executed;}
-
8/8/2019 Module 4 Php and Mysql
37/119
37
Parameters:
y init: Mostly used to set a counter
y condition: Evaluated for each loop
iteration. If it evaluates toTRUE, the loop
continues. If it evaluates to FALSE, theloop ends.
y increment: Mostly used to increment a
counter
-
8/8/2019 Module 4 Php and Mysql
38/119
38
Exampley The example below defines a loop that starts with i=1.
The loop will continue to run as long as i is less than, orequal to 5. i will increase by 1 each time the loop runs:
y
-
8/8/2019 Module 4 Php and Mysql
39/119
39
Create a PHP Function
y A function will be executed by a call to thefunction.
y Syntaxy function functionName()
{
code to be executed;}y PHP function guidelines:y Give the function a name that reflects what the
function doesy The function name can start with a letter or
underscore (not a number)
-
8/8/2019 Module 4 Php and Mysql
40/119
40
Exampley A simple function that writes my name when it is called:
y
-
8/8/2019 Module 4 Php and Mysql
41/119
41
PHP Functions - Adding parameters
y
To add more functionality to a function,we can add parameters. A parameter is
just like a variable.
y Parameters are specified after thefunction name, inside the parentheses
-
8/8/2019 Module 4 Php and Mysql
42/119
42
Exampley The following example will write different first names, but equal last name:
y
-
8/8/2019 Module 4 Php and Mysql
43/119
43
PHP Functions - Return values
y
To let a function return a value, use the return statement.y Example
y
Output:
y 1 + 16 = 17
-
8/8/2019 Module 4 Php and Mysql
44/119
44
PHP Forms and User Input
y
The PHP $_GET and $_POST variablesare used to retrieve information from
forms, like user input.
y PHP Form Handling
y The most important thing to notice whendealing with HTML forms and PHP is that
any form element in an HTML page will
automatically be available to your PHPscripts.
-
8/8/2019 Module 4 Php and Mysql
45/119
45
Example
y The example below contains an HTML form with two input fieldsand a submit button:
y
Name: Age:
y When a user fills out the form above and click on the submitbutton, the form data is sent to a PHP file, called "welcome.php":
-
8/8/2019 Module 4 Php and Mysql
46/119
46
"welcome.php" looks like this:
y
Welcome !
You are years old. -
8/8/2019 Module 4 Php and Mysql
47/119
47
PHP $_GET Function
y The built-in $_GET function is used to collectvalues in a form with method="get".
y The $_GET Function
y The built-in $_GET function is used to collectvalues from a form sent with method="get".
y Information sent from a form with the GETmethod is visible to everyone (it will be
displayed in the browser's address bar) and haslimits on the amount of information to send(max. 100 characters)
-
8/8/2019 Module 4 Php and Mysql
48/119
48
Example
y
Name: Age:
-
8/8/2019 Module 4 Php and Mysql
49/119
49
Explanation
y The "welcome.php" file can now use the $_GETfunction to collect form data (the names of the formfields will automatically be the keys in the $_GET array):
y Welcome .
You are years old!
y When to use method="get"?
y When using method="get" in HTML forms, all variablenames and values are displayed in the URL.
y Note:This method should not be used when sendingpasswords or other sensitive information!
-
8/8/2019 Module 4 Php and Mysql
50/119
50
HP $_POST Function
y The built-in $_POST function is used to collectvalues in a form with method="post".
y The $_POST Function
y The built-in $_POST function is used to collectvalues from a form sent with method="post".
y Information sent from a form with the POSTmethod is invisible to others and has no limitson the amount of information to send.
y Note: However, there is an 8 Mb max size forthe POST method, by default (can be changedby setting the post_max_size in the php.ini file).
-
8/8/2019 Module 4 Php and Mysql
51/119
51
Example
y
Name: Age:
-
8/8/2019 Module 4 Php and Mysql
52/119
52
Explanation
y The "welcome.php" file can now use the $_POSTfunction to collect form data (the names of the formfields will automatically be the keys in the $_POSTarray):
y
Welcome !
You are years old.y When to use method="post"?
y Information sent from a form with the POST method is
invisible to others and has no limits on the amount ofinformation to send.
y However, because the variables are not displayed
-
8/8/2019 Module 4 Php and Mysql
53/119
53
The PHP $_REQUEST Function
y T
he PHP built-in $_REQUEST
function containsthe contents of both $_GET, $_POST, and$_COOKIE.
y The $_REQUEST function can be used tocollect form data sent with both the GET and
POST methods.y Exampley Welcomey !y
You are years old. -
8/8/2019 Module 4 Php and Mysql
54/119
54
Day4:
PHP Date
PHP Include
PHP FileAssignment: creation of php page to
display the current date ( 3 marks)
-
8/8/2019 Module 4 Php and Mysql
55/119
55
The PHP Date() Function
y The PHP date() function formats a timestampto a more readable date and time.
y A timestamp is a sequence of characters,
denoting the date and/or time at which acertain event occurred.
y Syntax
y date(format,timestamp)
y timestamptimestampOptional. Specifies atimestamp.Default is the current date and time
-
8/8/2019 Module 4 Php and Mysql
56/119
56
HP Date() - Format the Date
y The required format parameter in the date()function specifies how to format the date/time.
y Here are some characters that can be used:y d - Represents the day of the month (01 to 31)
y m - Represents a month (01 to 12)y Y - Represents a year (in four digits)
y
-
8/8/2019 Module 4 Php and Mysql
57/119
57
PHP File Handling
y The fopen() function is used to open files in PHP.
Opening a Filey The fopen() function is used to open files in PHP.
y The first parameter of this function contains the name of the file tobe opened and the second parameter specifies in which mode thefile should be opened:
y
-
8/8/2019 Module 4 Php and Mysql
58/119
58
The file may be opened in one of the following modes:
Modes Descriptionr Read only. Starts at the beginning of the file
r+ Read/Write. Starts at the beginning of the file
w Write only. Opens and clears the contents of file; or creates a new file
if it doesn't exist
w+ Read/Write. Opens and clears the contents of file; or creates a new fileif it doesn't exist
a Append. Opens and writes to the end of the file or creates a new file ifit doesn't exist
a+ Read/Append. Preserves file content by writing to the end of the file
x Write only. Creates a new file. Returns FALSE and an error if file
already exists
x+ Read/Write. Creates a new file. Returns FALSE and an error if file
already exists
-
8/8/2019 Module 4 Php and Mysql
59/119
59
Fopen()
y Note: If the fopen() function is unable to open thespecified file, it returns 0 (false).
y Example
y The following example generates a message if thefopen() function is unable to open the specified file:
y
-
8/8/2019 Module 4 Php and Mysql
60/119
60
Closing a File
y The fclose() function is used to close anopen file:
y
-
8/8/2019 Module 4 Php and Mysql
61/119
61
Check End-of-file
y The feof() function checks if the "end-of-file" (EOF) has been reached.
The feof() function is useful for loopingthrough data of unknown length.
yNote:You cannot read from files opened
in w, a, and x mode!
y if (feof($file)) echo "End of file";
-
8/8/2019 Module 4 Php and Mysql
62/119
62
Reading a File Line by Line
y The fgets() function is used to read a single line from a file.
y Note:After a call to this function the file pointer has moved to thenext line.
y Example
y The example below reads a file line by line, until the end of file isreached:
y
-
8/8/2019 Module 4 Php and Mysql
63/119
63
Reading a File Character by Character
y The fgetc() function is used to read a single character from a file.
y Note:After a call to this function the file pointer moves to thenext character.
y Example
y The example below reads a file character by character, until theend of file is reached:
y
-
8/8/2019 Module 4 Php and Mysql
64/119
64
Day 5:
PHP File Upload
PHP Cookies*PHP Sessions
PHP E-mail
* students to read on there own and
present the following day
-
8/8/2019 Module 4 Php and Mysql
65/119
-
8/8/2019 Module 4 Php and Mysql
66/119
66
Create The Upload Script
y The "upload_file.php" file contains the code for uploading a file:
y
-
8/8/2019 Module 4 Php and Mysql
67/119
67
Upload to servery By using the global PHP $_FILES array you can upload files from a
client computer to the remote server.
y The first parameter is the form's input name and the second indexcan be either "name", "type", "size", "tmp_name" or "error". Likethis:
y $_FILES["file"]["name"] - the name of the uploaded file
y $_FILES["file"]["type"] - the type of the uploaded filey $_FILES["file"]["size"] - the size in bytes of the uploaded file
y $_FILES["file"]["tmp_name"] - the name of the temporary copy ofthe file stored on the server
y $_FILES["file"]["error"] - the error code resulting from the fileupload
y This is a very simple way of uploading files. For security reasons,you should add restrictions on what the user is allowed to upload.
-
8/8/2019 Module 4 Php and Mysql
68/119
68
Restrictions on Upload
y In this script we add some restrictions to the file upload.The user may only upload .gif or .jpegfiles and the file size must be under 20 kb:
y
-
8/8/2019 Module 4 Php and Mysql
69/119
69
Saving the Uploaded File
y The examples above create a temporary copy of the uploaded files in thePHP temp folder on the server.
y The temporary copied files disappears when the script ends.To store theuploaded file we need to copy it to a different location:
y
-
8/8/2019 Module 4 Php and Mysql
70/119
70
conty if (file_exists("upload/" . $_FILES["file"]["name"]))
{echo $_FILES["file"]["name"] . " already exists. ";}
else{move_uploaded_file($_FILES["file"]["tmp_name"],"upload/" . $_FILES["file"]["name"]);echo "Stored in: " . "upload/" . $_FILES["file"]["name"];}
}}
else{echo "Invalid file";}
?>
-
8/8/2019 Module 4 Php and Mysql
71/119
71
PHP Sessions
y A PHP session variable is used to storeinformation about, or change settings for
a user session. Session variables hold
information about one single user, and areavailable to all pages in one application.
-
8/8/2019 Module 4 Php and Mysql
72/119
72
Starting a PHP Session
y Before you can store user information in yourPHP session, you must first start up the session.
y Note:The session_start() function must appearBEFORE the tag:
y
-
8/8/2019 Module 4 Php and Mysql
73/119
73
Storing a Session Variabley The correct way to store and retrieve session variables is to use the
PHP $_SESSION variable:
y
-
8/8/2019 Module 4 Php and Mysql
74/119
74
example
y In the example below, we create a simple page-viewscounter.The isset() function checks if the "views"variable has already been set. If "views" has been set, wecan increment our counter. If "views" doesn't exist, wecreate a "views" variable, and set it to 1:
y
-
8/8/2019 Module 4 Php and Mysql
75/119
75
Destroying a Session
y If you wish to delete some session data, you can
use the unset() or the session_destroy()function.y The unset() function is used to free the
specified session variable:y You can also completely destroy the sessionby calling the session_destroy() function:
y
-
8/8/2019 Module 4 Php and Mysql
76/119
76
PHP Sending E-mails
y PHP allows you to send e-mails directlyfrom a script.
y The PHP mail() Function
PHP Simple E-Mail
-
8/8/2019 Module 4 Php and Mysql
77/119
77
PHP Simple E Mail
y The simplest way to send an email with PHP is to senda text email.
y In the example below we first declare the variables ($to,$subject, $message, $from, $headers), then we use thevariables in the mail() function to send an e-mail:
y
-
8/8/2019 Module 4 Php and Mysql
78/119
78
PHP Error Handling
y The default error handling in PHP is very simple.An error message with filename, line numberand a message describing the error is sent tothe browser.
y PHP Error Handling
y When creating scripts and web applications,error handling is an important part. If your code
lacks error checking code, your program maylook very unprofessional and you may be opento security risks.
-
8/8/2019 Module 4 Php and Mysql
79/119
79
Day6:
y PHP Secure E-mail
y PHP Error
y PHP Exception
y PHP Filter
y Create php file called txt and store thefollowing parameters of employee name,
IdNo, PayRollNo and Age
Basic Error Handling: Using the die()
-
8/8/2019 Module 4 Php and Mysql
80/119
80
Basic Error Handling: Using the die()
function
y The first example shows a simple script thatopens a text file:
y If the file does not exist you might get anerror like this:
yWarning:
y fopen(welcome.txt) [function.fopen]: failed toopen stream:no such file or directory inC:\webfolder\test.php on line 2
-
8/8/2019 Module 4 Php and Mysql
81/119
81
Error handling
y To avoid that the user gets an error messagelike the one above, we test if the file existbefore we try to access it:
y
-
8/8/2019 Module 4 Php and Mysql
82/119
82
PHP Exception Handling
y Exceptions are used to change the normalflow of a script if a specified error occurs
-
8/8/2019 Module 4 Php and Mysql
83/119
83
Basic Use of Exceptions
y When an exception is thrown, the code
following it will not be executed, and PHP
will try to find the matching "catch" block.y If an exception is not caught, a fatal error
will be issued with an "Uncaught
Exception" message.
-
8/8/2019 Module 4 Php and Mysql
84/119
84
Example
y
-
8/8/2019 Module 4 Php and Mysql
85/119
85
HP Filter
y PHP filters are used to validate and filter datacoming from insecure sources, like user input.
yWhat is a PHP Filter?
y
A PHP filter is used to validate and filter datacoming from insecure sources.
y To test, validate and filter user input or customdata is an important part of any web
application.y The PHP filter extension is designed to make
data filtering easier and quicker.
Functions and Filters
-
8/8/2019 Module 4 Php and Mysql
86/119
86
Functions and Filters
y To filter a variable, use one of the followingfilter functions:
y filter_var() - Filters a single variable with aspecified filter
y
filter_var_array() - Filter several variables withthe same or different filters
y filter_input - Get one input variable and filter it
y filter_input_array - Get several input variables
and filter them with the same or different filters
-
8/8/2019 Module 4 Php and Mysql
87/119
87
example
y
-
8/8/2019 Module 4 Php and Mysql
88/119
88
Options and Flags
y Options and flags are used to add additional fil tering options to the specified filters.
y Different filters have different options and flags.
y In the example below, we validate an integer using the filter_var() and the "min_range" and "max_range" options:
y
-
8/8/2019 Module 4 Php and Mysql
89/119
89
Day 7:
y MySQL Introductiony MySQL Connect
y MySQL Create* MySQL Insert
y MySQL Select
y Assignment: using only SQL commands createdatabase called Company assets with three
tablesy * students to read on there own and present
the following day
-
8/8/2019 Module 4 Php and Mysql
90/119
90
PHP MySQL Introduction
What is MySQL?y MySQL is the most popular open-source
database system.
y The data in MySQL is stored in database objects calledtables.
y A table is a collections of related data entries and itconsists of columns and rows.
y
Databases are useful when storing informationcategorically. A company may have a database with thefollowing tables: "Employees", "Products", "Customers"and "Orders".
-
8/8/2019 Module 4 Php and Mysql
91/119
91
DatabaseTables
Database Tables
A database most often contains one or more tables. Each table is identified by a name
(e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
Below is an example of a table called "Persons":
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 SandnesPettersen Kari Storgt 20 Stavanger
-
8/8/2019 Module 4 Php and Mysql
92/119
92
Queries
y A query is a question or a request.
y With MySQL, we can query a database forspecific information and have a recordsetreturned.
y query
y SELECT LastName FROM Persons
y The query above selects all the data in the"LastName" column from the "Persons" table,
-
8/8/2019 Module 4 Php and Mysql
93/119
93
Connect to Database
y PHP MySQL Connect to a Database
y Create a Connection to a MySQL
Database
y Before you can access data in a database, youmust create a connection to the database.
y This is done with the mysql_connect() function.
y Syntax
y mysql_connect(servername,username,password);
-
8/8/2019 Module 4 Php and Mysql
94/119
94
example
y In the following example we store the connection in avariable ($con) for later use in the script.The "die" partwill be executed if the connection fails:
y
-
8/8/2019 Module 4 Php and Mysql
95/119
95
Closing a Connection
y The connection will be closed automatically when thescript ends.To close the connection before, use themysql_close() function:
y
-
8/8/2019 Module 4 Php and Mysql
96/119
96
Create Database
y PHP MySQL Create Database andTables
y A database holds one or multiple tables.
y Create a Database
y The CREATE DATABASE statement isused to create a database in MySQL.
y Syntaxy CREATE DATABASE database_name
Example
-
8/8/2019 Module 4 Php and Mysql
97/119
97
Example
y The following example creates a database called "my_db":
y
-
8/8/2019 Module 4 Php and Mysql
98/119
98
Insert
y PHP MySQL Insert Intoy The INSERT INTO statement is used to insert
new records in a table.y Insert Data Into a Database Table
y The INSERT INTO statement is used to addnew records to a database table.y Syntaxy It is possible to write the INSERT INTO
statement in two forms.y The first form doesn't specify the column
names where the data will be inserted, onlytheir values:
-
8/8/2019 Module 4 Php and Mysql
99/119
99
Syntax of insert
y INSERT INTO table_nameVALUES (value1, value2, value3,...)The
second form specifies both the column
names and the values to be inserted:y INSERT INTO table_name (column1,
column2, column3,...)
V
ALUES (value1, value2, value3,...)
-
8/8/2019 Module 4 Php and Mysql
100/119
100
Assignment
y Create Database named eMobilis withthree tables namely student records,
parentRecords and college assets. Using
SQL commands add a list of 10 studentsto the database
-
8/8/2019 Module 4 Php and Mysql
101/119
101
Insert Data From a Form Into a Database
y Now we will create an HTML form that can be used to add newrecords to the "Persons" table.
y Here is the HTML form:
y
Firstname: Lastname: Age:
-
8/8/2019 Module 4 Php and Mysql
102/119
102
PHP MySQL Select
y The SELECT statement is used to select
data from a database.
y Select Data From a Database Tabley Syntax
y SELECT column_name(s)
FROM table_name
-
8/8/2019 Module 4 Php and Mysql
103/119
103
Example
y The following example selects all the data stored in the "Persons" table (The * character selectsall the data in the table):
y
Display the Result in an HTML Table
-
8/8/2019 Module 4 Php and Mysql
104/119
104
Display the Result in an HTML Table
y The following example selects the same data as the example above, but will display the data in an H
TML table:
y
D 8
-
8/8/2019 Module 4 Php and Mysql
105/119
105
Day 8:
y MySQL Where
y MySQL Order By
*MySQL Update
y
MySQL Deletey PHP ODBC
y Assignment: using database you created insert
records and update records and delete somerecords from the database ( 10 marks )
Wh l
-
8/8/2019 Module 4 Php and Mysql
106/119
106
Where clause
y PHP MySQL The Where Clause
y The WHERE clause is used to filter records.
y TheWHERE clause
y
The WHERE clause is used to extract onlythose records that fulfill a specified criterion.
y Syntax
y SELECT column_name(s)
FROM table_nameWHERE column_name operator value
E l
-
8/8/2019 Module 4 Php and Mysql
107/119
107
ExampleyThe following example selects all rows from the "Persons" table where"FirstName='Peter':
y
-
8/8/2019 Module 4 Php and Mysql
108/119
108
update
y PHP MySQL Updatey The UPDATE statement is used to modify data in a
table.
y Update Data In a Database
y The UPDATE statement is used to update existing
records in a table.y Syntax
y UPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_value
y Note: Notice the WHERE clause in the UPDATEsyntax.The WHERE clause specifies which record orrecords that should be updated. If you omit the WHEREclause, all records will be updated!
l
-
8/8/2019 Module 4 Php and Mysql
109/119
109
example
y The following example updates some data in the "Persons" table:y
-
8/8/2019 Module 4 Php and Mysql
110/119
110
Delete
y The DELETE statement is used to deleterecords in a table.
y Delete Data In a Database
y The DELETE FROM statement is used todelete records from a database table.
y Syntax
y DELETE FROM table_nameWHERE some_column = some_value
E l f d l
-
8/8/2019 Module 4 Php and Mysql
111/119
111
Example of delete
y The following example deletes all the records in the "Persons"table where LastName='Griffin':
y
-
8/8/2019 Module 4 Php and Mysql
112/119
112
PHP Database ODBC
y ODBC is an Application ProgrammingInterface (API) that allows you to connect
to a data source
y Create an ODBC Connectiony With an ODBC connection, you can
connect to any database
ODBCTO MS ACCESS
-
8/8/2019 Module 4 Php and Mysql
113/119
113
ODBCTO MS ACCESS
y Open the Administrative Tools icon in your ControlPanel.
y Double-click on the Data Sources (ODBC) iconinside.
y Choose the System DSN tab.
y Click on Add in the System DSN tab.
y Select the Microsoft Access Driver.ClickFinish.
y In the next screen, clickSelect to locate the database.
y Give the database a Data Source Name (DSN).
y ClickOK.
Connecting to an ODBC
-
8/8/2019 Module 4 Php and Mysql
114/119
114
y The odbc_connect() function is used to connect to anODBC data source.The function takes four parameters:the data source name, username, password, and anoptional cursor type.
y The odbc_exec() function is used to execute an SQL
statement.y Example
y The following example creates a connection to a DSNcalled northwind, with no username and no password. It
then creates an SQL and executes it:y $conn=odbc_connect('northwind','','');
$sql="SELECT * FROM customers";$rs=odbc_exec($conn,$sql);
Retrieving Records
-
8/8/2019 Module 4 Php and Mysql
115/119
115
g
y The odbc_fetch_row() function is used toreturn records from the result-set.This
function returns true if it is able to return
rows, otherwise false.y The function takes two parameters: the
ODBC result identifier and an optional
row number:
y odbc_fetch_row($rs)
Retrieving Fields from a Record
-
8/8/2019 Module 4 Php and Mysql
116/119
116
g
y The odbc_result() function is used to read fieldsfrom a record.This function takes twoparameters: the ODBC result identifier and afield number or name.
y The code line below returns the value of thefirst field from the record:
y $compname=odbc_result($rs,1);The code linebelow returns the value of a field called
"CompanyName":y $compname=odbc_result($rs,"CompanyName"
);
Closing an ODBC Connection
-
8/8/2019 Module 4 Php and Mysql
117/119
117
g
y The odbc_close() function is used toclose an ODBC connection.
y odbc_close($conn);
An ODBC Example
-
8/8/2019 Module 4 Php and Mysql
118/119
118
An ODBC Exampley The following example shows how to first create a database connection, then a result-set, and then display the
data in an HTML table.
y
-
8/8/2019 Module 4 Php and Mysql
119/119
xEnd!