chapters 9 15 18 & PHP intro

burnwholeInternet και Εφαρμογές Web

5 Φεβ 2013 (πριν από 4 χρόνια και 7 μήνες)

172 εμφανίσεις

FUNCTIONS AND

STORED PROCEDURES & FUNCTIONS

AND PROTECTING A DB

AND PHP

(
C
hapters 9, 15,
18)

Canned String Functions


Concatenate


Length of a string


Removing leading or trailing spaces


Finding string substrings


Transform string to upper or lower case


Reversing a string


Insert a substring into a string


Right or left padding of a string with a character


Etc.


Canned Numerical Functions


Round


Truncate


Absolute value


Square root


Random value


Etc.

Dates and Times


Now


local date and time from clock


Current timestamp


Currant date


Current time


Extract second, minute, hour, etc.,, from a date


Formatting dates


Adding intervals to dates


Subtract intervals from dates


Returning elapsed period length


Etc.

Specialized Functions


IF (testing an expression for true)


IFNULL (test two expressions and return first or second
based on which
is non
-
null)


COALESCE (test a list of expressions and return first non
-
null)

Two ways to use SQL with an Application


Dynamic SQL


Embedded SQL


Making calls to an SQL based DB

Dynamic SQL


SQL code can be generated at runtime based on
conditions found by the Host application


In particular, to create the appropriate WHERE clauses


The SQL code is put into a Host application string variable


You can use dynamic SQL within a stored procedure


EMBEDDED SQL


SQL is placed inside Host application code


The SQL is processed in a first compilation phase


SQL application uses cursors and INTO statements to
pass values to Host application


Connecting to a DB


PHP:


mysqli

(the
i

is for improved)


or PDO (PHP Data Objects)


Java: there are a set of drivers for the various relational
databases, including MySQL


The JDBC drivers are very popular and a lot of GUIs use them


.NET driver


Protecting a MySQL DB


Create and delete databases


Grant specific access rights to groups of users


Insert, Delete, Select rights on databases


Administration and other users


You can limit a user to access a DB only from a certain IP address


Separate access rights for altering a schema


Create DBs


Add tables


Change tables


Create views


Create
indices


Create triggers



PHP and MySQL

Web Server
Database Server
E
-
mail Server
The Internet
`
Client
`
Client
Continued

Web Server
PHP
Script
Database Server
HTTP request
HTTP response
`
Web Browser
The MVC pattern

Please install…


PHPStorm



for working with PHP


RubyMine



for Ruby on Rails MVC framework


Intellij

IDEA


for Grails
MVC framework

PHPStorm

& Textbook’s examples


Please install
PHPStorm


Go
to
http://
murach.com/servlet/murach.downloads.DownloadS
ervlet?file=phps_allfiles.zip


Login


Download zip file


Open
PHP_allfiles


Make the guitar shop
db


Put the project under the
xampp

(or other) doc root


Run
http://localhost/textbook/


PHP & Textbook, continued


Open the index file with
PHPStorm


See page 17 of text


See HTML


See link tag for the CSS file


See the sets of label and input tags that create input boxes


Notice content, data, and buttons in the div tags, which are part of
the CSS code


Open main.css with
PHPStorm
, to see CSS


The PHP File


Open
display_discount.php

file with
PHPStorm


Notice that


The PHP retrieves values from text box, product description, list
price, and discount price ($_POST is an array)


This is a built in array


The http POST method is defined in the previous html page


The PHP code calculates the Discount


Notice that PHP is converting strings to integers


The bottom part of the PHP file uses PHP echo commands to
display
the values


Example: PHP embedded in HTML


<?
php



// get the data from the request



$
first_name

= $_GET['
first_name
'];



$
last_name

= $_GET['
last_name
'];


?>


<!DOCTYPE html PUBLIC "
-
//W3C//DTD XHTML 1.0 Transitional



...>


<html
xmlns
="http://www.w3.org/1999/xhtml">



<head>



<title>Name Test</title>




<link
rel
="
stylesheet
" type="text/
css
"



href
="main.css"/>



</head>



<body>



<h2>Welcome</h2>



<p>First name: <?
php

echo $
first_name
; ?></p>



<p>Last name: <?
php

echo $
last_name
; ?></p>



</body>


</html>