1

DIG 3134 – Lecture 14

MySQL and PHP Play Together

Michael MoshellUniversity of Central Florida

Media Software Design

2

Two Concepts b4 We Begin

1. An INDEX is a way of speeding up a database

If you just make a DB table with 4 "plain vanilla" fields,searches are order-N(that means as search examines EVERY record.)

If you specify a field (or combination of fields) as KEYor PRIMARY INDEX, the system builds a special resource ("index") to speed up searchto order-log-N. (i. e. 1000 records -> 10 steps)

2000 records -> 11 steps)

3

Two Concepts b4 We Begin

2. A QUERY with USER INPUT is Very Dangerousbecause of SQL INJECTION ATTACK

A hostile user can type some junk that makesyour query into TWO queries, the secondof which destroys everything ... or reveals it

Solution: Run all user input through a filter like this:

$matchlastX=$_POST['matchlast'];$matchlast=$mysqli->real_escape_string($matchlastX)

4

Our objective today:Get Familiar with phpand database code

I give you 'address.php'

which is a super-simple (add-only) Address Book

1) We analyze and discuss its code2) You use phpMyAdmin to build the table3) You extend the code by adding 'city' and 'state' fields.4) You extend the code: add a title row to the HTML table5) You extend the code by adding a 'delete record'

functionality6) For the Elite Premium-Plan People: radio button version

5

New Features in 'address.php'

1. An Auto-Increment Field in the Table 'addressbook'

2. An INSERT query in the function 'addperson()'

Autoincrement: whenever a new record is INSERTed,this field takes a value that is larger (by 1) than its value in the previous new record.

So it grows like 1, 2, 3, 4, 5.

If you delete records (so list is now 1,2,4) and then adda record, its autoincrement number will be 6.

6

New Features in 'address.php'

Elite Note:

If you want to force a value into the auto-incrementsystem (for the next INSERT), use this query:

ALTER TABLE addressbook AUTO_INCREMENT = 3

Of course, this would set the next value to 3.

7

Use phpMyAdmin to build your table 'addressbook'

Four columns:idnumber – int – index='primary' and check 'A_I'

(which means auto-increment)

lastname – varchar (30)

firstname – varchar (30)

address – varchar (30)

8

Make vanilla 'address.php' work

Download 'address.txt' from the DIG3134 website,save it as 'address.php' in your WAMP or MAMPsystem's docroot

(WAMP calls that place 'www')(MAMP calls that place 'htdocs')

Modify its login info so that it works with your 'addressbook' table.

9

Save as 'address2.php'

3) Extend the code by adding 'city' and 'state' fields.

How to do this? Give it a try, first.

If you get stuck, the next pages are a step-by-step guide.

10

Modify 'address2.php': step by step

3) Extend the code by adding 'city' and 'state' fields.

3a) Add two new input fields to 'drawinput'

<input type='text' name='city'>City<br /><input type='text' name='state'>State<br />

11

Modify 'address2.php': step by step

3) Extend the code by adding 'city' and 'state' fields.

3b) Add two new fields to table 'addressbook'

12

Modify 'address2.php': step by step

3) Extend the code by adding 'city' and 'state' fields.

3c) Add two new fields to function 'addperson'

$cityX=$_POST['city'];$stateX=$_POST['state'];-- use $mysqli->real_escape_string to clean these! --

$query = "INSERT into addressbook VALUES (null,'$lastname','$firstname',$address,$city,$state)";

And now it ought to work!

13

Now you're on your own(but you can HELP one another!)

4) You extend the code: add a title row to the HTML table

14

In the next lecturewe will ...

5) extend the code by adding a 'delete record' functionality

Add an input field for a 'deletenumber' value

Add a submit button for action "Delete Person"

Construct a function named 'deleteperson', similar tothe 'addperson' function but with a differentquery.