DIG 4530c Lecture 8:

1
Media E-Commerce I
DIG 4530c - Lecture 8 MySQL meets PHP Michael
Moshell University of Central Florida
2
Querying mySQL from pHP
lt!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
"http//www.w3.org/TR/html401/loose.dtd"gt
lthtmlgt ltheadgt ltmeta http-equiv"Content-Type"
content"text/html charsetiso-8859-1"gt
lttitlegtWines - Example 6.1 from
Platypuslt/titlegt lt/headgt ltbodygt lth1gt Wines -
Example 6.1 from Platypuslt/h1gt ltpregt lt?php //
(1) Open the database connection connection
mysql_connect("localhost","root","")  
// (2) Select the winestore database
mysql_select_db("winestore", connection)  
// (3) Run the query on the winestore through the
connection result mysql_query ("SELECT
FROM winery", connection)
Step 1 Open connection
3
Querying mySQL from pHP
lt!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
"http//www.w3.org/TR/html401/loose.dtd"gt
lthtmlgt ltheadgt ltmeta http-equiv"Content-Type"
content"text/html charsetiso-8859-1"gt
lttitlegtWines - Example 6.1 from
Platypuslt/titlegt lt/headgt ltbodygt lth1gt Wines -
Example 6.1 from Platypuslt/h1gt ltpregt lt?php //
(1) Open the database connection connection
mysql_connect("localhost","root","")   // (2)
Select the winestore database
mysql_select_db("winestore", connection)  
// (3) Run the query on the winestore through the
connection result mysql_query ("SELECT
FROM winery", connection)
Step 1 Open connection
Step 2 select our DB
4
Querying mySQL from pHP
lt!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
"http//www.w3.org/TR/html401/loose.dtd"gt
lthtmlgt ltheadgt ltmeta http-equiv"Content-Type"
content"text/html charsetiso-8859-1"gt
lttitlegtWines - Example 6.1 from
Platypuslt/titlegt lt/headgt ltbodygt lth1gt Wines -
Example 6.1 from Platypuslt/h1gt ltpregt lt?php //
(1) Open the database connection connection
mysql_connect("localhost","root","")   // (2)
Select the winestore database
mysql_select_db("winestore", connection)  
// (3) Run the query on the winestore through the
connection result mysql_query
("SELECT FROM winery", connection)
Step 1 Open connection
Step 2 select our DB
Step 3 Run a query
5
Querying mySQL from pHP
result mysql_query ("SELECT FROM
winery", connection)   // (4) While there
are still rows in the result set, fetch the
current // row into the array row while
(row mysql_fetch_array(result,
MYSQL_NUM)) // (5) Print out each
element in row, that is, print the values of
// the attributes foreach (row as
attribute) print
"".attribute. ""
Step 4 Milk the cow
Step 5 Look at results
6
Querying mySQL from pHP
Step 5 Wrapping up
// Print a carriage return to neaten the
output print "\n" ?gt lt/pregt lt/bodygt lt/
htmlgt
The \n causes new lines, since our tags are
ltpregt lt/pregt.
7
And the output is ....
Wines - Example 6.1 from Platypus
1Moss BrothersSmith Rd3 2Hardy
BrothersJones St.1 3PenfoldsArthurton
Rd.1 4LindemannsSmith Ave.2 5Orlando
Jones St.1 .. because the system is just
dumping exactly what comes back from the 'foreach
(row as attribute)' command..
8
The array 'row' looks like this ..
But our test program didn't tell us what the
index values are. Let's go find out. I modify the
program, like this
foreach (row as index gt attribute) print
"index".index."--- attribute".attribute print
"\n"
9
The array 'row' looks like this ..
And so we see that the array was numerically
indexed.
10
Look at mysql2 A Query about Penfolds Winery.
The key new stuff is this
seekthis"Penfolds" myquery"SELECT FROM
winery WHERE wineryName ' ".seekthis." '
" Be veeeery careful with the quote marks! The
query that is placed into myquery will look
like this SELECT FROM winery WHERE
wineryName 'Penfolds'
11
Getting at the results
Here's the code to look at the data
print "The ".row1." winery has ID number
".row0)."ltbrgt"
Which tells us something about how the row
array is indexed with this kind of query. Let's
look at the results.
The Penfolds winery has ID number 3
12
About the database user/password
At the top of the program
connection mysql_connect("localhost","root",
"")
Let's look in phpMyAdmin to see what this is
about. Look in 'Privileges' on the main front
page. See this table
13
About the database user/password
Let's try adding a user
connection mysql_connect("localhost","herman
","hermit")
Use the interactive panel of phpMyAdmin to add
herman/hermit. Now the table changes to look
like this
14
Some Hints about Lab 2
For the fast swimmers
General plan of attack, Lab 2 version 1 - Read
the cumulative table from database into array -
Add the scores from the most recently captured
form - Display the cumulative results - Write the
results back out to the database. Lab 2 -
version 2 - Provide separate functionalities for
data capture - and data display. (Most users
don't need to see the cumulative results --
especially before voting!)
15
Some Hints about Lab 2
For the entire class
Today we begin with a simple password
example. Then we will generalize it to solve the
Project 2 problem.
16
Some Hints about Project 2
For the entire class
Next week I will provide a winestore 'cumulative
example' that can be cloned and twisted and
perverted to produce your Project 2 -- if you
need it.