CICS 515 b Internet Programming Week 2 Mike Feeley 1
Software infrastructure stuff MySQL and PHP • store files in public_html • run on remote.mss.icics.ubc.ca • access as http://ws.mss.icics.ubc.ca/~username/... • see wiki for configuration instructions and experience from previous years Eclipse for HTML / JavaScript editing and debugging • get the full build • install current ATF build - (http://www.eclipse.org/atf/downloads/index_build.php?plat=all&buildID=0.2.1-v200704051500&XULPlugin=true) • download JSLint - http://sourceforge.net/project/downloading.php?groupname=atf-additions&filename=fulljslint-2007-05-20- patched.js&use_mirror=umn - and set Ecplise AFT preferences to use it 2
Reading PHP • Review 13.1-13.4 • 13.5 • 9.0 - 9.10 3
A Sample Application 4
Description simple student database • student ID is unique nine-digit number • name is a 30 character string store the student database in MySql design a web page that • displays the complete list of students • allows new students to be added • allows students to be deleted • allows student names to be changed its in this weeks code base • http://www.cs.ubc.ca/~feeley/cics515/code/week2/enterStudents.php 5
Database MySQL online documentation • http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html MySQL on command line ~% /usr/local/mysql/bin/mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 420 Server version: 5.0.41 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use feeley_database; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> 6
list tables mysql> show tables; +---------------------------+ | Tables_in_feeley_database | +---------------------------+ | student | +---------------------------+ 1 row in set (0.00 sec) mysql> show a table’s schema mysql> describe student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | NO | PRI | | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.36 sec) mysql> 7
delete the table mysql> drop table student; Query OK, 0 rows affected (0.19 sec) mysql> show tables; Empty set (0.00 sec) adding the student table schema mysql> CREATE TABLE student ( -> sid INT NOT NULL, -> name VARCHAR(30), -> PRIMARY KEY (sid)); Query OK, 0 rows affected (0.02 sec) mysql> DESCRIBE student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | NO | PRI | | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) 8
Introduction to PHP P HP h ypertext p reprocessor • script embedded in an HTML document • script is executed in web server when document is requested by client • script inserts HTML code to replace itself in the document • server sends resulting pure HTML document to client browser basic syntax foo.php: <html> <body> <?php echo "Hello."; ?> </body> </html> debugging nightmear • any bug in script and sever my just send blank document — build, test incrementally 9
variables • names start with $ • replaced within strings (rules a bit complicated) - “foo”.$var.”foo” or “foo$var foo” or “foo${var}foo” • arrays are associative - $a[2] or $a[‘cat’] <html> <body> <?php $sid[0] = 10; $name[0] = "First Student"; $sid[1] = 20; $name[1] = "Second Student"; echo "<table border=2>"; echo "<tr><td>".$sid[0]."</td><td>".$name[0]."</td></tr>"; echo "<tr><td>".$sid[1]."</td><td>".$name[1]."</td></tr>"; echo "</table>"; ?> </body> </html> 10
other stuff is like C (which is like Java) • for loops, while loops, function etc. <html> <body> <?php $sid[0] = 10; $name[0] = "First Student"; $sid[1] = 20; $name[1] = "Second Student"; echo "<table border=2>"; for ($i=0; $i<2; $i++) { echo "<tr><td>".$sid[$i]."</td><td>".$name[$i]."</td></tr>"; } echo "</table>"; ?> </body> </html> 11
PHP and MySQL online documentation • http://ca3.php.net/manual/en/index.php dbconfig.php and opendb.php <?php $dbhost = ':/.autofs/homes/ubccshome/f/feeley/cics515/mysql/var/mysql.sock'; $dbuser = 'feeley'; $dbpass = 'feeley'; $dbname = 'feeley_database'; ?> <?php $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); mysql_select_db($dbname); ?> 12
MySql Configuration file MySqld is configured by • .my.sql file in your root directory PHP attaches to mysql using a socket file • you name the file in your dbconfig.php <?php $dbhost = ':/.autofs/homes/ubccshome/f/feeley/cics515/mysql/var/mysql.sock'; ... You configure MySqld • to choose an unique port • to use this socket file [mysqld] port = 8743 socket = /.autofs/homes/ubccshome/f/feeley/cics515/mysql/var/mysql.sock datadir = /.autofs/homes/ubccshome/f/feeley/cics515/mysql/var Also see the Wiki 13
querying db and displaying results listStudents.php: <html> <body> <?php include 'dbconfig.php'; include 'opendb.php'; $rows = mysql_query ("SELECT * FROM student ORDER BY sid"); echo "<table border=2>\n"; echo "<tr><td><b>SID</b></td><td><b>Name</b></td></tr>\n"; while ($row = mysql_fetch_assoc ($rows)) echo "<tr><td>".$row['sid']."</td><td>".$row['name']."</td></tr>\n"; echo "</table>\n"; mysql_close (); ?> </body> </html> 14
the result web server database Apache MySql <html> <body> <?php include 'dbconfig.php'; include 'opendb.php'; mysql> select * from student order by sid; +-----+----------------+---------+ $rows = mysql_query ("SELECT * FROM student ORDER BY sid"); | sid | name | country | echo "<table border=2>\n"; +-----+----------------+---------+ echo "<tr><td><b>SID</b></td><td><b>Name</b></td></tr>\n"; | 10 | First Student | NULL | while ($row = mysql_fetch_assoc ($rows)) | 20 | Second Student | NULL | echo "<tr><td>".$row['sid']."</td><td>".$row['name']."</td></tr>\n"; +-----+----------------+---------+ echo "</table>\n"; 2 rows in set (0.04 sec) mysql_close (); ?> </body> </html> web browser IE, Safari, Firefox, etc. <html> <body> <table border=2> <tr><td><b>SID</b></td><td><b>Name</b></td></tr> <tr><td>10</td><td>First Student</td></tr> <tr><td>20</td><td>Second Student</td></tr> </table> </body> </html> 15
Sending updates back to the database when a form is submitted • browser sends name and value of every input element back to web server • either using the “post” HTML message - send input values in HTML, to a specified URL • or the “get” HTML messages - request a specified URL with input values appended to it declaring form method and action <form method=“?” action=“?”> • method is “post” or “get” - use “get” for idempotent operations — operations where performing once is the same as performing more than once - otherwise use “post” • action is URL that browser posts to or gets from in PHP • $_POST[‘name’] or $_GET[‘name’] • value of named input element 16
Recommend
More recommend