Media Software Design
Download
1 / 14

- PowerPoint PPT Presentation


  • 172 Views
  • Uploaded on

Media Software Design. DIG 3134 – Lecture 14 MySQL and PHP Play Together Michael Moshell University of Central Florida. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '' - eris


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

Media Software Design

DIG 3134 – Lecture 14

MySQL and PHP Play Together

Michael Moshell

University of Central Florida


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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 KEY

or PRIMARY INDEX, the system builds a

special resource ("index") to speed up search

to order-log-N. (i. e. 1000 records -> 10 steps)

2000 records -> 11 steps)


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

Two Concepts b4 We Begin

2. A QUERY with USER INPUT is Very Dangerous

because of SQL INJECTION ATTACK

A hostile user can type some junk that makes

your query into TWO queries, the second

of 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)


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

Our objective today:

Get Familiar with php

and database code

I give you 'address.php'

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

1) We analyze and discuss its code

2) You use phpMyAdmin to build the table

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

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

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

functionality

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


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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 add

a record, its autoincrement number will be 6.


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

New Features in 'address.php'

Elite Note:

If you want to force a value into the auto-increment

system (for the next INSERT), use this query:

ALTER TABLE addressbook AUTO_INCREMENT = 3

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


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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)


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

Make vanilla 'address.php' work

Download 'address.txt' from the DIG3134 website,

save it as 'address.php' in your WAMP or MAMP

system's docroot

(WAMP calls that place 'www')

(MAMP calls that place 'htdocs')

Modify its login info so that it works with your

'addressbook' table.


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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.


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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 />


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

Modify 'address2.php':

step by step

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

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


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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!


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

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


Dig 3134 lecture 14 mysql and php play together michael moshell university of central florida

In the next lecture

we 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 to

the 'addperson' function but with a different

query.