Transcript LIS651 lecture 1 PHP basics

LIS651 lecture 2
mySQL and PHP mySQL function
Thomas Krichel
2006-11-12
arrays and tables
• Arrays seem to cause the most confusion in
student understanding.
• An array is just a way for PHP to address multiple
things as one variable.
• Arrays can be of multiple dimensions.
• This is typically the case when the array
represents the contents of a table.
example
• Here is an example table
name
Bruch Landbock
Balitka 8
Budweiser
type
dark
wheat
light
brewer
Bruch
Baltika
A.-B.
rating price
good 1.18
good 0.88
lousy 0.99
• typically, records appear in lines and fields in
columns.
one way to set out the table
$beers[0]['name']='Bruch Landbock';
$beers[0]['type']='dark';
$beers[0]['brewer']='Bruch'
….
$beers[2]['price']=0.99;
• Here, records are a numeric array. Fields are
string arrays that are members of the numeric
array.
• What instruction would improve the rating of
Budweiser?
another way …
$beer=array( 'name'=> 'Bruch Landbock', type='dark',
'brewer'=> 'Bruch', 'rating'=>'good', price=>'1.18');
$beers[]=$beer;
…
$beer=array( 'name'=>'Budweiser', type='light',
'brewer'=>'A.-B.', 'rating'=>'lousy', price=>0.99);
$beers[]=$beer;
• This will give the same array as before.
yet another way, as a matrix
$names=array('Bruch Landbock', 'Baltika 8','Budweiser');
$types=array( 'dark', 'wheat', 'light' );
$brewers=array( 'Bruch', 'Baltika', 'A.-B.');
$ratings=array( 'good', 'good', 'lousy');
$prices=array(1.18,0.88,0.99);
$beers=array( $names, $types,$brewers,$ratings,
$prices);
• What instruction would improve the rating of
Budweiser?
another way to set out the table
$names=array('Bruch Landbock', 'Baltika 8','Budweiser');
$types=array( 'dark', 'wheat', 'light' );
$brewers=array( 'Bruch', 'Baltika', 'A.-B.');
$ratings=array( 'good', 'good', 'lousy');
$prices=array(1.18,0.88,0.99);
$beers=array( 'name'=>$names, 'type'=>$types,
brewer=> $brewers, 'rating'=>$ratings,
'price'=>$prices);
• What instruction would improve the rating of
Budweiser?
using mySQL
• mySQL is installed on wotan.
• Normally this involves logging into wotan and
issuing commands to a character interface.
• The command would be
mysql -u user -p
• and then you type in your password. If you login
to wotan you can use it in this way.
uppercase and lowercase
• Traditionally SQL commands are written with
uppercase.
• mySQL commands are really case-insensitive.
• But variable names in the commands are casesensitive. I will therefore write them in lowercase.
• Statements in SQL are ended with a semicolon,
just like PHP statements.
creating databases
• CREATE DATABASE a mySQL command to
create a new database.
• Example
CREATE DATABASE newbase;
• creates a database newbase
GRANT
• This is a command to create users and give them
privileges. A simplified general syntax is
GRANT privileges ON item TO user_name
[IDENTIFIED BY 'password'] [WITH GRANT OPTION]
• If you use WITH GRANT OPTION, you allow the
user to grant other users the privileges that you
have given to her.
user privileges I
• SELECT allows users to select (read) records
from tables. Generally select is a word used for
read in databases.
• INSERT allows users to insert new rows into
tables.
• UPDATE allows users to change values in
existing table rows.
• DELETE allows users to delete table rows
(records)
• INDEX allows user to index tables
user privileges II
• ALTER allows users to change the structure of
the database.
– adding columns
– renaming columns or tables
– changing the data types of tables
• DROP allows users to delete databases or
tables. In general, the word drop refers to deleting
database or tables.
user privileges III
• CREATE allows users to create new databases or
tables. If a specific table or database is
mentioned in the GRANT statement, users can
only create that database or table, which will
mean that they have to drop it first.
• USAGE allows users nothing. This is a useful
point to start with if you just want to create a user.
• ALL means just that.
REVOKE
• This is the opposite of GRANT.
current setup
• As the super user, I did
CREATE DATABASE user_name;
GRANT ALL ON user_name TO user_name
IDENTIFIED BY 'secret_word' WITH GRANT OPTION;
• Here
– user_name is your wotan user name
– secret_word is your secret word
– * means all rights
create a web user
• You do not want to give the same access rights to
people coming in from the web as you have.
• You do not want to do this. You personally have
too many privileges.
• I have yet to find out how you can create a web
user by yourself.
creating tables
• This is done conveniently in phpmyadmin.
• Here is an example for real SQL code
CREATE TABLE customers (customer_id INT UNSIGNED
NOT NULL AUTO_INCREMENT PRIMARY KEY, name
CHAR(50) NOT NULL, address CHAR(100) NOT NULL,
email CHAR(40), state CHAR(2) NOT NULL);
column data types
• TINYINT can hold a number between -128 and
127 or between 0 to 255. BIT or BOOL are
synonyms for the TINYINT.
• SMALLINT can hold a number between -32768
and +32767 or 0 and 65535
• INT can hold a number between -2**31 and
2**31-1 or between 0 and 2**32-1. INTEGER is a
synonym for INT.
• BIGINT can hold a number between -2**63 and
2**61-1 or between 0 and 2**64-1.
column data types: float
• FLOAT is a floating number on 4 bytes
• DOUBLE is a floating number on 8 bytes
• DECIMAL(x,y) where x is the number of digits
before the decimal point and y is the number of
digits after the decimal point.
column data types: dates
• DATE is a day from 1000-01-01 to 9999-12-31.
• TIME is a time from -838:59:59 to 838:59:59
• DATETIME is a date and time, usually displayed
as YYYY-MM-DD HH:MM:SS
• TIMESTAMP is the number of seconds since
1970-01-01 at 0 hours. This number may run out
in 2037.
field options
• PRIMARY KEY says that this column is a the
primary key. There can be only one such column.
Values in the column must be unique.
• AUTO_INCREMENT can be used on columns
that contain integer values.
• NOT NULL requires the field not to be empty.
USE
• USE database tells mySQL to start working with
the database database.
• If you have not issued a USE command, you can
still address a table table by using database.table,
where datebase is the name of your database
and table is the name of your table. You are using
the dot to link the two together.
addressing database tables
columns
• Let there by a database database with a table
table and some column column. Then it is
addressed as database.table.column.
• Parts of this notation can be left out if it is clear
what is meant, for example if you have issued
USE database before, you can leave out the
database part.
INSERT
• INSERT inserts new rows into a table. In its
simples form
INSERT INTO table VALUES (value1, value2, ..);
• Example:
INSERT INTO products VALUES ('','Neufang Pils',1.23);
• Note that in the example, I insert the null string in
the first column because it is an auto_increment.
• Mark Sandford says: If you use an
auto_increment variable, you may as well have it
last.
partial INSERT
• If you are only giving a part of a record, or if you
want to enter them in a different order you will
have to give a list of column names.
INSERT INTO products (name,id) VALUES ('Neufang
Pils','');
SELECT
• This is the SQL statement to select rows from a
table. Here is the full syntax:
SELECT [options] columns [INTO file_details]
FROM table [WHERE conditions]
[GROUP BY group_type]
[HAVING where_definitions]
[ORDER BY order_type] [LIMIT limit_criteria]
[PROCEDURE proc_name(arguments)]
[lock_options]
columns to SELECT
• You can have a comma-separated list of columns
SELECT name, price FROM products;
• You can use the star to get all columns
SELECT * FROM products;
WHERE condition to SELECT
• = means equality
WHERE id = 3
•
•
•
•
>, <, >=, <= and != also work as expected
IS NULL tests if the value is null
IS NOT NULL
IN allows you to give a set
WHERE state IN ("NY","NJ","CT")
ORDER
• You can order by a field by saying ORDER BY.
• You can add ASC or DESC to achieve ascending
or descending order.
SELECT name, address FROM customers ORDER BY
name ASC
column functions and grouping
• You can use the function on the columns
SELECT AVG(amount) FROM orders;
• You can group the selection. For example, find
the minimum for each customer
SELECT MIN(amount) FROM orders
GROUP BY customerid;
• You can use them in conditions with HAVING,
such as
SELECT customerid FROM orders
HAVING AVG(amount) > 10;
column functions
• AVG(column) give average of the column
• COUNT(column) gives you a count of non NULL
values
• COUNT(DISTINCT column) gives a count of
distinct values
• MIN(column), MAX(column)
• STD(column) gives the standard deviation
• SUM(column) gives the sum of the items
LIMIT
• This can be used to limit the amount of rows.
LIMIT 10 19
• This is useful it web sites where you show a
selection of the results.
• This ends the discussion of the SELECT
command.
changing values in a row
• The general syntax is UPDATE [LOW_PRIORITY]
[IGNORE] table SET column1=expression1,
column2=expression2... [WHERE condition]
[ORDER BY order_criteria] [LIMIT number] ;
• An example is
UPDATE students SET email= '[email protected]'
WHERE name='Janice Insinga';
• IGNORE instructs to ignore errors.
• LOW_PRIORITY instructs to delay if the server is
busy.
deleting rows
• The general syntax is
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM table [WHERE condition] [ORDER BY
order_criteria] [LIMIT number];
• Bad example
DELETE FROM customers;
• Good example
DELETE FROM customers WHERE
customer.name='Thomas Krichel';
PHP mySQL functions
• We are using here the old version of PHP mySQL
function, starting with mysql_
• The interface is object-oriented, but can also be
accessed in a non-object-oriented way. This is
known as the procedural style, in the
documentation.
• You should use the online documentation at
http://php.net/mysql
mysql_connect()
• This is used to establish a connection to the
mySQL server. It is typically of the form
mysql_connect('host', 'user', 'password');
• Example
$link= mysql_connect('localhost','boozer','heineken');
• You can use localhost as the host name for wotan
talking to itself, but you could also connect to
other Internet hosts, if you have permission.
• The function returns a variable of type “resource”.
If there is a mistake, it returns false.
mysql_connect_error ()
• This function does not exist. Do not study this
slide.
• This function returns a string with the last
connection error.
$link = mysql_connect("localhost", "bad_user", "");
if (!$link) {
print "Can't connect to localhost. The error is<br>";
print mysql_connect_error();
print "<br/>";
}
mysql_error()
• This function return the error from the last mySQL
command. It returns false if there was no error.
$error=mysql_error();
if($error) {
print "mySQL error: $error<br/>";
}
• The value returned from that function is a simple
string.
• It is a good idea to check out error messages.
mysql_select_db()
• This command has the syntax
mysql_select_db('database') where database is
the name of a database.
• It returns a Boolean.
• This tells mySQL that you now want to use the
database database.
mysql_select_db('beer_shop');
• It has the same effect as issuing
USE beer_shop;
within mySQL.
mysql_query()
• mysql_query(query) send the query query to
mySQL.
$link = mysql_connect("localhost", "shop_owner",
"bruch"); // you may then add some connection checks
$query="SELECT * FROM beer_shop.customers";
$result=mysql_query($query);
• Note that the query itself does not require a
terminating semicolon.
• The result is in $result.
result of mysql_query()
• For SELECT, SHOW, DESCRIBE or EXPLAIN
mySQL queries, mysql_query() returns a
resource that can be further examined with
mysql_fetch_array().
• For UPDATE, DELETE, DROP and others,
mysql_query() returns a Boolean value.
mysql_num_rows()
• This command has the syntax
mysql_select_db('result') where result is the result
of a query.
• It returns the number of rows that are in the
result.
• You could also think of it as the number of
results.
examining resulting rows
• mysql_fetch_array(result) returns an array that is
the result row for the resource result representing
the most recent, or NULL if it the last result is
reached. Its results in an array that contains the
columns requested both by number and by
column name:
while($columns=mysql_fetch_array($result)) {
print 'name: '.$columns['name'];
print 'first column: ‘.$columns[0];
}
examining a specific result
• mysql_data_seek(result, number) sets the array
that is returned by mysql_fetch_array to a number
number.
while($row=mysql_fetch_array($result)) {
print 'first column: '.$row[0];
}
mysql_data_seek($result,0);
// otherwise the second loop would not work
while($row=mysql_fetch_array($result)) {
print 'first column: '.$row[0];
}
mysql_real_escape_string()
• mysql_real_escape_string(string) returns a string
escaped for the using in mySQL.
$name="John O'Guiness";
$s_name=mysql_real_escape_string($name);
print $s_name; // prints: John O\'Guiness
• Note that this function makes a call to mySQL,
therefore a connection must be established before
the function can be used.
• This function guards against SQL injections.
mysql_close()
• This command connection. When it is invoked
without an argument, it closes the current
connection.
• This is the happiest command there is, because it
means that we have finished.
• Unfortunately it is not used very often because
the mySQL connection is closed automatically
when the script finishes running.
extra: sha1()
• This is a function that calculates a combination of
40 characters from a string.
• The result of sha1() can not be translated back
into the original string.
• This makes it a good way to store password.
$s_password=sha1($password);
http://openlib.org/home/krichel
Thank you for your attention!
Please switch off machines b4 leaving!