Practical PHP and mysql walkthrough

1. Practical PHP and mysql walkthrough Using sample codes – Max Ng

2. Why php and mysql? • Easy to learn and install • Free to use and readily for download • LAMP is a solution stack of free, open source software. • Rich resources in WWW • Well supported in PolyU COMP lab

3. Php in polyu comp lab • PHP (Apache) • PHP files are to be placed in :/webhome/<your id>/public_html • Solaris Cluster Machine can be accessed thr PC using • WINSCP (file transfer) • PuTTY (Line command session) • The website can be accessed using Web browser to the url link : • http:/www.comp.polyu.edu.hk/~<your id>/index.php

4. mysqlin polyu comp lab • MySQL • A MySQL account is readily activated for your perusal. • Account Details: • Host: mysql.comp.polyu.edu.hk:3306 • User: <your id> • Password: <assigned, please check your email> • Schema: <your id> • To directly manipulate your schemathr’ PC: • In Lab PC, there is a GUI Tool: • "COMP" ->"Network Application Packages" ->"Database" ->"MySQL" ->"MySQLQBrowser“

5. We will walkthrough php and mysql together • Purpose oriented • Start from Database table preparation • To website implementation • Using a simple website to study some basic coding technique • My Book Store • Using a advanced website to walkthrough a e-commerce system • My Web Shop

6. At the end of session, we should be able to use the provided code to build a simple web site. • MySQL table setup • Create table • Insert record • Write PHP page • Connect to MySQL • Take the http-get parameters • Handle session • Looping control • Generate dynamic web page thr select statement

7. basic web page design of “My book store” summary_order.php • Request user login • Show product list and selection • Purchase list summary index.php books.php

8. We have to create a schema at first in mysql. • Create the schema to house our new table. • CREATE DATABASE <dbname>; • Note: • To remove the schema, we issue DROP DATABASE <dbname>; • In COMP lab, we use only our login ID as our default schema. • Change our session to another schema by issuing USE <dbname>;

9. We put the booklist table into the active schema. • Create table by issuing • CREATE TABLE [IF NOT EXISTS] • <tableName> ( <columnName> <columnType> <columnAttribute>, ... PRIMARY KEY(<columnName>), FOREIGN KEY (<columnName>) REFERENCES <tableName> (<columnName>) ) ; • E.g. • CREATE TABLE book_records_store( • Book_No BIGINT NOT NULL AUTO_INCREMENT, • Book VARCHAR(150) NOT NULL, • Price DECIMAL(5,2) NOT NULL, • PRIMARY KEY(Book_No))ENGINE=MyISAM AUTO_INCREMENT=1;

10. We insert records into the booklist table. • Insert record by issuing • INSERT INTO <tableName> • VALUES (<column1Value>, <column2Value>,...), ... • e.g. • INSERT INTO book_records_store VALUES • (null,'C++ for Starters','125.50'), • (null,'Java AWT Package','178.50');

11. Finally, We inspect the table content. • Review the schemas we have  SHOW DATABASES; • Set active schema by issuing USE <dbname>; • Review all table names in a schema  SHOW TABLES; • Browse the table content  • SELECT * | <column1Name>AS <alias1>, ..., <columnNName>AS <aliasN>FROM <tableName>WHERE <criteria>GROUP BY <columnName>ORDER BY <columnName>ASC|DESC, ... HAVING <groupConstraints>LIMIT <count>| <offset> <count>; • e.g. • SELECT * FROM book_records_store;

12. basic web page design of “My book store” summary_order.php • Request user login • Show product list and selection • Purchase list summary index.php books.php

13. The simplest form of php page is actually a page of html tag. • Index.php

14. We have to pass input values from index.php to books.php using http-get (2) (1) • <form method="GET" action="books.php"> • … • <input type="text" name="fname"/></td> • <input type="text" name="lname"/></td> • <input type="submit" name="Loging" value="Login"/></td> • … • </form> (1) (2) books.php (3) (1) (2) index.php http://.../books.php?fname=Max&lname=Ng&Loging=Login (1) (2) (3)

15. basic web page design of “My book store” summary_order.php • Request user login • Show product list and selection • Purchase list summary index.php books.php

16. We show the list of books in our booklist table in the books.php

17. PHP code is embedded into html page to generate the booklist table. • First of all, we connect to the database we just created and build our database query. • includeis to take the code from another php file and evaluate. databaseconfig.php

18. Next, create session to let values to be Available throughout session [2] [1] [1] isset() : check null [3] [4] [2] $_GET: Return the http-get variable. Do you remember what is this variable? [5] [3] session_start(): start a session [4] $_REQUEST: Return the variable either in the array variables in http-get, http-post or cookies. [5] $_SESSION: refer to a session variable to be available throughout the session

19. Lastly, Extract the database query return and construct the booklist table [3] [1] [2] [1] mysql_fetch_array() : Fetch a result row as an array [2] echo : output http text [3] in calling order_summary.php, http-get will pass an array with name “order[]” and value which is the price of the book.

20. Only the checkbox = ‘Y’ will be http-get to the summary_order.php http://.../order_summary.php?order%5B%5D=125.50&order%5B%5D=186.75&order%5B%5D=189.00&Ord=Order

21. basic web page design of “My book store” summary_order.php • Request user login • Show product list and selection • Purchase list summary index.php books.php

22. Summary_order.php construct the order table and output as http page [1] [2] [?] [1] session_start() : Retrieve the saved session. Now the session variable “firstN” and “lastN” available. [2] count() : count the no. of element in an array [?] We will discuss in the coming lab session.

23. Summary_order.php construct the order table and output as http page (cont’d)

24. At the end of session, we should be able to use the provided code to build a simple web site. • MySQL table setup • Create table • Insert record • Write PHP page • Connect to MySQL • Take the http-get parameters • Handle session • Looping control • Generate dynamic web page thr select statement

25. Functional Walkthrough: a ecommerce system using php and mysql

26. A comprehensive ecommerce site should be able to: • Handle customer request in timely fashion • Provide sufficient information for customer to make purchase decision • Smoothen the process from product selection to payment • Maintain the product list and stock control • Maintain the order information • Maintain the customer information • Perform web advertising and site analytics

27. Product list integrated with shopping cart

28. Shopping cart provided with direct checkout or back to shopping

29. Check out, payment and email notification all-do in one-take

30. Administration: catalog, order management and minor screen layout

31. Catalog management on product, featured product list and stock management

32. Trace customer new Order and further backend processing

33. Comprehensive site configuration on various settings

34. Thanks!!