Database driven, dynamic mobile content using PHP and MySQL

1
Database driven, dynamic mobile content using PHP
and MySQL

• David Robison
• University of Bradford

2
PHP/MySQL FTP Account

• Set up a PHP/MySQL account (if you havent
  already http//lamp.inf.brad.ac.uk)
• You are allocated 15mb of space
• Sites created here can be dynamic through their
  interaction with a MySQL database
• Once signed up, you can transfer files to your
  PHP and MySQL enabled web space by using an FTP
  programme (Smart FTP, WS_FTP or a programme with
  FTP functionality built in such as Dreamweaver)

3
Your account useful info

• To view your homepage type http//lamp.inf.brad.
  ac.uk (replace with the port
  number you got when you signed up)
• To manage your database, you can use phpMyAdmin
  http//lamp.inf.brad.ac.uk/manage/index.php
  (See help here if you get stuckhttp//lamp.inf
  .brad.ac.uk/isp/phpmyadmin.html)

4
Using Dreamweaver to manage file transfers to
your PHP enabled space

• Dreamweaver contains some useful tools for
  uploading and managing your online files
• First create a local folder in which to store
  files for FTP upload/download (this can be on
  your network drive)
• In Dreamweaver create a new site profile (by
  clicking manage sites. Select the Advanced
  tab.
• Remote Info and Local Info are the only two
  sections you need to fill in (screenshots are
  provided in the following slides)
• Host name lamp.inf.brad.ac.uk
• Login your regular username
• Password your password (most likely Informatics
  password)

5
Local Info
6
Remote Info
7
Introducing PHP

• PHP is a programming language used mainly for
  developing server-side applications and dynamic
  web content. Originally, PHP stood for Personal
  Home Page. Today, the official meaning is the
  recursive acronym PHP Hypertext Preprocessor.
  Some fans of this language also use the label
  Pretty Hypertext Preprocessor
  (http//en.wikipedia.org/wiki/Php)
• PHP scripts can be embedded in a Web or WAP page
  and interpreted, allowing instructions to be
  carried out on the server before being sent to
  the client who requested the page
• PHP is open source, and is available from
  www.php.net

8
The following PHP code outputs a WML deck

• lt?php
• // send wml headers
• header("Content-type text/vnd.wap.wml")
• echo ("lt?xml version\"1.0\" encoding\"iso-8859-
  1\"?gt
• lt!DOCTYPE wml PUBLIC \"-//WAPFORUM//DTD
  WML1.3//EN\" \"http//www.wapforum.org/DTD/wml13.d
  td\" gt ")
• // begin wml
• echo ("ltwmlgt
• ltcardgt
• ltpgtHello Worldlt/pgt
• lt/cardgt
• lt/wmlgt")
• ?gt

9
Upload and test

• Enter the PHP code contained in the previous
  slide into a blank Dreamweaver document
• (All code examples can be downloaded from
  Blackboard this is preferential to copying and
  pasting from PowerPoint as Microsoft messes
  with the characters!)
• Save the file (in the local/network folder you
  defined) and upload it by selecting the file from
  Dreamweavers file list and clicking the upwards
  arrow
• Load the Nokia 5100 Emulator and open the URL of
  your file (e.g. http//lamp.inf.brad.ac.uk59087/h
  ello.php)

10
Basics

• Echo statements cause a printout into the
  output that PHP is preparing the browser e.g.
  echo (Hello World)
• PHP opens with lt?php and ends with ?gt
• You can break in and out of PHP and into regular
  mark-up language by using the above tags
• If you use inverted commas in the WML or XHTML
  code that you want PHP to output, you must place
  a \ character before them (this is because they
  are characters that perform other functions in
  PHP)

11
Further info on using PHP to generate WML

• http//www.zend.com/zend/tut/wap.php
• The above tutorial gives you an introduction to
  plugging PHP and WML technologies together
• Well now use similar techniques to create an
  XHTML MP application

12
Using PHP to generate a Hello in XHTML MP

• lt?php
• // send XHTML MP headers
• echo( "lt?xml version\"1.0\" encoding\"UTF-8\"?gt
  
• lt!DOCTYPE html PUBLIC \"-//WAPFORUM//DTD XHTML
  Mobile 1.0//EN\" \"http//www.wapforum.org/DTD/xht
  ml-mobile10.dtd\"gt" )
• // begin XHTML MP
• echo ( "lthtmlgt
• ltheadgt
• lttitlegtEXAMPLE XHTML PAGElt/titlegt
• lt/headgt
• ltbodygt
• ltpgtHello Worldlt/pgt
• lt/bodygt
• lt/htmlgt " )
• ?gt

13
PHP resources for reference

• http//www.4webhelp.net/tutorials/php/basics.php
• Command reference www.php.net

14
Managing your MySQL database using phpMyAdmin

• http//lamp.inf.brad.ac.uk/manage/phpinfo.ph
  p (replace the stars with your sign-up number)
• Login with your e-mail username and password (or
  SQL one provided during sign-up)
• phpMyAdmin is supposed to be a friendly face by
  which you can administer your MySQL database
  (rather than having to type at the command line)
• phpMyAdmin is itself written using a combination
  of PHP and XHTML

15
Making an example jokes table in your database

• Create a table in the database click on your
  username on the left
• Call it jokes and assign 3 fields
• The first field should be called id a unique
  number to assign each joke. It should be an
  integer (INT) with length value 11
• Under extra you should select auto_increment.
  This will create a unique number for every new
  record. And you should select this field to be
  Primary the main index
• Call the second field questions assign it as
  VARCHAR with 255 characters
• Call the third answers with the same properties
  then click to create the table

16
Examine the output

• CREATE TABLE jokes ( id INT( 11 ) NOT NULL
  AUTO_INCREMENT,question VARCHAR( 255 ) NOT
  NULL ,answer VARCHAR( 255 ) NOT NULL ,PRIMARY
  KEY ( id )
• )
• This above MySQL command is what phpMyAdmin has
  executed for you. In addition, you can execute
  MySQL commands by typing them into the SQL
  (Structured Query Language) box phpMyAdmin
  provides
• You can also execute MySQL commands from within
  PHP. They work very well together!

17
Insert a joke

• Click Insert (to insert a record into the
  joke table)
• Leave the first field blank
• Insert text field 2 e.g. What do you call a
  man with three planks on his head?
• Insert answer e.g. Edward Woodward
• Repeat the process, enter a few jokes or any text

18
Resources for MySQL

• The home of MySQL www.mysql.com
• Building a Database-Driven Web Site Using PHP and
  MySQL http//dev.mysql.com/tech-resources/articl
  es/ddws/
• Another tutorial http//hotwired.lycos.com/webmo
  nkey/programming/php/tutorials/tutorial4.html

19
Using PHP to interact with your jokes table

• Were going to create three PHP files which will
  do the following
• Identify and connect to your database
  (connect.php)
• List the jokes available in the database
  (list_jokes.php)
• Display the answer for selected joke
  (get_joke.php)
• Save them in their own folder (e.g. jokes and
  upload the folder using Dreamweaver)

20
Save the following as connect.php

• lt?php
• // Make connection to Database
• db _at_mysql_connect (localhost,
  "your_user_name", "password")
• mysql_select_db("your_user_name")
• ?gt

21
List_jokes.php (part 1)

• lt?php
• echo( "lt?xml version\"1.0\" encoding\"UTF-8\"?gt
  
• lt!DOCTYPE html PUBLIC \"-//WAPFORUM//DTD XHTML
  Mobile 1.0//EN\" \"http//www.wapforum.org/DTD/xht
  ml-mobile10.dtd\"gt" )
• echo ( "
• lthtmlgt
• ltheadgt
• lttitlegtJokes Databaselt/titlegt
• lt/headgt
• ltbodygt")
• (continued)

22
Add this and save as list_jokes.php

• // Connect to database
• include( "connect.php" )
• // Ask for list of jokes
• result mysql_query( "SELECT id, question
  FROM jokes", db )
• if( result )
• echo( "ltulgt" )
• while( row mysql_fetch_row( result ) )
• echo( "ltligtQ)lta href\"get_joke.php?id" .
  row 0 . "\"gt" . row 1 . "lt/agt\n" )
• echo( "lt/ulgt" )
• echo ( "lt/bodygtlt/htmlgt" )
• ?gt

23
get_joke.php

• lt?php
• echo ( "
• lthtmlgt
• ltheadgtlttitlegtJokes Databaselt/titlegtlt/headgt
• ltbodygt")
• // Connect to database
• include( "connect.php" )
• // Show selected Joke
• SQL "SELECT question, answer FROM jokes WHERE
  id\"" . _GET 'id' . "\""
• result mysql_query( SQL, db )
• if( result )
• row mysql_fetch_row( result )
• echo( "" . row 0 . " ltbr/ gt - " . row 1
  . "\n" )
• else
• echo( "Failed to load" )

24
To get the application working (and to understand
it)

• Provided you have followed the steps correctly,
  you should now be able to execute
  list_jokes.php from the Nokia 5100 or another
  emulator/phone browser
• Play around with the code read around PHP and
  MySQL much more extensively if you wish to use it
  for a project (we havent looked at how to input
  to a database from a WAP page for example)

25
Other server-side possibilities

• JSP (Java Server Pages) account is available from
  http//jsp.inf.brad.ac.uk/
• ASP can be set up locally on a personal machine
  using Microsoft IIS
• .NET applications (.NET is available free to
  students)

26
Review

• We looked at plugging PHP, MySQL and WAP together
  using the example of a jokes database
• Almost identical techniques can be used to
  produce WML pages (and regular web pages)
• PHP talks to the MySQL database and generates
  XHTML, WML or any output you choose, as defined
  by you

27
Investigate

• You will need to dig deeper into many of the
  resources and links suggested in the lecture
  notes throughout the course
• As you can see, there are many choices involved
  in deciding which combination of technologies to
  use for mobile content and applications.

28
Further reading (beyond topic)

• http//developer.openwave.com/dvl/support/document
  ation/guides_and_references/xhtml-mp_style_guide/c
  hapter5.htm (User Input using XHTML MP)
• http//wurfl.sourceforge.net/help_doc.php (The
  WURFL is an XML configuration file which contains
  information about capabilities and features of
  several wireless devices.)
• http//www.xml.com/pub/a/2004/04/14/mobile.html?pa
  ge2 (Using WURFL and PHP to detect device type)