Transcript LIS651 lecture 1 PHP basics
LIS651 lecture 2 mySQL and PHP mySQL functions
Thomas Krichel 2007-04-01
today
• Today I talk about – arrays and table (again, since this causes problems) – some basic mySQL commands – a PHP interface to mySQL called mysqli • Today you do – create a table with phpmyadmin – write PHP scripts to read/write data to the table using the web
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 Festbock Balitka 8
type
dark
brewer
Bruch
rating price
good 1.18
wheat Baltika good 0.88
Budweiser light A.-B.
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 server is installed on wotan. It is a daemon that deals with client requests. There is also a tty client installed. To use it you log into wotan and type mysql -u
user
-p and then you type in your password. We will cover this in the last lecture.
uppercase and lowercase
• Traditionally SQL commands are written with uppercase.
• mySQL commands are really case-insensitive.
• But variable names in the commands are case sensitive. I will therefore write them in lowercase.
CREATE DATABASE
• CREATE DATABASE a mySQL command to create a new database.
• Example CREATE DATABASE newbase; • creates a database newbase.
• You have no privileges to create a database.
• But I don’t see the reason you wanting to do that.
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.
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 – ALL 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
and
table
is the name of your database 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")
SELECT using multiple tables
•
table1,table2
can be used to join both tables to build a big table that can be searched SELECT orders.id FROM customers, orders WHERE customers.id= 3 • This type of join is a Cartesian product aka a full join. For each row of the first table, it adds rows from the second table.
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
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.
UPDATE
• UPDATE [LOW_PRIORITY] [IGNORE]
table
SET
column1=expression1, column2=expression2...
[WHERE [LIMIT
condition number
] ; ] [ORDER BY
order_criteria
] • This changes values in a row.
• 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.
DELETE
• DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM
table
[WHERE
condition
] [ORDER BY
order_criteria
] [LIMIT
number
] • Here is a very bad example DELETE FROM customers; • Here is a good example DELETE FROM customers WHERE customer.name='Thomas Krichel‘;
PHP mySQL functions
• We are using here the new version of PHP mySQL function, starting with mysqli_ • 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/mysqli
error suppression
• The function mentioned in this library usually report any error that has occurred. • It can be useful to suppress such errors with the PHP error suppression operator @.
• @
function
() will run the function
function
reporting mistakes.
without • You can then create your own customized mistakes by checking for errors every time you run a mysqli function. This is useful.
mysqli_connect()
• This is used to establish a connection to the mySQL server. It is typically of the form mysqli_connect('
host
', '
user'
, '
password
'); • Example $link= mysqli_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 mySQL connection with mysqli
• The mysqli module has the ability to handle several connections to the mySQL server.
• Once a connection is established it is represented by the variable returned by mysqli_connect().
• This variable, of type "resource" has the be referenced later to let mysqli functions know what connection you are using, because there may be more than one open at the same time.
mysqli_connect_error()
• This function returns a string with the last connection error. $link = mysqli_connect("localhost", "bad_user", ""); if (!$link) { print "Can't connect to localhost. The error is
"; print mysqli_connect_error(); print "
"; } • Note the use of ! to express Boolean "not".
mysqli_error(
link
)
• This function return the error from the last mySQL command. It returns false if there was no error. $error=mysqli_error($link); if($error) { print "mySQL error: $error
"; } • This function requires the connection as a parameter. • The value returned from that function is a simple string. • It is a good idea to check out error messages.
mysqli_select_db()
• This command has the syntax mysqli_select_db(
link
,'
database
') where
link
is a resource representing a connection and
database
is the name of a database
.
• This tells mySQL that you now want to use the database
database.
mysqli_select_db($link,'beer_shop'); • It has the same effect as issuing USE beer_shop
;
within mySQL
.
mysqli_query()
• mysqli_query(
link,query
) send the query string
query
to mySQL connection represented by
link
$link = mysqli_connect("localhost", "owner", "bruch"); // you may then add some connection checks $query="SELECT * FROM beer_shop.customers"; $result=mysqli_query($link,$query); • Note that the query itself does not require a terminating semicolon.
• The result is in $result.
result of mysqli_query()
• For SELECT, SHOW, DESCRIBE or EXPLAIN mySQL queries, mysqli_query() returns a resource that can be further examined with mysqli_fetch_array(). This is very important function that we look at in the next slide.
• For UPDATE, INSERT, DELETE, DROP and others, mysqli_query() returns a Boolean value.
examining resulting rows
• mysqli_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=mysqli_fetch_array($result)) { print 'name: '.$columns['name']; print 'first column: ‘.$columns[0]; }
examining a specific result
• mysqli_data_seek(
result, number
) sets the array that is returned by mysqli_fetch_array to a number
number.
while($row=mysqli_fetch_array($result)) { print 'first column: '.$row[0]; } } mysqli_data_seek($result,0); // otherwise the second loop would not work while($row=mysqli_fetch_array($result)) { print 'first column: '.$row[0];
mysqli_num_rows()
• This command has the syntax mysqli_select_db(
result
) where the resource
result
is the result of a query
.
• It returns the number of rows that are in the result. • This is useful in announcing the number results before display of results.
mysqli_real_escape_string()
• mysqli_real_escape_string(
link
,
string
) returns a string escaped for the using in mySQL.
$name="John O'Guiness"; $s_name=mysqli_real_escape_string($link,$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.
mysqli_close(
link
)
• This command closes a connection. It requires the connection as an argument, so that it knows which connection to close. • 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!