Module 4 Php and Mysql

8/8/2019 Module 4 Php and Mysql

1/119

1

PHP and MySQL

2 weeks coverage


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


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)


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


Day6:

PHP Secure E-mail

PHP Error

PHP Exception

PHP Filter

Create php file called txt and

storethefollowing parameters

ofemployeename, IdNo,

PayRollNo andAge


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


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)


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)


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/119


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


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 (


11/119

11

Comments in PHP

In PHP, we use // to make a single-line comment or /* and */ to make alarge comment block.


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:


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.


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.


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).


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.


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


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


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


20/119

20

Logical Operators


&& 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


21/119

21

Logical Operators


&& 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


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


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:


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;


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


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;

}


27/119

27

Example

y


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";


29/119

29

Example

y In the following example you access the variablevalues by referring to the array name and index:

y


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


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;}


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


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);


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


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 )


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;}


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


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


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)


40/119

40

Exampley A simple function that writes my name when it is called:

y


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


42/119

42

Exampley The following example will write different first names, but equal last name:

y


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


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.


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":


46/119

46

"welcome.php" looks like this:

y

Welcome !
You are years old.


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)


48/119

48

Example

y

Name: Age:


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!


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).


51/119

51

Example

y

Name: Age:


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


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.


54/119

54

Day4:

PHP Date

PHP Include

PHP FileAssignment: creation of php page to

display the current date ( 3 marks)


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


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


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


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


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


60/119

60

Closing a File

y The fclose() function is used to close anopen file:

y


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";


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


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


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


65/119


66/119

66

Create The Upload Script

y The "upload_file.php" file contains the code for uploading a file:

y


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.


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


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


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";}

?>


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.


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


73/119

73

Storing a Session Variabley The correct way to store and retrieve session variables is to use the

PHP $_SESSION variable:

y


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


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


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


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


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.


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()


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


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


82/119

82

PHP Exception Handling

y Exceptions are used to change the normalflow of a script if a specified error occurs


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.


84/119

84

Example

y


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


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


87/119

87

example

y


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


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


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".


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


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,


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);


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


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


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


97/119

97

Example

y The following example creates a database called "my_db":

y


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:


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,...)


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


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:


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


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


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


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


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


107/119

107

ExampleyThe following example selects all rows from the "Persons" table where"FirstName='Peter':

y


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


109/119

109

example

y The following example updates some data in the "Persons" table:y


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


111/119

111

Example of delete

y The following example deletes all the records in the "Persons"table where LastName='Griffin':

y


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


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


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


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


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


117/119

117

g

y The odbc_close() function is used toclose an ODBC connection.

y odbc_close($conn);

An ODBC Example


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


119/119

xEnd!

Module 4 Php and Mysql

Documents

Transcript of Module 4 Php and Mysql