Intermediate PHP & MySQL

1. Intermediate PHP & MySQL

2. Welcome This slideshow presentation is designed to introduce you to some intermediate PHP concepts and an introduction to MySQL.It is the second of two PHP workshops available at www.tinyurl.com/rpi123. In addition to the two PHP workshops, there are also workshops on HTML and CSS. These slides are based on source material found at the w3schools.com website. You are encouraged to visit the site – it is a great resource.

3. Let's dive right in, shall we?

4. The PHP date() function formats a timestamp to a more readable date and time. PHP Date() Function

5. The required format parameter in the date() function specifies how to format the date/time. d - Represents the day of the month (01 to 31)‏ m - Represents a month (01 to 12)‏ Y - Represents a year (in four digits)‏ Other characters, like"/", ".", or "-" can also be inserted between the letters to add additional formatting. PHP Date() Function

6. PHP Date() Function

7. The optional timestamp parameter in the date() function specifies a timestamp. If you do not specify a timestamp, the current date and time will be used. The mktime() function returns the Unix timestamp for a date. The Unix timestamp contains the number of seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified. PHP Date() Function

8. PHP Date() Function

9. You can insert the content of one PHP file into another PHP file before the server executes it, with the include() or require() function. > include() generates a warning, but the script will continue execution > require() generates a fatal error, and the script will stop PHP Server Side Includes (SSI)‏

10. These two functions are used to create functions, headers, footers, or elements that will be reused on multiple pages. SSI saves a lot of work. This means that you can create a standard header, footer, or menu file for all your web pages. When the header needs to be updated, you update the include file, or when you add a new page to your site, you can simply change the menu file (instead of updating the links on all your web pages). PHP Server Side Includes (SSI)‏

11. The include() function takes all the content in a specified file and includes it in the current file. If an error occurs, the include() function generates a warning, but the script will continue execution. Here's what the error output might look like... PHP include() Function

12. Warning: include() [function.include]: URL file-access is disabled in the server configuration in /home/user/public_html/page.php on line xx Warning: include(http://www.somedomain.com/file.php) [function.include]: failed to open stream: no suitable wrapper could be found in /home/user/public_html/page.php on line xx Warning: include() [function.include]: Failed opening 'http://www.somedomain.com/file.php' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/user/public_html/page.php on line xx PHP include() Function error

13. Assume that you have a standard header file, called "header.php". To include the header file in a page, use the include() function: PHP include() Function

14. Assume we have a standard menu file, called "menu.php", that should be used on all pages: And this is how you'd include it... PHP include() Function

15. PHP: include() Function Here's what the output HTML would look like...

16. PHP: include() Function

17. The require() function is identical to include(), except that it handles errors differently. If an error occurs, the include() function generates a warning, but the script will continue execution. The require() generates a fatal error, and the script will stop. PHP require() Function

18. PHP require() Function So, if we use include()...

19. PHP require() Function The errors show, but the code still gets executed. See how it echoed “Hello World”.

20. PHP require() Function But, if we use require() instead...

21. PHP require() Function We get only the error message – and the code doesn't get executed.

22. A cookie is often used to identify a user. A cookie is a small file that the server embeds on the user's computer. Each time the same computerrequests a page with a browser,it will send the cookie, too. WithPHP, you can both createand retrieve cookie values. PHP Cookies

23. The setcookie() function is used to set a cookie. Note: The setcookie() function must appear BEFORE the <html> tag. PHP setcookie() Function

24. In the example below, we will create a cookie named "user" and assign the value "Alex Porter" to it. We also specify that the cookie should expire after one hour: PHP setcookie() Function

25. You can also set the expiration time of the cookie in another way. It may be easier than using seconds. PHP setcookie() Function

26. The PHP $_COOKIE variable is used to retrieve a cookie value. In the example below, we retrieve the value of the cookie named "user" and display it on a page: PHP $_COOKIE

27. PHP $_COOKIE If the cookie is set, receive a personal welcome – if not, it echoes a generic greeting.

28. And now for something Completely different...

29. (Instant “A” for those who got the Monty Python reference)‏ A brief but fascinating introduction to MySQL

30. The new MySQL logo - a jumping dolphin - symbolizes the speed, power, precision and good nature of the MySQL database and community. Michael "Monty" Widenius (creator of MySQL):"I am personally concerned about the survival of endangered species, and I liked the idea of the dolphin as soon as it came up. It combines great symbol value with a powerful, modern design." FYI The Logo

31. The My in MySQL is not like the i in iPad. It is actually the name of Monty's daughter.Yes, her name is My. The SQL part stands for Structured Query Language. FYI The Name

32. MySQL is a database. The data in MySQL is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. MySQL Introduction

33. A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data. Below is an example of a table called "Persons": MySQL Tables

34. A query is a question or a request. With MySQL, we can query a database for specific information and have a recordset returned. Look at the following query: MySQL Queries

35. The query above selects all the data in the "LastName" column from the "Persons" table, and will return a recordset like this: MySQL Queries

36. Before you can access data in a database, you must create a connection to the database. In PHP, this is done with the mysql_connect() function. MySQL mysql_connect()‏

37. In the following example we store the connection in a variable ($con) for later use in the script. The "die" part will be executed if the connection fails: MySQL mysql_connect()‏

38. The connection will be closed automatically when the script ends. To close the connection before, use the mysql_close() function: MySQL mysql_close()‏

39. The CREATE DATABASE statement is used to create a database in MySQL. To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection. MySQL Create a Database

40. MySQL Create a Database

41. The CREATE TABLE statement is used to create a table in MySQL. We must add the CREATE TABLE statement to the mysql_query() function to execute the command. MySQL Create a Table

42. MySQL Create a Table This is the top part of the file.The CREATE TABLE code is on the next slide...

43. MySQL Create a Table

44. Important: A database must be selected before a table can be created. The database is selected with the mysql_select_db() function. Note: When you create a database field of type varchar, you must specify the maximum length of the field, e.g. varchar(15). MySQL Create a Table

45. About Primary Keys and Auto Increment Fields: Each table should have a primary key field. A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record. MySQL Create a Table

46. The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field. MySQL Create a Table

47. MySQL Create a Table

48. The INSERT INTO statement is used to add new records to a database table. Syntax It is possible to write the INSERT INTO statement in two forms. MySQL Inserting Data

49. The first form doesn't specify the column names where the data will be inserted, only their values: The second form specifies both the column names and the values to be inserted: MySQL Inserting Data

50. To get PHP to execute the statements above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection. Previously we created a table named "Persons", with three columns; "Firstname", "Lastname" and "Age". We will use the same table in this example. The following example adds two new records to the "Persons" table: MySQL Inserting Data