PHP and MySQL

1. PHP and MySQL CS380

2. How Web Site Architectures Work • 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

3. How Web Site Architectures Work • 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

4. Querying a database from the web • 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

5. PHP MySQL functions CS380

6. Other MySQL PHP functions CS380

7. Insert in a database • Insert new values to columns of a table INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);SQL $isbn=$_POST['isbn']; $author=$_POST['author']; $title=$_POST['title']; $price=$_POST['price']; $query = "insert into books values ('".$isbn."', '".$author."', '".$title."', '".$price."')";SQL CS380

8. Practice: Bookorama • Query database using forms • Insert data in database CS380

9. Database Design Appendix A CS380

10. Database design principles • 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

11. Database design principles • 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

12. First database design • 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

13. Second database design • 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

14. Second database design • primary key: a table column guaranteed to be unique for each record • record in Student table with id of 888 is Lisa Simpson's student info CS380

15. Second database design • 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 Simpson's course grades

16. Design question • 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?

17. Design answer • 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