PHP MySQL

1
PHP MySQL Introduction

• What is MySQL?
• MySQL is a database.
• The data in MySQL is stored in database objects
  called tables.
• A table is a collections of related data entries
  and it consists of columns and rows.
• Databases are useful when storing information
  categorically.
• A company may have a database with the following
  tables "Employees", "Products", "Customers" and
  "Orders".

2
Introduction

• Before you actually start building your database
  scripts, you must have a database to place
  information into and read it from. In this
  section I will show you how to create a database
  in MySQL and prepare it for the data. I will also
  begin to show you how to create the contacts
  management database.

3
Database Construction

• MySQL databases have a standard setup. They are
  made up of a database, in which is contained
  tables. Each of these tables is quite separate
  and can have different fields etc. even though it
  is part of one database. Each table contains
  records which are made up of fields.

4
Databases And Logins

• The process of setting up a MySQL database
  varies from host to host, you will however end up
  with a database name, a user name and a password.
  This information will be required to log in to
  the database.If you have PHPMyAdmin (or a
  similar program) installed you can just go to it
  to log in with your user name and password. If
  not you must do all your database administration
  using PHP scripts.

5
Fields

• There are a wide variety of fields and attributes
  available in MySQL and I will cover a few of
  these here
• These are just a few of the fields which are
  available. A search on the internet will provide
  lists of all the field types allowed.

Field Type Description
TINYINT Small Integer Number
SMALLINT Small Integer Number
MEDIUMINT Integer Number
INT Integer Number
VARCHAR Text (maximum 256 characters)
TEXT Text
6
Creating A Table With PHP

• To create a table in PHP is slightly more
  difficult than with MySQL. It takes the following
  formatCREATE TABLE tablename FieldsThe
  fields are defined as followsfieldname
  type(length) extra info,The final field entered
  should not have a comma after it.I will give
  full an example of using these later in the
  section

7
Creating A Table

• Before you can do anything with your database,
  you must create a table. A table is a section of
  the database for storing related information. In
  a table you will set up the different fields
  which will be used in that table. Because of this
  construction, nearly all of a site's database
  needs can be satisfied using just one
  database.Creating a table in PHPMyAdmin is
  simple, just type the name, select the number of
  fields and click the button. You will then be
  taken to a setup screen where you must create the
  fields for the database. If you are using a PHP
  script to create your database, the whole
  creation and setup will be done in one command.

8
The Contacts Database

• The contacts database will contain all the conact
  information for the people you enter and the
  information will be able to be edited and viewed
  on the internet. The following fields will be
  used in the database

9
The Contacts Database
Name Type Length Description
id INT 6 A unique identifier for each record
first VARCHAR 15 The person's first name
last VARCHAR 15 The person's last name
phone VARCHAR 20 The person's phone number
mobile VARCHAR 20 The person's mobile number
fax VARCHAR 20 The person's fax number
email VARCHAR 30 The person's e-mail address
web VARCHAR 30 The person's web address
10
The Contacts Database

• You may be wondering why I have used VARCHAR
  fields for the phone/fax numbers even though they
  are made up of digits. You could use INT fields
  but I prefer to use VARCHAR as it will allow
  dashes and spaces in the number, as well as
  textual numbers (like 1800-COMPANY) and as we
  will not be initiating phone calls from the web
  it is not a problem.There is one other thing
  you should be aware of in this database. The id
  field will also be set as PRIMARY, INDEX, UNIQUE
  and will be set to auto_increment (found under
  Extra in PHPMyAdmin). The reason for this is
  that this will be the field identifier (primary
  and index) and so must be unique. The auto
  increment setting means that whenever you add a
  record, as long as you don't specify an id, it
  will be given the next number.If you are using
  PHPMyAdmin or a management program you can now
  create this in a table called contacts.

11

• Database Tables
• A database most often contains one or more
  tables. Each table is identified by a name (e.g.
  "Customers" or "Orders").
