afea 1

1
?????aµµat?sµ?? ??ad??t???
LECTURE 8
Using Databases with PHP Scripts Using MySQL
Database with PHP
??. Ge?????? F. F?a???????
2
Objectives

• To understand the advantages of using databases
  to store Web data
• To learn how to prepare a MySQL database for use
  with PHP
• To learn how to store, retrieve, and update data
  in a MySQL database
• !!!!!!!!!!!! SOS !!!!!!!!
• my.ini of MySQL must have the following line
  in order to see GREEK Characters !!!!!!!!!!!
• default-character-setgreek

3
What is a database?

• A set of data organized into one or more computer
  files.
• Using files for product inventory is a type of
  database
• Generally the term is reserved for more formal
  database systems like access, Oracle or MySQL.

4
Advantages of Databases Over Files

• Faster access
• Better concurrent access
• Easier changes to data and scripts
• Increased security

5
Relational Database?

• Relational databases store data in tables
  (usually more than one) with defined
  relationships between the tables.

6
Which Database System

• PHP works with a variety of databases that
  include
• Oracle
• Access
• Ingres
• SQL Server
• MySQL
• Will use MySQL since simple to use, free and very
  popular.

7
Using A Query Language

• When using a database, use a separate query
  language to work with database
• Within MySQL, use Structured Query Language
  (SQL), to access database

8
Creating a Database Instance

• Once you have access to a server with MySQL
  installed, need to get a database instance
  created for you.
• Usually created by a database administrator
• Creates a database instance, userid and password.

9
Creating Your Database Tables

• Once database instance is created need to create
  your tables.
• Use SQL CREATE TABLE command

10
Other Data Types?

• MySQL supports many other data types beyond TEXT
  and INT. Here are a few
• TEXT specifies that the table column can hold a
  large amount of character data. It can use space
  inefficiently since it reserves space for up to
  65,535 characters.
• CHAR(N) specifies a table column that holds a
  fixed length string of up to N characters (N must
  be less than 256).
• VARCAR(N) specifies a table column that holds a
  variable length string of up to N characters and
  removes any unused spaces on the end of the entry.

11
Other Data Types?

• INT specifies a table column that holds an
  integer with a value from about 2 billion to
  about 2 billion.
• INT UNSIGNED specifies a table column that holds
  an integer with a value from 0 to about 4
  billion.
• SMALLINT specifies a table column that holds an
  integer with a value from 32,768 to 32,767.
• SMALLINT UNSIGNED specifies a table column that
  holds an integer with a value from 0 to 65,535.
• DECIMAL(N,D) specifies a number that supports N
  total digits, of which D digits are to the right
  of the decimal point.

12
Some additional CREATE TABLE Options

• Can specify some additional options in CREATE
  TABLE

13
Issuing CREATE TABLE From PHP Script Segment

• 1. connect mysql_connect(server, user,
  pass)
• 2. if ( !connect )
• 3. die ("Cannot connect to server using
  user")
• 4. else
• 5. mysql_select_db('MyDatabaseName')
• 6. SQLcmd 'CREATE TABLE Products(
• ProductID INT
  UNSIGNED NOT NULL
• AUTO_INCREMENT
  PRIMARY KEY,
• Product_desc
  VARCHAR(50), Cost INT,
• Weight INT,
  Numb INT )'
• 7. mysql_query(SQLcmd, connect)
• 8. mysql_close(connect)
• 9.

Connect to MySQL
Issue the SQL query to the database.
14
Full Script

• 1. lthtmlgtltheadgtlttitlegtCreate Tablelt/titlegtlt/headgtlt
  bodygt
• 2. lt?php
• 3. server 'localhost'
• 4. user root'
• 5. pass 'mypasswd'
• 6. mydb 'mydatabase'
• 7. table_name 'Products'
• 8. connect mysql_connect(server, user,
  pass)
