Web Design:

1. Web Design: Fall 2010 Mondays 7-9pm 200 Sutardja-Dai Hall Basic to Advanced Techniques PHP & MySQL Lecture Code:

2. Quiz Lecture Lab Today’s Agenda

3. Announcements

4. Review: PHP and MySQL Client Side Server Side Web Design:Basic to Advanced Techniques • Web Server • Serve Website • Send HTML, CSS, and JavaScript files • Send images • Interprets and executes PHP code • PHP code calls/talks to Database • Web Browser • HTTP Request (visit website) • Interpret and render received files • JavaScript Execution

5. Review

6. PHP PHP is a server side scripting language that dynamically writes to HTML pages. PHP can use the server’s resources: Image processors E-mail inboxes Chat services (IRC, AIM, MSN) Databases Client never sees PHP The only code that your client sees is HTML, not PHP

7. Writing PHP Open PHP with <?php Close PHP with ?> Variables begin with a dollar sign: $ PHP files usually end in .php extension

8. User Input GET variables In the URL: http://www.example.com/index.php?page=2 Retrieved by $_GET[‘page’] POST variables Sent by a form <form action=“post”> <input name=“page” value=“2”></form> Retrieved by $_POST[‘page’] Cookies Set by PHP; setcookie(‘page’, ‘2’, time()+3600, ‘/’, ‘example.com’) Retrieved by $_COOKIE[‘page’]

9. Flow Control in PHP: “if” If the conditional is true, then execute the code. • “else” statement – not required. • Only one of these executes!

10. Flow Control in PHP: “while” While the conditional is true, execute the code. Will print out:“0, 1, 2, 3, 4, 5, 6, 7, 8, 9, ” Code may execute more than once, or never execute.

11. Review: SQL Structured Query Language We ask our database about our data, using SQL. Creating, reading, updating and deleting records.

12. Review: CRUD • Create • INSERT INTO <table> (<field>) VALUES (<value>) • Read • SELECT <fields> FROM <table(s)> WHERE <condition> • Update • UPDATE <table> SET <field> = <value> WHERE <condition> • Delete • DELETE FROM <table> WHERE <condition>

13. New Material: PHP & MySQL It would be really nice if we could use our database information in PHP... Essentially, we want to let people access the data and actually do something with this information. Share photos, recommend movies, send messages, play games

15. Where does this interaction happen?

16. Where does this interaction happen? MySQL query

17. Connecting to a Database We have to let PHP know that we’re connecting to a MySQL database. • We’re connecting to a MySQL server on our local machine in the above example. We need to supply both a username and password to access the database. • Then, because a MySQL server can have multiple databases, we tell our current MySQL connection what database want to use.

18. Communicating with the Database We’ve already connected to our server, and now we want to communicate with it. • How do we communicate with the database? • SQL Queries: CRUD • How do we do this in PHP? • mysql_query()

19. Sending Queries How do we find: usernames of people who are at least 23 years old? SQL: SELECT username FROM users WHERE age >= 23 Using mysql_query:$query = mysql_query(“SELECT username FROM users WHERE age>= 23”, $db);

20. Getting the Results How do we retrieve our data from the query? mysql_fetch_assoc() Usage: $return = mysql_fetch_assoc($query); This gives you the results from the database one by one.

21. Getting the Results (2) They are returned as an associative array, keyed by your table column names. • We retrieve our data like this: $return[username] • echo “$return[username]”; will print “user2”

22. Getting the Results (3) What if we have more than one record that is retrieved by the query?

23. Getting the Results (3) • “Keep giving me more records that satisfy my query until there are no more.”

24. Displaying the Results • We want our results to look more structured and nice: so let’s put it in a table…

25. Displaying the Results

26. Displaying the Results

27. Add some CSS

28. Adding Records • What if we want to add records into our database? • We know SQL: INSERT INTO <table> (<field names>) VALUES (<values>)

29. Adding Records (2) • Adding dynamically: we want a form so you can create your own user by entering your username and age. • You can already do this with what you know about PHP!

30. Ordering • mysql_fetch_assoc() gives you results in an order that you might not want – sometimes you want your results in an order alphabetically, by time, etc. • MySQL operator: “ORDER BY <fieldname> <direction>” • Direction: ASC or DESC (ascending/descending) • Example query:SELECT * FROM users WHERE age >= 23 ORDER BY username DESC

31. State Persistence (cookies) • If you want to remember who a user is across page loads, you need to keep some sort of state on their end. • Cookies solves this problem – you can remember who people are. • We achieve this in PHP by using setcookie() • setcookie(“username”, “allen”) • Next time the website is visited, they can check to see if $_COOKIE[‘username’] is set. Then, if it is, it looks up records for “allen” and displays information accordingly.

32. Concerns • A lot can go wrong when you work with databases… you have to worry about: • Security • “SQL Injection” attacks • Encryption (md5) • Validity • All fields filled in, expecting numerical values • Scalability • If a lot of people are visiting your website at once, your database is the most likely thing to cause your website to crash