building an online bidding application using php/mysql
DESCRIPTION
Building an online bidding application using PHP/MySQL. Widhy Hayuhardhika NP, S.Kom. Outline Topic # 1 MySQL Connection. MySQL Database Connection. Overview of database structure Connecting to MySQL database Selecting the database to use Using the require_once statement. - PowerPoint PPT PresentationTRANSCRIPT
Widhy Hayuhardhika NP, S.Kom
Building an online bidding application using
PHP/MySQL
Outline Topic #1 MySQL Connection
Overview of database structureConnecting to MySQL databaseSelecting the database to useUsing the require_once statement
MySQL Database Connection
Database: auctionTables
tblaccounttblbiditemstblbidhistory
Overview of Database connection
This will hold the account info of bidders/ auctioneers
Table structureColumn accountid: integer, primary key, auto-
incrementColumn username: string 50 charsColumn password: string 50 chars
Table tblaccount
This will hold the items auctioned for biddingTable structure
Column biditemid: integer , primary key, auto-increment
Column accountid: string 50 charsThis identifies the auctioneer
Column biditem: string 50 charsColumn biddesc: tiny text
Table tblbiditems
This will hold the bid info for each item being auctioned
Table structureColumn bidhistoryid: integer , primary key,
auto-incrementColumn accountid: integerColumn biditemid: integerColumn bidprice: doubleColumn dtesubmitted: datetime
Table tblbidhistory
Function mysql_connect:Creates a connection to MySQLSyntax: mysql_connect($hostname, $username,
$password)Ex: $conn=mysql_connect(“localhost”,
“root”,”password”)Function mysql_select_db
Specifies the database in MySQL for useSyntax: mysql_select_db($database,
$connection)Ex: mysql_select_db(“auction”, $conn)
Function dieTerminates execution of PHP script
Connecting to databases:
Create file dbconnect.incFor code reuse, a separate file can be created
to connect to the databasePHP pages can call dbconnect.inc to connect
yo the auction database
Connecting to MySQL and selecting auction database
Function require_once()Loads a file into a PHP script
Reusing the database connection
Outline Topic #2 Creation of Accounts
HTML form handlingMySQL commands
Function mysql_query()Function mysql_error()
Adding recordsSQL insert statement
Creation of accounts
Create:File index.htmlFile addaccount.htmlFile addaccountprocess.php
$_POST array
HTML form handling
First page that displaysProvide the user with the option to create
accounts
File index.html
Displays a form for accepting new account info
File addaccount.html
$_POST arraySpecial 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 statementSQL Insert
Adds a record on a database table
File addaccountprocess.php
File addaccountprocess.php script
Username: auctioneer1This account will place items for bidding
Usernames: bidder1, bidder2These account will bid for item auctioned off
Create accounts:
Outline Topic #3 Managing Logins
SQL select statementFunction mysql_num_rowsFunction isset()SessionURL rewriting
Querystring $_GET array
Create: File login.php File loginverify.php File checkstatus.inc File menu.php
Managing logins
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 tblaccountExample 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
Retrieves the number of rows from a result set
Can only be used for SQL select statements
Function mysql_num_rows
Checks if a variable existExample: isset($name)
This check if the variable $name exist
Function isset()
Special variables stored in web serversAllows passing of information between web
pagesCall the function session_start() at the start of
scripts that will use sessions
Sessions
QuerystringInformation can be passed on by appending
variable/value to the URL
$_GET arraySpecial array that holds all querystring values
URL Rewriting
File login.php code
File login.php browser shot
File loginverify.php code
File checkstatus.inc code
File menu.php
Outline Topic #4 Adding Items to Auction
File menu.phpCreate:
File addauctionitem.phpFile addauctionitemprocess.php
Adding items to auction
File menu.php
File addauctionitem.php code
File addauctionitem.php screen shot
File addauctionprocess.php
Outline Topic #5 Deleting Bid Items
Function mysql_fetch_array()Writing querystring URL to identify records
to deleteSQL delete statementCreate:
File listauctionitems.phpFile: deletebiditem.php
Deleting Bid Items
Fetches a row as an associative from a select query result set
Function mysql_fetch_array()
Sample mysql_fetch_array() code
Auction items belonging to current account will be selected
A loop will be created to go through each rowEach row will hyperlink to a PHP based page
for deletionTo identify the row, a querystring variable will
be appended to the URL
Writing querystring URLto identify records to delete
Writing querystring URLto identify records to delete- code
Example 1: delete from tblaccountDeletes all rows on table tblaccount
Example 2: delete from tblaccount where accountid=1Deletes only rows matching the condition
SQL delete statement
File menu.php
File listauctionitems.php
File deletebiditem.php
Outline Topic #6 Logging Out
Function session_destroy()Create:
File logout.php
Loggin out
Terminates all session variables stored in server memory
Function session_destroy()
File menu.php
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
Outline Topic #7 Viewing Bid Items
Establishing relations between tablesSQL natural join clauseCreate:
File listbiditems.php
Viewing bid items
Establishing relationsTable 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
Used with SQL select statementConnects rows between different tables via
their common column
SQL natural join clause
File menu.php
All items with their respective owners being auction are listed
Each item will hyperlink to a PHP page for accepting bidsAccepting bids will be covered in the next topic
sectionEach hyperlink will append a querystring
variable to identify it in the PHP page for accepting bids
File listbiditems.php
File listbiditems.php code
File listbiditems.php screen shot
Outline Topic #7 Accepting Bids
Using hidden fields to store ID numbersMySQL now() functionCreate:
File acceptbid.phpFile acceptbidprocess.php
Accepting bids
Not displayed to the browserUsed to pass constant values
Hidden fields
Place the id of the auction item in a hidden fieldFile acceptbid.php
File acceptbid.php screen shot
File acceptbid.php HTML generated code
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu formatdepending on whether the function is used in a
string or numeric contextThe value is expressed in the current time
zone.
MySQL now() function
File acceptbidprocess.php
Resulting records
Outline Topic #9 Listing Bids For Each Bid Item
MySQL date_format() functionRelating information from two or more tablesSQL order by clause
Listing bids for each bid item
Formats a string based on a specified formatThe 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
MySQL date_format() sample