Transcript Accessing the MySQL Database with PHP

1
PHP and MySQL
CS380
How Web Site Architectures Work
2







User’s browser sends HTTP request.
The request may be a form where the action is to
call PHP code (ex. results .php).
Web server receives the request.
Web server passes the file (results.php) to the PHP
engine.
PHP engine parses the script.
PHP opens connection to MySQL server if needed.
PHP sends query to database server.
CS380
How Web Site Architectures Work
3






Database server processes the query.
Database sends back results to PHP server.
PHP formats data that it received from database
server nicely for HTML.
PHP engine finishes running script.
PHP returns HTML to the web server.
Web server passes HTML back to the browser.
CS380
Querying a database from the web
4





Check and filter data coming from the user.
Setup connection to the appropriate database.
Query the database.
Retrieve the results.
Present the results back to user.
CS380
PHP MySQL functions
5
name
description
mysql_connect
connects to a database server
mysql_select_db
chooses which database on server to
use (similar to SQL USE database;
command)
mysql_query
performs a SQL query on the
database
mysql_real_escape_string
encodes a value to make it safe for
use in a query
mysql_fetch_array, ...
returns the query's next result row as
an associative array
mysql_close
closes a connection to a database
CS380
Other MySQL PHP functions
6
name
description
mysql_num_rows
returns number of rows matched by
the query
mysql_num_fields
returns number of columns per result in
the query
mysql_list_dbs
returns a list of databases on this
server
mysql_list_tables
returns a list of tables in current
database
mysql_list_fields
returns a list of fields in the current
data
complete list
CS380
Insert in a database
7
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
SQL

Insert new values to columns of a table
$isbn=$_POST['isbn'];
$author=$_POST['author'];
$title=$_POST['title'];
$price=$_POST['price'];
$query = "insert into books values
('".$isbn."', '".$author."', '".$title."', '".$price."')";
SQL
CS380
Practice: Bookorama
8


Query database using forms
Insert data in database
CS380
9
Database Design
Appendix A
CS380
Database design principles
10


database design: the act of deciding the schema
for a database
database schema: a description of what tables a
database should have, what columns each table
should contain, which columns' values must be
unique, etc.
CS380
Database design principles
11

some database design principles:
 keep
it simple, stupid (KISS)
 provide an identifier by which any row can be uniquely
fetched
 eliminate redundancy, especially of lengthy data
(strings)
 integers are smaller than strings and better to repeat
 integers can be compared/searched more quickly than
strings, real numbers
CS380
First database design
12

what's good and bad about this design?
good: simple (one table), can see all data in one place
 bad: redundancy (name, email, course repeated frequently)
 bad: most searches (e.g. find a student's courses) will have
to rely on string comparisons
 bad: there is no single column whose value will be unique in
each row

Second database design
13



splitting data into multiple tables avoids redundancy
normalizing: splitting tables to improve structure and
remove redundancy / anomalies
normalized tables are often linked by unique integer
IDs
Second database design
14

primary key: a table column guaranteed to be
unique for each record

CS380
record in Student table with id of 888 is Lisa Simpson's
student info
Second database design
15


records of one table may be associated with record(s) in
another table
foreign key: a column in table A that stores a value of a
primary key from another table B
 records in Grade table with student_id of 888 are Lisa
Design question
16

suppose we want to keep track of the teachers who teach
each course




e.g. Ms. Krabappel always teaches CSE 142 and INFO 100
e.g. Ms. Hoover always teaches CSE 143
e.g. Mr. Stepp always teaches CSE 190M
what tables and/or columns should we add to the database?
Design answer
17



add a teachers table containing information about
instructors
link this to courses by teacher IDs
why not just skip the teachers table and put the
teacher's name as a column in courses?

repeated teacher names are redundant and large in size