Web Application Development

1
Web Application Development

• Seminar 3
• CMWA 6103
• Jaspal Kaur

2
Topics Covered

• Accessing MySQL Database from the Web with PHP
• Setting up connections
• Closing the connection
• Choosing a database to use
• Creating tables
• Querying the database
• Retrieving Query results
• Disconnecting from the database

3
Setting up connections

• Opening a connection to MySQL database from PHP
  is easy. Just use the mysql_connect() function
  like this
• lt?php
• dbhost 'localhost'
• dbuser 'root'
• dbpass 'password'
• conn mysql_connect(dbhost, dbuser, dbpass)
• dbname mydatabase'
• mysql_select_db(dbname)
• ?gt

4
Setting up connections

• dbhost is the name of MySQL server. When your
  webserver is on the same machine with the MySQL
  server you can use localhost or 127.0.0.1 as the
  value of dbhost. The dbuser and dbpass are
  valid MySQL user name and password.
• Don't forget to select a database using
  mysql_select_db() after connecting to mysql. If
  no database selected your query to select or
  update a table will not work.

5
Closing the connection

• The connection opened in a script will be closed
  as soon as the execution of the script ends. But
  it's better if you close it explicitly by calling
  mysql_close() function.
• lt?php
• // an example of closedb.php
• // it does nothing but closing
• // a mysql database connection
• mysql_close(conn)
• ?gt

6
Choosing a Database to use

• Once you have connected to MySQL, you will need
  to choose the database with which you want to
  work.
• This is the equivalent of saying USE databasename
  within the mysql monitor and is accomplished with
  the mysql_select_db() function.
• mysql_select_db(dbname)

7
Choosing a Database to use

• To create a database use the mysql_query()
  function to execute an SQL query like this
• lt?php
• dbhost 'localhost'
• dbuser 'root'
• dbpass 'password'
• dbname mydatabase'
• conn mysql_connect(dbhost, dbuser, dbpass)
• mysql_select_db(dbname)
• query "CREATE DATABASE mydatabase"
• result mysql_query(query)
• mysql_close(conn)
• ?gt

8
Creating the tables

• If you want to create tables in the database you
  just created don't forget to call
  mysql_select_db() to access the new database.
• To create tables in the new database you need to
  do the same thing as creating the database. First
  create the SQL query to create the tables then
  execute the query using mysql_query() function.

9
Creating the tables

• lt?php
• .
• query 'CREATE TABLE contact( '.
• 'cid INT NOT NULL AUTO_INCREMENT, '.
• 'cname VARCHAR(20) NOT NULL, '.
• 'cemail VARCHAR(50) NOT NULL, '.
• 'csubject VARCHAR(30) NOT NULL, '.
• 'cmessage TEXT NOT NULL, '.
• 'PRIMARY KEY(cid))'
• result mysql_query(query)
• ..
• ?gt

10
Inserting records into table

• After the database and the tables are ready it's
  time to put something into the database.
• Inserting data to MySQL is done by using
  mysql_query() to execute INSERT query. Note that
  the query string should not end with a semicolon.
  Below is an example of adding a new MySQL user by
  inserting a new row into table user in database
  mysql

11
Inserting records into table

• Example insert.php
• Source code insert.phps
• lt?php
• include 'library/config.php'
• include 'library/opendb.php'
• mysql_select_db(mysql)
• query "INSERT INTO user (host, user, password,
  select_priv, insert_priv, update_ priv) VALUES
  ('localhost', 'phpcake', PASSWORD('mypass'), 'Y',
  'Y', 'Y')"
• mysql_query(query)
• query "FLUSH PRIVILEGES"
• mysql_query(query)
• include 'library/closedb.php'
• ?gt

12
Inserting records into table

• In a real application the values of an INSERT
  statement will be form values. Below is an
  example of using form values with INSERT. It's
  the same as above except that the new username
  and password are taken from _POST
• Example adduser.php
• Source code adduser.phps
• lthtmlgt
• ltheadgt
• lttitlegtAdd New MySQL Userlt/titlegt
• ltmeta http-equiv"Content-Type"
  content"text/html charsetiso-8859-1"gt
• lt/headgt
• ltbodygt

13
Inserting records into table

• lt?php
• if(isset(_POST'add'))
• include 'library/config.php'
• include 'library/opendb.php'
• username _POST'username'
• password _POST'password'
• query "INSERT INTO user (host, user, password,
  select_priv, insert_priv, update_ priv) VALUES
  ('localhost', 'username', PASSWORD('password'),
  'Y', 'Y', 'Y')"
