MySQL Databases

1
MySQL Databases PHP Integration

• Using PHP to write data to, and retrieve data
  from, a MySQL database

2
Interacting with Databases

• We used XML pages statically to act as databases
• Relational Database
• Think of an Excel document where each worksheet
  is a table within the document (database)
• A table is rows / columns while a database is
  records / fields
• Data is stored in database, not RAM as with XML

3
MySQL Command Line

• Login mysql u username p (prompt for
  password)
• SHOW DATABASES, CREATE, ALTER, DROP, SELECT,
  INSERT, UPDATE, DELETE
• Must use commands (in all caps) and semicolons
  () at the end to execute strings

4
phpMyAdmin

• Web-based database management system written in
  PHP
• Used to manipulate any database item, view items,
  perform queries, etc
• Table Creation
• Field Name, Type (usually VARCHAR), Length
  Values, Collation, Attributes, Null, Default,
  Extra
• Login using your username (flastname) and
  password (password)

5
Databases and PHP

• PHP function mysqli_connect / mysqli_close
• Test our connection to our database using
  PHPlt?php dbconnect mysqli_connect(192.168.2
  54.124, flastname, password) echo
  ltpgtMySQL Client Version , mysqli_get_client_inf
  o(), lt/pgt echo ltpgtMySQL Connection ,
  mysqli_get_host_info(dbconnect), lt/pgt echo
  ltpgtMySQL Protocol Version , mysqli_get_proto_in
  fo(dbconnect), lt/pgt echo ltpgtMySQL Server
  Version , mysqli_get_server_info(dbconnect),
  lt/pgt mysqli_close(dbconnect)?gt

6
sample1.html sample1.php

• sample1.html grabs user input in an HTML form
  and holds it in memory
• sample1.php connects to a MySQL database for
  the insertion of the data to the table
• http//192.168.254.124/instructor/CIS115-Spring20
  11/Lecture5/
• View sample1.html and sample1.phps code
• View phpMyAdmin to see the customer database
  sample1 table records

7
dbexample1.html dbexample1.php

• dbexample1.html provides a username/password
  field for logging into the database
• dbexample1.php connects to a MySQL database for
  the selection and viewing of table data in an
  organized table
• http//192.168.254.124/instructor/CIS115-Spring20
  11/Lecture5/
• View dbexample1.html and dbexample1.phps code
• View phpMyAdmin to see the customer database
  sample1 table records

8
order.html order.php

• order.html gathers data and sends it to order.php
  for verification
• Nothing is written to the databasedata is just
  reprinted on the screen
• http//192.168.254.124/instructor/CIS115-Spring20
  11/Lecture5/orders/

9
orderdb.html orderdb.php

• orderdb.html gathers data and sends it to
  orderdb.php for verification
• orderdb.php inputs that data into a database for
  storage using fields corresponding to the form
  variables
• http//192.168.254.124/instructor/CIS115-Spring20
  11/Lecture5/orders/

10
orderdboutput.html orderdboutput.php

• orderdboutput.html provides a login form for a
  user to log in and view the contents of the
  database table
• orderdboutput.php grabs the entered
  username/password, checks it against the databse,
  then displays the table records in an organized
  HTML table
• http//192.168.254.124/instructor/CIS115-Spring20
  11/Lecture5/orders/