• Tables contain records (rows) with data.
• Below is an example of a table called "Persons"
• LastName FirstName Address City
• Hansen Ola Timoteivn10
  Joyland jhon lock
  Borgvn 23 MoreTown Pettersen
  Kari Storgt 20 GSU Landings

12

• The table above contains three records (one for
  each person) and four columns (LastName,
  FirstName, Address, and City)
• Queries
• A query is a question or a request.
• With MySQL, we can query a database for specific
  information and have a recordset returned.
• Look at the following query
• SELECT LastName FROM Persons
• The query above selects all the data in the
  "LastName" column from the "Persons" table, and
  will return a recordset like this
• LastName
• Hansen
• john
• Blake

13

• Download MySQL Database
• If you don't have a PHP server with a MySQL
  Database, you can download MySQL for free
  herehttp//www.mysql.com/downloads/index.html

14
Creating The Table In PHP

• The following code should be used to create this
  table in PHP. Some of the code has not been
  covered yet but I will explain it fully in the
  next part.lt?user"username"password"passwo
  rd"database"database"mysql_connect(localhost
  ,user,password)_at_mysql_select_db(database) or
  die( "Unable to select database")query"CREATE
  TABLE contacts (id int(6) NOT NULL
  auto_increment,first varchar(15) NOT NULL,last
  varchar(15) NOT NULL,phone varchar(20) NOT
  NULL,mobile varchar(20) NOT NULL,fax varchar(20)
  NOT NULL,email varchar(30) NOT NULL,web
  varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id
  (id),KEY id_2 (id))"mysql_query(query)mysql_c
  lose()?gtEnter your database, MySQL username
  and MySQL password in the appropriate positions
  on the first three lines above.

15
PHP MySQL Connect to a Database

• Create a Connection to a MySQL Database
• Before you can access data in a database, you
  must create a connection to the database.
• In PHP, this is done with the mysql_connect()
  function.
• Syntax
• mysql_connect(servername,username,password)Param
  eter Description
• Servername Optional. Specifies the server to
  connect to. Default value is "localhost3306
• Username Optional. Specifies the username to log
  in with. Default value is the name of the user
  that owns the server process
• passwordOptional. Specifies the password to log
  in with. Default is ""

16
Connecting To The Database

• The first thing you must do before you can do any
  work at all is to connect to the MySQL database.
  This is an extremely important step as, if you
  are not connected, your commands to the database
  will fail.Good practice for using databases is
  to specify the username, password and database
  name first so that if you change any of them at a
  later date you will only have to change one
  lineusername"username"password"password"
  database"your_database"

17
connection

• At this point you may be wondering if it is a
  security risk, keeping your password in the file.
  You don't need to worry, though, because the PHP
  scource code is processed aby the server before
  being sent to the browser so it is impossible for
  the user to see the script's source.Next, you
  will need to issue the command to start a
  database connectionmysql_connect(localhost,use
  rname,password)This line tells PHP to connect
  to the MySQL database server at 'localhost'
  (localhost means the server that the site is
  running one. Unless you web host tells you
  otherwise you should use localhost. If you are
  given a server address (such as sql.myserver.com
  you should replace localhost with
  "sql.myserver.com" (including the quotes)) using
  the username stored in username and the password
  in password.Before I show you how to work with
  the database, I will show you one more
  commandmysql_close()

18
Connection cont

• This is a very important command as it closes the
  connection to the database server. Your script
  will still run if you do not include this command
  but too many open MySQL connections can cause
  problems for a web host. It is good practice to
  always include this line once you have issued all
  your commands to the database, to keep the server
  running well.

19

• Example
• In the following example we store the connection
  in a variable (con) for later use in the script.
  The "die" part will be executed if the connection
  fails
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  // some code?gt

20

• Closing a Connection
• The connection will be closed automatically when
  the script ends. To close the connection before,
  use the mysql_close() function
• lt?phpcon mysql_connect("localhost","peter","abc
  123")if (!con)    die('Could not connect '
  . mysql_error())  // some codemysql_close(
  con

21
PHP MySQL Create Database and Tables

• Create a Database
• The CREATE DATABASE statement is used to create a
  database in MySQL.
• Syntax
• CREATE DATABASE database_name
• To get PHP to execute the statement above we must
  use the mysql_query() function.
• This function is used to send a query or command
  to a MySQL connection.

22

• Example
• The following example creates a database called
  "my_db"
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  if (mysql_query("CREATE
  DATABASE my_db",con))    echo "Database
  created"  else    echo "Error creating
  database " . mysql_error()  mysql_close(con
  )?gt

23

• Create a Table
• The CREATE TABLE statement is used to create a
  table in MySQL.
• Syntax
• CREATE TABLE table_name(column_name1
  data_type,column_name2 data_type,column_name3
  data_type,....)
• We must add the CREATE TABLE statement to the
  mysql_query() function to execute the command

24

• Example
• The following example creates a table named
  "Persons", with three columns. The column names
  will be "FirstName", "LastName" and "Age"
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  // Create databaseif
  (mysql_query("CREATE DATABASE my_db",con))    
  echo "Database created"  else    echo
  "Error creating database " . mysql_error()  
  // Create tablemysql_select_db("my_db",
  con)sql "CREATE TABLE Persons(FirstName
  varchar(15),LastName varchar(15),Age
  int)"// Execute querymysql_query(sql,con)
  mysql_close(con)?gt