• mysql_query(query) or die('Error, insert query
  failed')
• query "FLUSH PRIVILEGES"
• mysql_query(query) or die('Error, insert query
  failed')
• include 'library/closedb.php'
• echo "New MySQL user added"
• else

14
Inserting records into table

• ltform method"post"gt
• lttable width"400" border"0" cellspacing"1"
  cellpadding"2"gt
• lttrgt
• lttd width"100"gtUsernamelt/tdgt
• lttdgtltinput name"username" type"text"
  id"username"gtlt/tdgt
• lt/trgt
• lttrgt
• lttd width"100"gtPasswordlt/tdgt
• lttdgtltinput name"password" type"text"
  id"password"gtlt/tdgt
• lt/trgt
• lttrgt
• lttd width"100"gtnbsplt/tdgt
• lttdgtnbsplt/tdgt
• lt/trgt
• lttrgt
• lttd width"100"gtnbsplt/tdgt
• lttdgtltinput name"add" type"submit" id"add"
  value"Add New User"gtlt/tdgt
• lt/trgt
• lt/tablegt

15
Querying the database

• Using PHP you can run a MySQL SELECT query to
  fetch the data out of the database. You have
  several options in fetching information from
  MySQL. PHP provide several functions for this.
  The first one is mysql_fetch_array()which fetch a
  result row as an associative array, a numeric
  array, or both.
• Below is an example of fetching data from MySQL,
  the table contact have three columns, name,
  subject and message.

16
Querying the database

• Example select.php
• Source code select.phps, contact.txt
• lt?php
• include 'config.php'
• include 'opendb.php'
• query "SELECT name, subject, message FROM
  contact"
• result mysql_query(query)
• while(row mysql_fetch_array(result,
  MYSQL_ASSOC))
• echo "Name row'name' ltbrgt" .
• "Subject row'subject' ltbrgt" .
• "Message row'message' ltbrgtltbrgt"
• include 'closedb.php'
• ?gt

17
Querying the database

• The while() loop will keep fetching new rows
  until mysql_fetch_array() returns FALSE, which
  means there are no more rows to fetch. The
  content of the rows are assigned to the variable
  row and the values in row are then printed.
  Always remember to put curly brackets when you
  want to insert an array value directly into a
  string.
• In above example I use the constant MYSQL_ASSOC
  as the second argument to mysql_fetch_array(), so
  that it returns the row as an associative array.
  With an associative array you can access the
  field by using their name instead of using the
  index . Personally I think it's more informative
  to use row'subject' instead of row1.
• PHP also provide a function called
  mysql_fetch_assoc() which also return the row as
  an associative array.

18
Querying the database

• lt?php
• include 'config.php'
• include 'opendb.php'
• query "SELECT name, subject, message FROM
  contact"
• result mysql_query(query)
• while(row mysql_fetch_assoc(result))
• echo "Name row'name' ltbrgt" .
• "Subject row'subject' ltbrgt" .
• "Message row'message' ltbrgtltbrgt"
• include 'closedb.php'
• ?gt

19
Querying the database

• There is another method for you to get the values
  from a row. You can use list(), to assign a list
  of variables in one operation.
• lt?php
• include 'config.php'
• include 'opendb.php'
• query "SELECT name, subject, message FROM
  contact"
• result mysql_query(query)
• while(list(name,subject,message)
  mysql_fetch_row(result))
• echo "Name name ltbrgt" .
• "Subject subject ltbrgt" .
• "Message row ltbrgtltbrgt"
• include 'closedb.php'
• ?gt

20
Querying the database

• In above example, list() assign the values in the
  array returned by mysql_fetch_row() into the
  variable name, subject and message.Of course
  you can also do it like this
• lt?php
• query "SELECT name, subject, message FROM
  contact"
• result mysql_query(query)
• while(row mysql_fetch_row(result))
• name row0
• subject row1
• message row2
• echo "Name name ltbrgt" .
• "Subject subject ltbrgt" .
• "Message row ltbrgtltbrgt"

21
Deleting the database

• Use mysql_query() and to execute the SQL DROP
  DATABASE statement.
• lt?php
• // ... do something here
• query 'DROP DATABASE mydatabase'
• result mysql_query(query)
• // ... probably do something here too
• ?gt

22
End of Seminar 3

• Next Exam Revision