Transcript on PHP, introduction to database

LIS651 lecture 1
PHP basics, database introduction
Thomas Krichel
2005-10-29
today
• PHP part
– we work with
•
•
•
•
text
numbers
Booleans and flow control
arrays
– we reorganize the shop
• Database theory
• Introduction to mySQL.
strings
• a piece of text in PHP is called a string.
• A string is often surrounded by single quotes.
print 'I want beer';
$want='beer';
print 'I want $want';
// prints: I want $want
• If you want to use the values of variables, use
double quotes
$want='beer';
print "I want $want";
// prints: I want beer
single and double quotes
• You can use single quotes to quote double quotes
print 'She wrote: "I want beer." and sighed.';
// prints: She wrote: "I want beer." and sighed.
• and vice versa
print "She wrote: 'I want beer.' and sighed";
// prints: She wrote: 'I want beer.' and sighed.
• Sometimes it is not obvious when to put single
quotes, double quotes, and when to leave them
out. If one thing does not work, try something
else.
the backslash escape
• The backslash is used to quote characters in a
that otherwise are special.
print 'Don\'t give me bad beer!';
$kind='bock';
$beer='Festbock'
print "<p class=\"$kind\">$beer</p>";
// prints: <p class="bock">Festbock</p>
• The backslash itself is quoted as \\
print "a \\ against beer consumption";
// prints: a \ against beer consumption
more backslash escapes
•
•
•
•
\n makes the newline character
\r make the carriage return
(no use in Unix)
\t makes the tab
(seldomly used in HTML)
\$ makes the dollar
(used in the shop)
– $amount='1.50';
– print "you owe \$$amount per bottle.";
– // prints: you owe $1.50 per bottle.
If the backslash was not there $ would be
considered to be a variable.
string functions
• There are a long list of string functions in the PHP
reference manual. When you work with text, you
should look at those string functions at
http://www.php.net/manual/en/ref.strings.php
• Working with text is particularly important when
checking the input of users into your form.
• I am looking at just a few of examples here. You
really need to read the reference to see what is
available.
trim()
• trim(string) removes the whitespace at the
beginning and the end of the string string. It
returns the transformed string.
$input " Festbock ";
$output=trim($input);
print "|$output|"; // prints: |Festbock|
• whitespace is any of the following characters
–
–
–
–
the blank character
the newline
the carriage return
the tabulation character
strlen()
• strlen(string) returns the length of the string string.
$zip=trim($_POST['zipcode']);
$zip_length=strlen($zip);
print $zip_length;
// hopefully, prints 5
strip_tags()
• strip_tagS(string) removes HTML tags from the
string string
$input="<b>But</b>weiser";
print strip_tags($input);
// prints: Butweiser
$in="<a href=\"http://porn.com\"><img
src=\"http://porn.com/ad.gif\"/></a>";
print strip_tags($in);
// prints nothing, hurray!
htmlentities()
• htmlentities(string) makes HTML entities out of
HTML special chars in the string string. <,>,&, and
" are transformed. It returns the transformed
string.
$in="What does the <div> element do?";
print htmlentities($in);
// prints: What does the &lt;div&gt; element do?
addslashes()
• if a variable read from a form, say, for example a
user name Antoine d'Amstel, contains a single or
double quote, this can be very problematic in
certain later treatments. Use addslashes(string) to
add slashes to user input that may contain
quotation characters.
• stripslashes(string) does the opposite.
numbers
• Numbers are set without the use of quotes.
• You can +, -, * and / for the the basic calculations.
• There also is the modulus operator %. It gives the
reminder of the division of the first number by the
second
print 10 % 7;
// prints 3
• Use parenthesis for complicated calculations
$pack=2 * (10 % 7);
print "a $pack pack";
// prints: a 6 pack
other number functions
• abs() calculates the absolute value
print abs(-3)
print abs(3)
// prints: 3
// prints: 3
• The list of functions that use numbers is
http://www.php.net/manual/en/ref.math.php
geeky increment/decrement
• ++ is an operator that adds one. The value of the
resulting expression depends on the position of
the operator
$a=4;
print ++$a;
print $a;
$b=4;
print $b++;
print $b;
// prints: 5
// prints: 5
// prints 4
// prints 5
• -- works in the same way
variable names
• Variable name must start with a letter or
underscore. They can contain letters, digits and
underscores. The following are examples of
illegal names
– $2drunk
– $bottle-content
– $brewer@grosswald
• Variable names are case sensitive. I use
lowercase only and add underscores in long
names.
• It is good to give variables meaningful names.
concatenation
• This is done with the . operator.
$cost=5.23;
$message='This costs ' . $cost;
print $message;
// prints: This costs 5.23
• PHP sees that 5.23 is a number but will treat it as
a string for this purpose.
geeky combined operators
• There are some combined operators that change
a value and set it to the new one. For example
$a+=$b ;
• is the same as
$a=$a+$b;
• Same effect for -=, *=, /=, %=, and .=
$a="I want ";
$b="Balitka 8";
$a.=$b;
echo $a;
// prints: "I want Baltika 8"
Boolean value
• Every expression in PHP has a Boolean value.
• It is either 'true' or 'false'.
• In certain situation, an expression is evaluated as
a Boolean
• For example
if(expression)
expression1 or expression2
what is truth?
• All strings are true except
– the empty string
– the string "0"
• All numbers are true except
–0
– 0.0
• example
$a=5-4-1;
// $a is false
• Note that variables that you have not assigned
contents are false. This includes misspelled
variables!!
comparison operators
• Expressions that are evaluated in Boolean often
use comparison operators.
$beer == 'grosswald' // checks for equality
• note difference from
$beer='grosswald'; // this is always true
• Other comparisons are
< smaller than
> larger than
<= smaller or equal than
>= larger or equal than
logical operators
• 'and' is logical AND. 'or' is logical OR.
if($brand=='Budweiser' or $brand="Sam Adams") {
echo "Commiserations for buying a lousy beer\n";
} # where is the mistake in this piece of code?
• 'not' is Boolean NOT
• These can be combined. Use parenthesis
if((($pints) > 2 and ($vehicle=='car')) or (($pints > 6) and
($vehicle=='bicycle'))) {
print "order a cab!\n";
}
variable types
• Variables in PHP have types. You can check for
types
is_numeric()
is_string()
is_int()
is_float()
• They all return a Boolean value.
• They can be used to check the nature of a
variable.
if() { }
• if(expression) evaluates an expression
expression as Boolean, and executes a block of
code surrounded by curly brackets if the
expression is true.
if($drunk) {
print "Dont\'t drive!\n";
}
• Note you don't need to indent the block as done
above, but the way Thomas has done it there is
pretty much standard, so do it in the same way.
if() {} else {}
• if you have an if() you can add an else block of
code to execute when the condition is false
if($sober) {
print "You can drive\n";
}
else {
print "Check if you are fit to drive\n";
}
elseif() { }
• You can build chain of conditions
if($pints_drunk==0) {
print "You are ok to drive\n";
}
elseif($pints_drunk<3) {
print "Don't use the car, get on your bike\n";
}
elseif($pints_drunk<=6) {
print "Take a cab home\n";
}
else { print "Call the local hospital!\n";
}
while() { }
• while() { } executes a piece of code while the
condition is true
$count=0;
while($count < 100) {
print “ Пиво без водки -- деньги на ветер!<br/>";
$count=$count+1; # don't forget to increment $count!
}
arrays
• The variables we have looked at up until now are
scalars. They can only contain one piece of data.
• Arrays are variables that can contain one that one
piece of data.
– For example, a six pack in conveniently represented as
an array of cans of beer.
– For another example, a class is a group of people,
each having a name, a social security number, etc.
numeric arrays
• An numeric array has key value pairs where the
keys are numbers.
$good_beers[0]="Baltika 8";
$good_beers[1]="Bruch Festbock";
• or as follows
$lousy_beers=array("Miller Lite", "Sam Adams",
"Budweiser");
print $lousy_beers[0];
// prints: Miller Lite
print $lousy_beers[2];
// prints: Budweiser
string arrays
• Sometimes you need data structured by a string.
For example for a price list.
$price['Grosswald Export']=1.45;
$price['Bruch Festbock']=1.74;
// the array $price has strings as keys
• An equivalent way to declare this is
$price=array('Grosswald Export' => 1.45, 'Bruch
Festbock' => 1.74);
array functions
• There is a very large number of array functions.
They are described in the array function
reference.
http://www.php.net/manual/en/ref.array.php
• Now we are just looking at some examples.
count()
• count() returns the size of an array
$price['Grosswald Export']=1.45;
$price['Bruch Festbock']=1.74;
$product_number=count($price);
print "We have $product_number products for you
today.";
// prints: We have 2 products for you today.
counting in numeric arrays
• For numeric arrays, you can add members very
simple without keeping track of number.
$beers=("Karlsberg", "Bruch") ;
$beers[]="Budweiser";
// $beer now has Karlberg, Bruch and Budweiser
print count($beers) ; // prints 3
looping through an array
• foreach() loops through an array. An example
illustrates
print "<table caption=\"price list\">\n";
foreach ($price as $item => $euro_amount) {
print "<tr><td>$item</td>\n";
print "<td>&euro;$euro_amount</td></tr>\n";
}
print "</table>";
• This prints the full price list. But it could also do
the whole form. This is fabulous!
example from the form
• $_GET is an array. You can loop through it.
foreach($_GET as $control => $value) {
print “you set $control to $value<br/>\n”;
}
the well-aligned price table
$l_r=array('left','right');
$count=0; // counter of elements printed
print "<table caption=\"price list\">\n";
foreach ($price as $item => $euro_amount) {
print "<tr><td align=\"$l_r[$count % 2]\"";
print "$item";
$count++;
print "</td>\n<td align=$l_r[$count % 2]\">
&euro;$euro_amount</td></tr>\n";
$count++;
}
print "</table>\n";
// This produces something like
// <table caption="price list">
// <tr><td align="left">Grosswald Export</td>
// <td align="right">&euro;1.45</td></tr>
// <tr><td align="left">Bruch Festbock</td>
// <td align="right"'>&euro;1.74</td></tr>
// </table>
multiple arrays
• Example
$products[0]['name']="Grosswald Pilsener";
$products[0]['price']=1.56;
$products[1]['name']="Grosswald Export";
$products[1]['price']=1.34;
$products[2]['name']="Bruch Landbier";
$products[2]['price']=1.22;
restructure the shop
• Instead of having two files, one with HTML, the
other with PHP, let us have just one. It's easier.
• One cool thing to help that is cool is
$_SERVER[PHP_SELF]
It gives the file name of your script in the form. As
you change your script file name, you do not need
to change the name of the form submitted.
check for submission
• We include a hidden element in the form to see if
it was submitted
<input type="hidden" name="submitted" value="1"/>
• We start the script we check for submission
if($_GET['submitted']) {
// work on the data that was submitted
}
else {
// print form
}
define some variables
$form_greet='<h1>Please place your order</h1>';
$form_top="<form action=\"$_SERVER[PHP_SELF]\"
method=\"GET\"><table>";
$form_submit='</table><input type="submit" value="I
order"/>';
$submit_check='<input type="hidden" name="submitted"
value="1"/>';
$form_bottom='</form>';
$order_head="<h1>Results of your order</h1><div>\n";
•$order_bottom="Thank you for your order. We will ship
when we get your check. Prosit!\n</div>";
printing results
if($_GET['submitted']) {
$total_euro=0;
print $order_head;
foreach($_GET as $number => $amount) {
if($amount > 0 and $products[$number]) {
$pay=$amount*$products[$number][price];
print "$amount bottles of ";
print $products[$number][name];
print " is &euro;$pay<br/>";
$total_euro+=$pay;
}
}
print results, start form printing part
$total_dollar=$total_euro*$euro_rate;
$total_dollar=number_format($total_dollar,2);
print "The euro rate is $euro_rate<br/>\n";
print "Your bill is \$$total_dollar\n</div>";
}
else { // print the form
print $form_greet; print $form_top; $product_count=0;
foreach ($products as $prod) {
print "\n<tr><td>";
print $prod['name'];
print "</td><td>";
final part, printing the form
print "<input type=\"text\" name=\"";
print $product_count;
print "\" maxlength=\"2\" size=\"2\"/>";
print "</td><td>@&euro;";
print $prod['price'];
print "</td></tr>\n";
$product_count++; // don't forget!
}
print $submit_check;
print $form_submit;
print $form_bottom;
}
databases
• Databases are collection of data with some
organization to them.
• The classic example is the relational database.
• But not all database need to be relational
databases.
relational databases
• A relational database is a set of tables. There
may be relations between the tables.
• Each table has a number of record. Each record
has a number of fields.
• When the database is being set up, we fix
– the size of each field
– relationships between tables
example: Movie database
ID
M1
M2
M3
M4
M5
M6
| title
| Gone with the wind
| Room with a view
| High Noon
| Star Wars
| Alien
| Blowing in the Wind
| director
| F. Ford Coppola
| Coppola, F Ford
| Woody Allan
| Steve Spielberg
| Allen, Woody
| Spielberg, Steven
• Single table
• No relations between tables, of course
| date
| 1963
| 1985
| 1974
| 1993
| 1987
| 1962
problem with this database
• All data wrong, but this is just for illustration.
• Name covered inconsistently. There is no way to
find films by Woody Allan without having to go
through all spelling variations.
• Mistakes are difficult to correct. We have to
wade through all records, a masochist’s
pleasure.
Better movie database
ID | title
M1 | Gone with the wind
M2 | Room with a view
M3 | High Noon
M4 | Star Wars
M5 | Alien
M6 | Blowing in the Wind
| director
| D1
| D1
| D2
| D3
| D2
| D3
ID
D1
D2
D3
| birth year
| 1942
| 1957
| 1942
| director name
| Ford Coppola, Francis
| Allan, Woody
| Spielberg, Steven
| year
| 1963
| 1985
| 1974
| 1993
| 1987
| 1962
Relational database
• We have a one to many relationship between
directors and film
– Each film has one director
– Each director has produced many films
• Here it becomes possible for the computer
– To know which films have been directed by Woody
Allen
– To find which films have been directed by a director
born in 1942
Many-to-many relationships
• Each film has one director, but many actors star
in it. Relationship between actors and films is a
many to many relationship.
• Here are a few actors
ID
A1
A2
A3
| sex
|f
|m
|f
| actor name
| Brigitte Bardot
| George Clooney
| Marilyn Monroe
| birth year
| 1972
| 1927
| 1934
Actor/Movie table
actor id
| movie id
A1
| M4
A2
| M3
A3
| M2
A1
| M5
A1
| M3
A2
| M6
A3
| M4
… as many lines as required
databases in libraries
• Relational databases dominate the world of
structured data
• But not so popular in libraries
– Slow on very large databases (such as catalogs)
– Library data has nasty ad-hoc relationships, e.g.
• Translation of the first edition of a book
• CD supplement that comes with the print version
Difficult to deal with in a system where all relations and
field have to be set up at the start, can not be changed
easily later.
databases in web sites
• Lots of active web sites are driven by relational
databases. All large active web sites are.
• The design of a active web site first involves
looking at databases.
• In a shop situation, we have the following tables
–
–
–
–
customers
products
orders
orders_products
for multiple to multiple relationship between orders and products.
mySQL
• They are a very successful, open-source vendor
of SQL software.
• Their product is basically freely available.
• We will learn the mySQL dialect of SQL.
• We will do this next week.
phpmyadmin
• phpmyadmin is a set of PHP scripts that create a
general purpose interface to work with a mySQL
database.
• It is written in PHP.
• It lives at http://wotan.liu.edu/phpmyadmin.
• You need an account. This is not your wotan
account, but a permission to use a database on
the mySQL server running at wotan.
creating mySQL databases
• To create accounts however, I will do it in mySQL
directly with you now. After logging into mySQL as
the root user, I will issue
GRANT ALL ON user_name.* TO user_name
IDENTIFIED BY 'secret_word' WITH GRANT OPTION;
• user_name is your user name. It is the name of
your database as well as the name under which
you are logging in.
• You will type in the secret word.
http://openlib.org/home/krichel
Thank you for your attention!
Please switch off machines b4 leaving!