25

• Important A database must be selected before a
  table can be created. The database is selected
  with the mysql_select_db() function.
• Note When you create a database field of type
  varchar, you must specify the maximum length of
  the field, e.g.
• varchar(15).
• The data type specifies what type of data the
  column can hold. 

26
Primary Keys and Auto Increment Fields

• Each table should have a primary key field.
• A primary key is used to uniquely identify the
  rows in a table.
• Each primary key value must be unique within the
  table. Furthermore, the primary key field cannot
  be null because the database engine requires a
  value to locate the record.
• The following example sets the personID field as
  the primary key field. The primary key field is
  often an ID number, and is often used with the
  AUTO_INCREMENT setting.
• AUTO_INCREMENT automatically increases the value
  of the field by 1 each time a new record is
  added. To ensure that the primary key field
  cannot be null, we must add the NOT NULL setting
  to the field.

27

• Example
• sql "CREATE TABLE Persons (personID int NOT
  NULL AUTO_INCREMENT, PRIMARY KEY(personID),First
  Name varchar(15),LastName varchar(15),Age
  int)"mysql_query(sql,con)

28
PHP MySQL Insert Into

• Insert Data Into a Database Table
• The INSERT INTO statement is used to add new
  records to a database table.
• Syntax
• It is possible to write the INSERT INTO statement
  in two forms.
• The first form doesn't specify the column names
  where the data will be inserted, only their
  values
• INSERT INTO table_nameVALUES (value1, value2,
  value3,...)
• The second form specifies both the column names
  and the values to be inserted
• INSERT INTO table_name (column1, column2,
  column3,...)VALUES (value1, value2, value3,...)

29

• To get PHP to execute the statements above we
  must use the mysql_query() function. This
  function is used to send a query or command to a
  MySQL connection.
• Example
• In the previous chapter we created a table named
  "Persons", with three columns "Firstname",
  "Lastname" and "Age".
• We will use the same table in this example. The
  following example adds two new records to the
  "Persons" table

30

