title postid userid

obtainablerabbiΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 2 μήνες)

123 εμφανίσεις


Using PHP with databases

2405/2005 Internet Programming

February 7, 2007

Terri Oda

PHP and databases


You often want to store the information
inputted into forms or generated from form
input


User information


Session ids for logged in users


Blog/forum posts, comments


Can keep info in cookies on the client side


Can keep it in files on the server


Mostly done with databases

Databases


Store… data!


Aren’t you shocked?


Why do we use them?


Allow random access of records


Handle locking


Can be indexed for fast access


Records of a given type are stored in tables


Can link together information from separate records


Robust query language for getting specific information

Name

Quest

Color

Other

Sir
Launcelot

Holy Grail

Blue

Sir Robin

Holy Grail

I don’t
know

Sir
Galahad

Holy Grail

Blue. No
Yel
--

auugh!

SQL


Structured Query Language


Pronouned “ess cue el” or “sequel”


Yes, people argue about this


Language used to create, retrieve, update,
delete from databases


SQL is standardized by ANSI and ISO


In theory, you use the same SQL on any db


Differing databases support different parts of the
standard


Each individual database also has its own
extensions to SQL

PostgreSQL


Free, Open Source database


MySQL is the other very popular one


Reasonably standards
-
compliant SQL
support


Not that it matters for this class because
I’m only showing simple stuff

A brief intro to SQL: Create table

CREATE TABLE guestbook (


name varchar NOT NULL,


email varchar,


website varchar,


message text,


time timestamp

);


Name, type of data, additional factors


Data types include various numbers, dates,
text, binary fields, auto
-
incrementing
counters, etc.

A brief intro to SQL: Select


SELECT * FROM table_name;


SELECT firstname, lastname FROM
table_name;


SELECT * FROM table_name WHERE
firstname=“Bob”;


SELECT * FROM table_name ORDER
BY lastname, firstname;


SELECT * FROM table_name LIMIT 5;

Joining tables together


SELECT username, title FROM users,
posts WHERE users.userid =
posts.userid;


userid

username

joined_date

3141592

Wash

Sept 1, 2002

userid

postid

title

3141592

8832923

Defining
interesting: oh
god oh god
we’re all going
to die?

10020344

I’m a leaf on
the wind

username

title

Wash

Defining interesting: oh god oh god we’re all going to die?


Wash

I’m a leaf on the wind


A brief intro to SQL: Insert


insert into guestbook (name, email) values (‘Chuck’,
’cnorris@scs.carleton.ca');


Makes a new row in the guestbook table, with



‘Chuck’ in the name column


‘cnorris@scs.carleton.ca’ in the email column


All other columns are left blank.

| name | email | website | message

+
------
+
-------------------------
+
---------
+
-----------

| Chuck|
cnorris@scs.carleton.ca

| |


PHP Data Objects


PDO: PHP Data Objects


An interface for accessing databases with
PHP


Idea is to have a generic and common
set of functions for accessing many
databases


If you need to change your database,
there will be only minimal code changes

Connecting to the DB

$dbh = new PDO('pgsql:host=localhost;

dbname=mydatabase;

user=terri;

password=notapassword');


Specify: type of database, database host,
name, the username, and the password

Making a Query in PHP


Given your database handle $dbh:

$result = $dbh
-
>query('SELECT * FROM
guestbook');


Getting information back out:


Database returns “rows” of information


$row = $result
-
>fetch();


(This is not the only way to get the info)

Getting Query Results in PHP


Result is an Array


[name] => Alice


[0] => Alice


[email] => alice@scs.carleton.ca


[1] => alice@scs.carleton.ca


[website] => http://www.scs.carleton.ca/~alice


[2] => http://www.scs.carleton.ca/~alice


Results given by column name and number


$row[‘name’] and $row[0] give the same string

Inserting rows



Basically the same process:

$result = $dbh
-
>query(“insert into guestbook
(name, email) values (‘Chuck’,
’cnorris@scs.carleton.ca')”);


If there are errors, $result will be false, and
detailed error info can be found in $dbh
-
>errorInfo()


This returns an array including an error number
and message

Putting it all together


Blog Demo:


Take input from form


Put it in the database


Display it

But what about security?


Still need to check that your input is good


Eg: integers are actually numbers


Text fields aren’t too long


Still need to check for HTML, etc.


Assuming you will output this to a webpage


Be careful about quotes (single and double) and
backslashes


SQL injection