Transcript PHP meets MySQL - La Salle University

PHP meets MySQL
1
Address Book Example
• Let us create a rather simple, one table
database containing names, addresses,
phone numbers, etc.
• Then we will create a PHP site that
interfaces with this database.
– The site will have two pages, the first will have
a list of people in the database.
– The second will be shown after the user
selects a person from the list and will display
more information about the selected person.
2
In the test database, use the Create new table area to
make a new table. Give it a name. We are all using the
same database, so incorporate your name into the table
name to prevent conflicts. Enter the number of fields. Mine
will have 8 (id, first name, last name, email, office phone,
home phone, department, office location).
3
Possible design of an address book
4
The ID Field
• Most database tables should have an primary
key – a field (or fields) that can uniquely identify
each record in the table.
• In this case we have chosen the PersonID to
serve as the table’s ID.
• We checked the radio button (first in the row) to
indicate that it is a primary key.
• We chose the type to be CHAR which means
that it can include letters (unlike a number) and
must be of fixed length (unlike a varchar).
5
Unique
• The third column of radio buttons is labeled
Unique.
• If a field is marked as unique, that means no two
records in the table can have the same value for
that field.
– Such a field is a candidate key – that is, it could be
used as the primary key but some other choice was
made.
– In Banner a person can be identifies by a PIDM, a La
Salle ID and a Social Security Number. One will
serve as the primary key and the other should be
marked as unique.
– There are no unique fields in our address book
example (apart from the primary key)
6
Index
• The second column is marked Index.
• The usual meaning of Index is that you might
want to sort on this field.
– If you indicate that you might want to sort on a field,
the DBMS can do some of the work on this task
ahead of time, so that it goes faster when requested.
– (I’m not sure why unique and index are radio buttons
– something may be unique and a desirable field for
sorting. ???)
– I selected index on the LastName field.
7
Field Lengths
• Try to choose field lengths that are long
enough to accommodate the data you
anticipate being entered, but that would
rule out bad data being entered.
– A lot of the decisions that go into database
design are about data integrity – making sure
that the data that gets in there is at least of
the correct format.
8
Phone Numbers?
• Certain questions arise concerning phone
numbers.
– Are they really numbers? Does one add or
subtract them?
– If they are stored as text (CHAR or
VARCHAR), should one store the formatting.
• Does one store 2155551234?
• Or does one store (215) 555-1234?
9
NULL or NOT NULL
• One more decision regarding the fields in the
table is whether or not a NULL value is allowed
for the field.
– For the field designated as a primary key, the answer
is a resounding NO.
– Since we are interested in integrity, forcing someone
to enter data because the field is NOT NULL might
make it more likely that they enter bad data.
– It is not always just a question of whether or not a
field should have a value but a practical one of
whether the data-entry person will know it. Is it
meaningful to have the record with this field NULL?
10
Click on Save
11
In the table below, I allowed a lot of fields to be NULL, I didn’t want to
eliminate a record because any one piece of information is missing.
If you are unhappy with your design decisions, you can click on the pencil
icon for a field which will give you the option to edit the design.
12
Should you decide to collect more data
about a person, you can add fields.
13
To start entering data, click on the Insert
button.
14
Enter the values for your record in the Value
column
Make sure to put data in the Value
column, the function column can
throw one off at first.
Also a little weird is this second
record that gets “ignored.”
15
Click Go to enter the record, make your radio button choice
depending on whether you have more data to enter or not.
16
As with any database action, it can be achieved using a
SQL statement, which the interface shows you.
This can be useful later on if you
are designing an interface for
data entry.
Again it even provides the PHP.
17
View records, click on the table button on
left, and then on Browse button at top.
18
Start two pages, one for the user to choose a
name and the second to handle the request.
Look ma, I’m trying to be
XHTML compliant.
19
Set some Page Properties, the Title, and
then insert a form on the first page.
20
Choose an action and a method for the
form. We’ll try GET this time.
The form will have a drop-down list of
names. The values will be the IDS.
Thus the use of GET may be a security
issue. If you think that your Ids should
be kept more private, use POST.
21
Add a menu/list to the page. Give it a name.
22
To center the menu, go to code, place <div> tags around
the select tags and set the div’s align attribute to “center”
23
Let us add one option to remind ourselves
what the HTML for an option looks like.
24
Turn that into a comment, we want our
options to come from the database.
25
Start a PHP code block with the select tags.
26
Add a statement to aid in debugging.
27
Assign some database related variables
28
Use the function for connecting to MySQL.
die(): is a function that will allow you (possibly) to display a
message and then get out of the script.
With some errors one might desire the script to continue, with
others one just wants to get out of the script. The die() function is
used in the latter case.
29
Use the function for selecting the
database
In the database select function, the database connection
variable is an optional parameter. (You might need it if you
were connecting to multiple databases.)
30
Assign to the $sql variable a string containing the SQL
query and then use the function to execute the query.
Remember the pma interface would provide you with the
php for various queries that were performed on the
database through the interface. So the assignment
statement could be a copy/paste in some circumstances.
31
Loop through the query results and print out
the result into HTML option tags.
Note that the harder (less familiar) part here is the while loop with
its row array and mysql_fetch_array function. However, it occurs
often and is usually the same thing each time.
32
Result of records coming from database and being
placed into options of a drop-down list (menu)
33
Insert a button below the drop-down list
34
Give it a name, a label and an action.
35
In the handler file, set up page
properties, insert a header
36
Result of header tag insert.
37
Start a PHP block with the <h2> tags
38
Copy the database related code from
the previous file
39
Edit the query to select data corresponding
to the person chosen
40
Return to AddressBook, add a
target to the form
41
Handler does not replace origin
42