• ?phpcon mysql_connect("localhost","peter","abc
  123")if (!con)    die('Could not connect '
  . mysql_error())  mysql_select_db("my_db",
  con)mysql_query("INSERT INTO Persons
  (FirstName, LastName, Age)VALUES ('Peter',
  'Griffin', '35')")mysql_query("INSERT INTO
  Persons (FirstName, LastName, Age) VALUES
  ('Glenn', 'Quagmire', '33')")mysql_close(con)
  ?gt

31
Insert Data From a Form Into a Database

• Now we will create an HTML form that can be used
  to add new records to the "Persons" table.
• Here is the HTML form
• lthtmlgtltbodygtltform action"insert.php"
  method"post"gtFirstname ltinput type"text"
  name"firstname" /gtLastname ltinput type"text"
  name"lastname" /gtAge ltinput type"text"
  name"age" /gtltinput type"submit"
  /gtlt/formgtlt/bodygtlt/html

32

• When a user clicks the submit button in the HTML
  form in the example above, the form data is sent
  to "insert.php".
• The "insert.php" file connects to a database, and
  retrieves the values from the form with the PHP
  _POST variables.
• Then, the mysql_query() function executes the
  INSERT INTO statement, and a new record will be
  added to the "Persons" table.
• Here is the "insert.php" page

33

• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)sql"INSERT INTO Persons (FirstName,
  LastName, Age)VALUES('_POSTfirstname','_POST
  lastname','_POSTage')"if
  (!mysql_query(sql,con))    die('Error ' .
  mysql_error())  echo "1 record
  added"mysql_close(con)?gt

34
PHP MySQL Select

• Select Data From a Database Table
• The SELECT statement is used to select data from
  a database.
• Syntax
• SELECT column_name(s)FROM table_name
• To get PHP to execute the statement above we must
  use the mysql_query() function. This function is
  used to send a query or command to a MySQL
  connection.

35

• Example
• The following example selects all the data stored
  in the "Persons" table (The character selects
  all the data in the table)
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)result mysql_query("SELECT FROM
  Persons")while(row mysql_fetch_array(result
  ))    echo row'FirstName' . " " .
  row'LastName'  echo "ltbr /gt"  
  mysql_close(con)?gt

36

• The example above stores the data returned by the
  mysql_query() function in the result variable.
• Next, we use the mysql_fetch_array() function to
  return the first row from the recordset as an
  array.
• Each call to mysql_fetch_array() returns the next
  row in the recordset.
• The while loop loops through all the records in
  the recordset.
• To print the value of each row, we use the PHP
  row variable (row'FirstName' and
  row'LastName').

37

• The output of the code above will be
• Peter GriffinGlenn Quagmire

38
Display the Result in an HTML Table

• The following example selects the same data as
  the example above, but will display the data in
  an HTML table
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)result mysql_query("SELECT FROM
  Persons")echo "lttable border'1'gtlttrgtltthgtFirs
  tnamelt/thgtltthgtLastnamelt/thgtlt/trgt"while(row
  mysql_fetch_array(result))    echo
  "lttrgt"  echo "lttdgt" . row'FirstName' .
  "lt/tdgt"  echo "lttdgt" . row'LastName' .
  "lt/tdgt"  echo "lt/trgt"  echo
  "lt/tablegt"mysql_close(con)?gt

39

• The output of the code above will be

Firstname Lastname
Glenn Quagmire
Peter Griffin
40
PHP MySQL The Where Clause

• he WHERE clause
• The WHERE clause is used to extract only those
  records that fulfill a specified criterion.
• Syntax
• SELECT column_name(s)FROM table_nameWHERE
  column_name operator value
• To get PHP to execute the statement above we must
  use the mysql_query() function.
• This function is used to send a query or command
  to a MySQL connection.

41

• example
• The following example selects all rows from the
  "Persons" table where "FirstName'Peter'
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)result mysql_query("SELECT FROM
  PersonsWHERE FirstName'Peter'")
• while(row mysql_fetch_array(result))    ec
  ho row'FirstName' . " " . row'LastName'  e
  cho "ltbr /gt"  ?gt

42

• The output of the code above will be
• Peter Griffin

43
PHP MySQL Order By Keyword

• The ORDER BY keyword is used to sort the data in
  a recordset.
• The ORDER BY Keyword
• The ORDER BY keyword is used to sort the data in
  a recordset.
• The ORDER BY keyword sort the records in
  ascending order by default.
• If you want to sort the records in a descending
  order, you can use the DESC keyword.
• Syntax
• SELECT column_name(s)FROM table_nameORDER BY
  column_name(s) ASCDESC

44

• Example
• The following example selects all the data stored
  in the "Persons" table, and sorts the result by
  the "Age" column
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)result mysql_query("SELECT FROM
  Persons ORDER BY age")while(row
  mysql_fetch_array(result))    echo
  row'FirstName'  echo " " .
  row'LastName'  echo " " . row'Age'  echo
  "ltbr /gt"  mysql_close(con)?gt

