用PHP和MySQL构建一个在线交易应用程序案例。

海绵宝宝发布于2018/06/05 00:00

注脚

1.Widhy Hayuhardhika NP, S.Kom Building an online bidding application using PHP/MySQL

2.Outline Topic # 1 MySQL Connection

3.Overview of database structure Connecting to MySQL database Selecting the database to use Using the require_once statement MySQL Database Connection

4.Database: auction Tables tblaccount tblbiditems tblbidhistory Overview of Database connection

5.This will hold the account info of bidders/ auctioneers Table structure Column accountid : integer, primary key, auto-increment Column username : string 50 chars Column password : string 50 chars Table tblaccount

6.This will hold the items auctioned for bidding Table structure Column biditemid : integer , primary key, auto-increment Column accountid : string 50 chars This identifies the auctioneer Column biditem : string 50 chars Column biddesc : tiny text Table tblbiditems

7.This will hold the bid info for each item being auctioned Table structure Column bidhistoryid : integer , primary key, auto-increment Column accountid : integer Column biditemid : integer Column bidprice : double Column dtesubmitted : datetime Table tblbidhistory

8.Function mysql_connect: Creates a connection to MySQL Syntax: mysql_connect($hostname, $username,$password) Ex: $conn=mysql_connect(“localhost”, “root”,”password”) Function mysql_select_db Specifies the database in MySQL for use Syntax: mysql_select_db($database, $connection) Ex: mysql_select_db(“auction”, $conn) Function die Terminates execution of PHP script Connecting to databases:

9.Create file dbconnect.inc For code reuse, a separate file can be created to connect to the database PHP pages can call dbconnect.inc to connect yo the auction database Connecting to MySQL and selecting auction database

10.Function require_once() Loads a file into a PHP script Reusing the database connection

11.Outline Topic # 2 Creation of Accounts

12.HTML form handling MySQL commands Function mysql_query() Function mysql_error() Adding records SQL insert statement Creation of accounts

13.Create: File index.html File addaccount.html File addaccountprocess.php $_POST array HTML form handling

14.First page that displays Provide the user with the option to create accounts File index.html

15.Displays a form for accepting new account info File addaccount.html

16.$_POST array Special arrays that hold all form variables Function mysql_query() Executes an SQL statement on the database Function mysql_error() Displays error encountered when executing an SQL statement SQL Insert Adds a record on a database table File addaccountprocess.php

17.File addaccountprocess.php script

18.Username: auctioneer1 This account will place items for bidding Usernames: bidder1, bidder2 These account will bid for item auctioned off Create accounts:

19.Outline Topic # 3 Managing Logins

20.SQL select statement Function mysql_num_rows Function isset() Session URL rewriting Querystring $_GET array Create: File login.php File loginverify.php File checkstatus.inc File menu.php Managing logins

21.Example 1: select * from tblaccount Selects all columns/ rows from table tblaccount Example 2: select username, password from tblaccount Selects columns username and password for all rows in table tblaccount Example 3: select * from tblaccount where username=‘jundolor’ Selects all columns from table tblaccount for all rows whose column username contains ‘jundolor’ Example 4: select accountid from tblaccount where username=‘media’ Selects column accountid from tblaccount for all rows whose column username contains ‘media’ SQL select statement

22.Retrieves the number of rows from a result set Can only be used for SQL select statements Function mysql_num_rows

23.Checks if a variable exist Example: isset($name) This check if the variable $name exist Function isset()

24.Special variables stored in web servers Allows passing of information between web pages Call the function session_start() at the start of scripts that will use sessions Sessions

25.Querystring Information can be passed on by appending variable/value to the URL $_GET array Special array that holds all querystring values URL Rewriting

26.File login.php code

27.File login.php browser shot

28.File loginverify.php code

29.File checkstatus.inc code

30.File menu.php

31.Outline Topic # 4 Adding Items to Auction

32.File menu.php Create: File addauctionitem.php File addauctionitemprocess.php Adding items to auction

33.File menu.php Create: File addauctionitem.php File addauctionitemprocess.php Adding items to auction

34.File addauctionitem.php code

35.File addauctionitem.php screen shot

36.File addauctionprocess.php

37.Outline Topic # 5 Deleting Bid Items

38.Function mysql_fetch_array() Writing querystring URL to identify records to delete SQL delete statement Create: File listauctionitems.php File: deletebiditem.php Deleting Bid Items

39.Fetches a row as an associative from a select query result set Function mysql_fetch_array()

40.Sample mysql_fetch_array() code

41.Auction items belonging to current account will be selected A loop will be created to go through each row Each row will hyperlink to a PHP based page for deletion To identify the row, a querystring variable will be appended to the URL Writing querystring URLto identify records to delete

42.Writing querystring URLto identify records to delete- code

43.Example 1: delete from tblaccount Deletes all rows on table tblaccount Example 2: delete from tblaccount where accountid=1 Deletes only rows matching the condition SQL delete statement

44.Example 1: delete from tblaccount Deletes all rows on table tblaccount Example 2: delete from tblaccount where accountid=1 Deletes only rows matching the condition SQL delete statement

45.File listauctionitems.php

46.File deletebiditem.php

47.Outline Topic # 6 Logging Out

48.Function session_destroy() Create: File logout.php Loggin out

49.Terminates all session variables stored in server memory Function session_destroy()

50.Terminates all session variables stored in server memory Function session_destroy()

51.Once logout.php is called, all session variable will be dropped from server memory Browser will not be able to access any page calling checkverify.php (ex: menu.php) File logout.php

52.Outline Topic # 7 Viewing Bid Items

53.Establishing relations between tables SQL natural join clause Create: File listbiditems.php Viewing bid items

54.Establishing relations Table tblbiditem Holds the items being auctioned off Column accountid identifies the owner if the auctioned item Table tblaccount Holds account information of the owner of the item being auctioned Column accountid Links the owner of the account to the auction item

55.Used with SQL select statement Connects rows between different tables via their common column SQL natural join clause

56.Used with SQL select statement Connects rows between different tables via their common column SQL natural join clause

57.All items with their respective owners being auction are listed Each item will hyperlink to a PHP page for accepting bids Accepting bids will be covered in the next topic section Each hyperlink will append a querystring variable to identify it in the PHP page for accepting bids File listbiditems.php

58.File listbiditems.php code

59.File listbiditems.php screen shot

60.Outline Topic # 7 Accepting Bids

61.Using hidden fields to store ID numbers MySQL now() function Create: File acceptbid.php File acceptbidprocess.php Accepting bids

62.Not displayed to the browser Used to pass constant values Hidden fields

63.Place the id of the auction item in a hidden field File acceptbid.php

64.File acceptbid.php screen shot

65.File acceptbid.php HTML generated code

66.Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format depending on whether the function is used in a string or numeric context The value is expressed in the current time zone. MySQL now() function

67.File acceptbidprocess.php

68.Resulting records

69.Outline Topic # 9 Listing Bids For Each Bid Item

70.MySQL date_format() function Relating information from two or more tables SQL order by clause Listing bids for each bid item

71.Formats a string based on a specified format The following are some of the specifies of the format string: %D: Day of month with English suffix %d: Numeric day of month (01…31) %M: Month name (January…December) %m: Month numeric (01…12) %Y: Year (4 digits) %y: Year (2 digits) MySQL date_format() function

72.MySQL date_format() sample

user picture
  • 海绵宝宝
  • Apparently, this user prefers to keep an air of mystery about them.

相关Slides