![Page 1: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/1.jpg)
1
PHP meets MySQL
![Page 2: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/2.jpg)
2
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.
![Page 3: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/3.jpg)
3
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).
![Page 4: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/4.jpg)
4
Possible design of an address book
![Page 5: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/5.jpg)
5
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).
![Page 6: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/6.jpg)
6
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)
![Page 7: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/7.jpg)
7
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.
![Page 8: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/8.jpg)
8
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.
![Page 9: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/9.jpg)
9
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?
![Page 10: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/10.jpg)
10
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?
![Page 11: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/11.jpg)
11
Click on Save
![Page 12: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/12.jpg)
12
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.
![Page 13: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/13.jpg)
13
Should you decide to collect more data about a person, you can add fields.
![Page 14: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/14.jpg)
14
To start entering data, click on the Insert
button.
![Page 15: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/15.jpg)
15
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.”
![Page 16: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/16.jpg)
16
Click Go to enter the record, make your radio button choice
depending on whether you have more data to enter or not.
![Page 17: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/17.jpg)
17
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.
![Page 18: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/18.jpg)
18
View records, click on the table button on
left, and then on Browse button at top.
![Page 19: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/19.jpg)
19
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.
![Page 20: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/20.jpg)
20
Set some Page Properties, the Title, and
then insert a form on the first page.
![Page 21: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/21.jpg)
21
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.
![Page 22: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/22.jpg)
22
Add a menu/list to the page. Give it a name.
![Page 23: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/23.jpg)
23
To center the menu, go to code, place <div> tags around the select tags and set the div’s align attribute to “center”
![Page 24: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/24.jpg)
24
Let us add one option to remind ourselves what the HTML for an option looks like.
![Page 25: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/25.jpg)
25
Turn that into a comment, we want our options to come from the database.
![Page 26: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/26.jpg)
26
Start a PHP code block with the select tags.
![Page 27: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/27.jpg)
27
Add a statement to aid in debugging.
![Page 28: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/28.jpg)
28
Assign some database related variables
![Page 29: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/29.jpg)
29
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.
![Page 30: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/30.jpg)
30
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.)
![Page 31: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/31.jpg)
31
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.
![Page 32: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/32.jpg)
32
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.
![Page 33: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/33.jpg)
33
Result of records coming from database and being
placed into options of a drop-down list (menu)
![Page 34: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/34.jpg)
34
Insert a button below the drop-down list
![Page 35: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/35.jpg)
35
Give it a name, a label and an action.
![Page 36: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/36.jpg)
36
In the handler file, set up page properties, insert a header
![Page 37: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/37.jpg)
37
Result of header tag insert.
![Page 38: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/38.jpg)
38
Start a PHP block with the <h2> tags
![Page 39: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/39.jpg)
39
Copy the database related code from the previous file
![Page 40: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/40.jpg)
40
Edit the query to select data corresponding to the person chosen
![Page 41: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/41.jpg)
41
Return to AddressBook, add a target to the form
![Page 42: 1 PHP meets MySQL. 2 Address Book Example Let us create a rather simple, one table database containing names, addresses, phone numbers, etc. Then we will](https://reader035.vdocuments.us/reader035/viewer/2022062718/56649e885503460f94b8d3e3/html5/thumbnails/42.jpg)
42
Handler does not replace origin