45

• The output of the code above will be
• Glenn Quagmire 33Peter Griffin 35
• Order by Two Columns
• It is also possible to order by more than one
  column. When ordering by more than one column,
  the second column is only used if the values in
  the first column are equal
• SELECT column_name(s)FROM table_nameORDER BY
  column1, column2

46
PHP MySQL Update

• The UPDATE statement is used to modify data in a
  table.
• Update Data In a Database
• The UPDATE statement is used to update existing
  records in a table.
• Syntax
• UPDATE table_nameSET column1value,
  column2value2,...WHERE some_columnsome_value
• Note Notice the WHERE clause in the UPDATE
  syntax. The WHERE clause specifies which record
  or records that should be updated. If you omit
  the WHERE clause, all records will be updated!

47

• To get PHP to execute the statement above we must
  use the mysql_query() function. This function is
  used to send a query or command to a MySQL
  connection.
• Example
• Earlier in the tutorial we created a table named
  "Persons". Here is how it looks
• FirstName LastName Age
• Peter Griffin
  35
• Glenn Quagmire
  33

48

• The following example updates some data in the
  "Persons" table
• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)mysql_query("UPDATE Persons SET Age
  '36'WHERE FirstName 'Peter' AND LastName
  'Griffin'")mysql_close(con)?gt

49

• After the update, the "Persons" table will look
  like this
• FirstName LastName Age
• Peter Griffin
  36
• Glenn Quagmire 33

50
PHP MySQL Delete

• The DELETE statement is used to delete records in
  a table.
• Delete Data In a Database
• The DELETE FROM statement is used to delete
  records from a database table.
• Syntax
• DELETE FROM table_nameWHERE some_column
  some_value
• Note Notice the WHERE clause in the DELETE
  syntax. The WHERE clause specifies which record
  or records that should be deleted. If you omit
  the WHERE clause, all records will be deleted!

51

• To get PHP to execute the statement above we must
  use the mysql_query() function. This function is
  used to send a query or command to a MySQL
  connection.
• Example
• Look at the following "Persons" table
• FirstName LastName Age
• Peter Griffin
  35
• Glenn Quagmire 33
• The following example deletes all the records in
  the "Persons" table where LastName'Griffin'

52

• lt?phpcon mysql_connect("localhost","peter","ab
  c123")if (!con)    die('Could not connect
  ' . mysql_error())  mysql_select_db("my_db",
  con)mysql_query("DELETE FROM Persons WHERE
  LastName'Griffin'")mysql_close(con)?gt
• After the deletion, the table will look like
  this
• FirstName LastName Age
• Glenn Quagmire 33

53
PHP Database ODBC

• ODBC is an Application Programming Interface
  (API) that allows you to connect to a data source
  (e.g. an MS Access database).
• Create an ODBC Connection
• With an ODBC connection, you can connect to any
  database, on any computer in your network, as
  long as an ODBC connection is available.
• Here is how to create an ODBC connection to a MS
  Access Database 
• Open the Administrative Tools icon in your
  Control Panel.
• Double-click on the Data Sources (ODBC) icon
  inside.
• Choose the System DSN tab.
• Click on Add in the System DSN tab.
• Select the Microsoft Access Driver. Click Finish.
• In the next screen, click Select to locate the
  database.
• Give the database a Data Source Name (DSN).
• Click OK.

54

• Note that this configuration has to be done on
  the computer where your web site is located. If
  you are running Internet Information Server (IIS)
  on your own computer, the instructions above will
  work, but if your web site is located on a remote
  server, you have to have physical access to that
  server, or ask your web host to to set up a DSN
  for you to use.