• 9. if (!connect)
• 10. die ("Cannot connect to server using
  user")
• 11. else
• 12. SQLcmd "CREATE TABLE
  table_name (
• ProductID INT UNSIGNED NOT
  NULL
• AUTO_INCREMENT PRIMARY KEY,
• Product_desc VARCHAR(50),
• Cost INT, Weight INT, Numb INT)"

15
Full Script continued

• 13. mysql_select_db(mydb)
• 14. if (mysql_query(SQLcmd, connect))
• 15. print 'ltfont size"4" color"blue"
  gtCreated Table'
• 16. print "ltigttable_namelt/igt in
  databaseltigtmydblt/igtltbrgtlt/fontgt"
• 17. print "ltbrgtSQLcmdSQLcmd"
• 18. else
• 19. die ("Table Create Creation Failed
  SQLcmdSQLcmd")
• 20.
• 21. mysql_close(connect)
• 22.
• 23. ?gtlt/bodygtlt/htmlgt

16
Script Browser Output
17
Inserting Data

• Once database is created will need to insert data
• Use the SQL INSERT command

18
A Full Example

• Consider an application that allows end-user to
  enter inventory data
• Item Description ltinput type"text" size"20"
• maxlength"20" name"Item"gt
• Weight ltinput type"text" size"5"
• maxlength"20" name"Weight"gt
• Cost ltinput type"text" size"5"
• maxlength"20" name"Cost"gt
• Number Availableltinput type"text" size"5"
• maxlength"20" name"Quantity"gt

19
Receiving PHP Script

• 1. lthtmlgtltheadgtlttitlegtInsert Resultslt/titlegtlt/head
  gtltbodygt
• 2. lt?php
• 3. host 'localhost'
• 4. user root'
• 5. passwd 'mypasswd'
• 6. database 'mydatabase'
• 7. connect mysql_connect(host, user,
  passwd)
• 8. table_name 'Products'
• 9. query "INSERT INTO table_name VALUES
  ('0','Item','Cost','Weight','Quantity')"
• 10. print "The Query is ltigtquerylt/igtltbrgt"
• 11. mysql_select_db(database)
• 12. print 'ltbrgtltfont size"4" color"blue"gt'
• 13. if (mysql_query(query, connect))
• 14. print "Insert into database was
  successful!lt/fontgt"
• 15. else
• 16. print "Insert into database failed!lt/fontgt"
• 17. mysql_close (connect)
• 18. ?gtlt/bodygtlt/htmlgt

20
Script Output
This script can be executed at
http//webwizard.aw.com/phppgm/C8/insert.html
21
Retrieving Data

• Two major ways to retrieve data
• 1. Retrieving all elements from a table
• 2. Searching for specific records in a table
• To retrieve all data, use following SQL command

22
Retrieving Data

• To retrieve all data, use following SQL command
• For example
• 1. connect mysql_connect('Localhost',
  'phppgm', 'mypasswd')
• 2. mysql_select_db('MyDatabase')
• 3. SQLcmd 'SELECT FROM Products'
• 4. results_id mysql_query(SQLcmd, connect)

23
Using mysql_fetch_row()

• Use the mysql_fetch_row() function to retrieve
  data on row at a time

24
A Script Example

• 1. lthtmlgtltheadgtlttitlegtTable Outputlt/titlegtlt/headgtlt
  bodygt
• 2. lt?php
• 3. host 'localhost'
• 4. user root'
• 5. passwd 'mypasswd'
• 6. database mydatabase'
• 7. connect mysql_connect(host, user,
  passwd)
• 8. table_name 'Products'
• 9. print 'ltfont size"5" color"blue"gt'
• 10. print "table_name Datalt/fontgtltbrgt"
• 11. query "SELECT FROM table_name"
• 12. print "The query is ltigtquery lt/igtltbrgt"
• 13. mysql_select_db(database)
• 14. results_id mysql_query(query, connect)
• 15. if (results_id)
• 16. print 'lttable border1gt'
• 17. print 'ltthgtNumltthgtProductltthgtCostltthgtWeightlt
  thgtCount'

25
A Script Example

• 18. while (row mysql_fetch_row(results_id))
• 19. print 'lttrgt'
• 20. foreach (row as field)
• 21. print "lttdgtfieldlt/tdgt "
• 22.
• 23. print 'lt/trgt'
• 24.
• 25. else die ("Queryquery failed!")
• 26. mysql_close(connect)
• 27. ?gt lt/tablegtlt/bodygtlt/htmlgt

26
Script Output
27
Searching For Specific Records

• Use the SELECT SQL statement with a WHERE clause
• SELECT FROM TableName WHERE (test_expression)

Specify a test expression to evaluate
Specify the table name to look at.
The asterisk () means look at all table
columns.
28
Selected WHERE CLAUSE Test Operators
29
Consider the following example

• The following example searches a hardware
  inventory database for a specific part name
  entered by the user.
• The form uses the following key HTML form element
  definition.
• ltinput type"text" name"Search" size"20"gt

30
PHP Source

• 1. lthtmlgtltheadgtlttitlegtSearch Resultslt/titlegtlt/head
  gtltbodygt
• 2. lt?php
• 3. host 'localhost'
• 4. user root'
• 5. passwd 'mypasswd'
• 6. database mydatabase'
• 7. connect mysql_connect(host, user,
  passwd)
• 8. table_name 'Products'
• 9. print 'ltfont size"5" color"blue"gt'
• 10. print "table_name Datalt/fontgtltbrgt"
• 11. query "SELECT FROM table_name WHERE
• (Product_desc 'Search')"
• 12. print "The query is ltigtquerylt/igt ltbrgt"
• 13. mysql_select_db(database)
• 14. results_id mysql_query(query, connect)

31
PHP Source II

• 15. if (results_id)
• 16. print 'ltbrgtlttable border1gt'
• 17. print 'ltthgtNumltthgtProductltthgtCostltthgtWeight
  ltthgtCount'
• 18. while (row mysql_fetch_row(results_id))
  
• 19. print 'lttrgt'
• 20. foreach (row as field)
• 21. print "lttdgtfieldlt/tdgt "
• 22.
• 23. print 'lt/trgt'
• 24.
• 25. else die ("queryQuery Failed")
• 26. mysql_close(connect)
• 27. ?gt lt/bodygtlt/htmlgt

32
Would have the following output
33
Updating a Database Record

• Use SQL UPDATE command when needing to update a
  database record
• UPDATE Table_name
• SET col1chng_express1,col2chng_express2, . . .
• WHERE test_expression

Specify the name of the table to update.
Specify one or more table column to receive the
results of an expression. Optionally specify a
WHERE
Optionally specify a WHERE clause and test
expression.
34
For Example

• The following searches the Products table for
  values of Product_desc equal to Hammer.
• UPDATE Products
• SET Cost2
• WHERE 'Product_descHammer'

35
For Example

• The following looks through the Products table
  for values of Product_desc equal to Hammer.
• When it finds it, it decrements the Count column
  value by 1.
• UPDATE Products
• SET CountCount-1
• WHERE 'Product_descHammer'

36
A Full Example

• Consider the following example
• Displays current inventory
• Asks end-user to decrement value for 1 item
• Uses the following HTML
• Hammerltinput type"radio name"Product"
  value"Hammer" gt
• Screwdriver ltinput type"radio name"Product"
  value"Screwdriver" gt
• Wrenchltinput type"radio" name"Product
  value"Wrench"

37
Full Example I

• 1. lthtmlgtltheadgtlttitlegtProduct Update
  Resultslt/titlegtlt/headgtltbodygt
• 2. lt?php
• 3. host 'localhost'
• 4. user root'
• 5. passwd 'mypasswd'
• 6. database mydatabase'
• 7. connect mysql_connect(host, user,
  passwd)
• 8. table_name 'Products'
• 9. print 'ltfont size"5" color"blue"gt'
• 10. print "Update Results for Table
  table_namelt/fontgtltbrgt\n"
• 11. query "UPDATE table_name
• SET Numb Numb-1
• WHERE (Product_desc 'Product')"
• 12. print "The query is ltigt query lt/igt
  ltbrgtltbrgt\n"
• 13. mysql_select_db(database)

38
A Full Example II

• 14. results_id mysql_query(query, connect)
• 15. if (results_id)
• 16. Show_all(connect, database,table_name)
• 17. else
• 18. print "Updatequery failed"
• 19.
• 20. mysql_close(connect)

39
A Full Example III

• 21. function Show_all(connect, database,
  table_name)
• 22. query "SELECT from table_name"
• 23. results_id mysql_query(query, connect)
• 24. print 'lttable border1gtltthgt Num ltthgt
  Productltthgt Cost ltthgt Weight ltthgtCount'
• 26. while (row mysql_fetch_row(results_id))
• 27. print 'lttrgt'
• 28. foreach (row as field)
• 29. print "lttdgtfieldlt/tdgt "
• 30.
• 31. print 'lt/trgt'
• 32.
• 33.
• 34. ?gt lt/bodygtlt/htmlgt

40
Would output the following

• Execute this example at http//webwizard.aw.com/p
  hppgm/C8/startsale.html

41
Summary

• A database is a set of data organized into one or
  more computer files. Relational databases store
  data in tables
• Before you can start to work with a MySQL
  database, you must install a copy of MySQL on
  your personal computer or Web server, create a
  database instance for your script and create your
  initial tables by issuing the SQL CREATE TABLE
  command.
• Use the SQL SELECT statement to retrieve data
  from a MySQL database and include a WHERE clause
  to select specific table rows..

42
Summary - II

• Use an SQL UPDATE statement to change records in
  a MySQL database. Include a WHERE clause to
  select specific table rows and a SET clause to
  define change expressions.