PHP

1
PHP

• MySQL

2
SQL Tables

• CREATE TABLE tablename
• fieldname type(length) extra info,...
• Extra info
• NULL (allows nulls in this field)
• Not NULL (null is not allowed value for this
  field)
• AUTO_INCREMENT
• (valuebiggest value in this field among existing
  1

3
SQL Tables

• Field TypeDescription
• TINYINT Small Integer Number
• SMALLINT Small Integer Number
• MEDIUMINT Integer Number
• INT Integer Number
• VARCHAR Text (maximum 255 characters)
• TEXT Text

4
SQL Tables Example

• CREATE TABLE birthdays (
• id INT NOT NULL AUTO_INCREMENT,
• PRIMARY KEY(id),
• Name VARCHAR(30),
• Birthday VARCHAR(7)
• )

5
SQL

• Select or list of fields From table_name Where
  conditions
• Select from birthdays
• Select Name from birthdays where idgt12 And
  birthdayJune4
• Delete from table_name Where conditions
• Delete from birthdays where namePeggy

6
SQL

• Insert Into table_name (fields list)
  VALUES(fields values)
• INSERT INTO birthdays (name, birthday) VALUES
  ('Peggy', 'June4')
• Update table_name Set fieldvalue,... Where
  conditions
• UPDATE birthdays SET birthday2003.01.10 WHERE
  name'Peggy'

7
PHP/MySQL Connection

• resource mysql_connect ( string server , string
  username , string password)
• Open a connection to a MySQL Server . If you
  would like to use more than
• one connection then use returned parameter to
  identify connection for later
• mysql functions calls.
• bool mysql_select_db ( string database_name ,
  link_identifier)
• sets the current active database on the server
  that's associated with the specified link
  identifier. If no link identifier is specified,
  the last opened link is assumed. If no link is
  open, the function will try to establish a link
  as if mysql_connect() was called without
  arguments, and use it.
• bool mysql_close ( link_identifier)Close
  connection that's associated with the specified
  link identifier. If link_identifier isn't
  specified, the last opened link is used.

8
Example

• lt?
• user"username"password"password"database
  "database"
• mysql_connect(localhost,user,password)_at_mysq
  l_select_db(database) or die( "Unable to select
  database")
• ...mysql_close()
• ?gt

9
Example 2

• lt?link mysql_connect( "localhost",
  _POST'username', _POST'password') or die
  ("Connect Error ".mysql_error())
• print "Successfully connected.\n"
• mysql_close(link)
• ?gt

10
PHP/MySQL Working with

• resource mysql_query(string query,
  link_identifier)
• sends a query to the currently active database
  on the server that's associated with the
  specified link identifier.
• mixed mysql_result ( resource result, int row ,
  mixed field)Returns the contents of one cell
  from a MySQL result set.
• variablemysql_result(result,i,"fieldname")
• variablemysql_result(result,2,0) //Returns a
  value of the first column value of the third
  record
• int mysql_num_rows ( resource result)
• Returns the number of rows in a result set.
• PSThis command is only valid for SELECT
  statements. To retrieve the number of rows
  affected by a INSERT, UPDATE or DELETE query, use
  mysql_affected_rows().

11
Example

• lt?usernameu" passwordp"
  databasedb"mysql_connect(localhost,username,
  password)_at_mysql_select_db(database) or die(
  "Unable to select database")
• query"SELECT FROM contacts"resultmysql_qu
  ery(query)nummysql_num_rows(result)mysql_c
  lose()echo "ltbgtltcentergtDatabase
  Outputlt/centergtlt/bgtltbrgtltbrgt" i0while (i lt
  num) phonemysql_result(result,i,"phone")
  emailmysql_result(result,i,"email")webmysql
  _result(result,i,"web")
• echo "Phone phoneltbrgtMobile
  mobileltbrgtE-mail emailltbrgtWeb
  webltbrgtlthrgtltbrgt"
• i
• ?gt

12
Example

• lthtmlgtltheadgtlttitlegt(Title Here)lt/titlegtlt/headgtltbod
  ygtlt?phpdb"mydatabase"link
  mysql_connect("localhost")if (! link)
  die("Couldn't connect to MySQL")mysql_select_db(
  db , link) or die("Couldn't open db
  ".mysql_error())
• result mysql_query("SELECT FROM birthdays")
  or die("SELECT Error ".mysql_error())num_rows
  mysql_num_rows(result)print "There are
  num_rows records.ltPgt"print "lttable width200
  border1gt\n"while (get_info
  mysql_fetch_row(result)) print
  "lttrgt\n" foreach (get_info as field) print
  "\tlttdgtltfont facearial size1/gtfieldlt/fontgtlt/tdgt
  \n"print "lt/trgt\n"print "lt/tablegt\n"
• mysql_close(link)?gtlt/bodygtlt/htmlgt