DATABASES http www flickr comphotostorkildr3462607995 Overview of databases
DATABASES http: //www. flickr. com/photos/torkildr/3462607995/
Overview of databases • Database = structured storage for your data • Each database can have several tables Grades sid cid grade Courses cid prof 931905000 361 4. 0 361 cscaffid 4 70 SE I 931905000 362 3. 0 362 agroce 4 70 SE II 931905001 361 4. 0 496 cscaffid 4 70 Mobile/Cloud 931905001 362 4. 0 931905001 496 931905002 361 cred cap title 3. 0 Students sid onid name 2. 0 931905000 cjones C. Jones 931905001 amorgan 2 A. Morgan 931905002 rholdt R. Holdt
Setting up a database • Simple approach: Get an ENGR database Go to http: //onid. oregonstate. edu, log in, click Web Database on the left. It give you the sample PHP code to use as well as database names and passwords.
Database table overview: primary keys Usually, your table will have a "primary key" – a column that is unique for each row. Usually, you will use an integer as your primary key. cid prof cred cap title 361 cscaffid 4 70 SE I 362 agroce 4 70 SE II 496 cscaffid 4 70 Mobile/Cloud It is also possible to have a "composite key" – two or more columns that as a combination are unique for each row.
Other columns in the table can be strings (aka "varchars"), ints, etc. cid prof cred cap title 361 cscaffid 4 70 SE I 362 agroce 4 70 SE II 496 cscaffid 4 70 Mobile/Cloud The data types supported vary by database. My. SQL supports these… http: //dev. mysql. com/doc/refman/5. 0/en/data-type-overview. html
Foreign keys Sometimes, a non-primary column in one table references the primary key in another table. This is called a "foreign key constraint. " sid cid grade cid prof cred cap title 931905000 361 4. 0 361 cscaffid 4 70 SE I 931905000 362 3. 0 362 agroce 4 70 SE II 931905001 361 4. 0 496 cscaffid 4 70 Mobile/Cloud 931905001 362 4. 0 931905001 496 3. 0 931905002 361 2. 0
Example Connection Current ONID Database Settings Hostname oniddb. cws. oregonstate. edu Database Name scaffidc-db Username scaffidc-db Password m. YRBEDC 15 Sl. LJXmv <? php $mysqli = new mysqli("oniddb. cws. oregonstate. edu", "scaffidc-db", "m. YRBEDC 15 Sl. LJXmv", "scaffidc-db"); mysqli_close($mysqli); ? >
Queries • mysqli object has a query() method • Use with caution, does nothing to protect against injection • Generally you will just provide a query as a single argument to this function
Queries Continued • Return values: – FALSE on failure – For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries returns a mysqli_result object – For other successful queries returns TRUE
Let’s create some tables! <? php $mysqli = new mysqli("oniddb. cws. oregonstate. edu", "scaffidc-db", "m. YRBEDC 15 Sl. LJXmv", "scaffidc-db"); $mysqli->query("drop table grades"); $mysqli->query("drop table students"); $mysqli->query("drop table courses"); /* watch out for, and remove, extra carriage returns below */ if (!$mysqli->query("create table courses(cid integer, prof varchar(64), cred integer, cap integer, title varchar(200), primary key(cid))") || !$mysqli->query("create table students(sid integer, onid varchar(32), name varchar(200), primary key(sid))") || !$mysqli->query("create table grades(cid integer, sid integer, grade decimal(3, 2), primary key(sid, cid), foreign key(sid) references students, foreign key(cid) references courses)") ){ printf("Cannot create table(s). n"); } $mysqli->close(); ? >
Let’s insert some rows! … /* watch out for, and remove, extra carriage returns below */ $mysqli->query("insert into courses(cid, prof, cred, cap, title) values(361, 'cscaffid', 4, 70, 'SE I')"); $mysqli->query("insert into courses(cid, prof, cred, cap, title) values(362, 'agroce', 4, 70, 'SE II')"); $mysqli->query("insert into courses(cid, prof, cred, cap, title) values(496, 'cscaffid', 4, 70, 'Mobile/Cloud')"); $mysqli->query("insert into students(sid, onid, name) values(931905000, 'cjones', 'C. Jones')"); $mysqli->query("insert into students(sid, onid, name) values(931905001, 'amorgan 2', 'A. Morgan')"); $mysqli->query("insert into students(sid, onid, name) values(931905000, 'rholdt', 'R. Holdt')"); … /* watch out for, and remove, extra carriage returns above */
Reading all the rows from a table … /* watch out for, and remove, extra carriage returns below */ echo "<table>"; if ($result = $mysqli->query("select cid, prof, cred, cap, title from courses")) { while($obj = $result->fetch_object()){ echo "<tr>"; echo "<td>". htmlspecialchars($obj->cid). "</td>"; echo "<td>". htmlspecialchars($obj->title). "</td>"; echo "<td>". htmlspecialchars($obj->prof). "</td>"; echo "<td>". htmlspecialchars($obj->cred). "</td>"; echo "<td>". htmlspecialchars($obj->cap). "</td>"; echo "</tr>"; } $result->close(); } echo "</table>"; …
Updating one row in the table … $mysqli->query("update courses set cap=80 where cid = 362"); echo "<table>"; if ($result = $mysqli->query("select cid, prof, cred, cap, title from courses where cid >= 300 and cid <= 400")) { while($obj = $result->fetch_object()){ echo "<tr>"; echo "<td>". htmlspecialchars($obj->cid). "</td>"; echo "<td>". htmlspecialchars($obj->title). "</td>"; echo "<td>". htmlspecialchars($obj->prof). "</td>"; echo "<td>". htmlspecialchars($obj->cred). "</td>"; echo "<td>". htmlspecialchars($obj->cap). "</td>"; echo "</tr>"; } $result->close(); } echo "</table>"; …
Deleting one row in the table … $mysqli->query("delete from courses where cid = 362"); echo "<table>"; if ($result = $mysqli->query("select cid, prof, cred, cap, title from courses where cid >= 300 and cid <= 400")) { while($obj = $result->fetch_object()){ echo "<tr>"; echo "<td>". htmlspecialchars($obj->cid). "</td>"; echo "<td>". htmlspecialchars($obj->title). "</td>"; echo "<td>". htmlspecialchars($obj->prof). "</td>"; echo "<td>". htmlspecialchars($obj->cred). "</td>"; echo "<td>". htmlspecialchars($obj->cap). "</td>"; echo "</tr>"; } $result->close(); } echo "</table>"; …
Deleting data when you have foreign keys • Table X references table Y, delete rows from Y before rows from X. (e. g. , delete Grades before Courses. ) Grades sid cid grade Courses cid prof 931905000 361 4. 0 361 cscaffid 4 70 SE I 931905000 362 3. 0 362 agroce 4 70 SE II 931905001 361 4. 0 496 cscaffid 4 70 Mobile/Cloud 931905001 362 4. 0 494 cscaffid 4 20 Web dev 931905001 496 3. 0 931905002 361 2. 0 cred cap title
A brief word about security • DO NOT trust the data from the server – One of your co-workers might have allowed users to put evil data into the database! – You can remove special characters with htmlspecialchars() • DO NOT trust the data from the browser – JS and j. Query form validation can be bypassed! – Used prepared statements if you need to use data from the browser in your SQL statements • Bonus: Potential for performance improvement if you need to repeatedly execute an SQL statement.
Inserting with a prepared statement /* Imagine, for a moment, that we are working with data from the browser */ if ($stmt = $mysqli->prepare("insert into courses(cid, prof, cred, cap, title) values(? , ? , ? )")) { for ($i = 100; $i < 300; $i++) { $cid = $i; $prof = "Professor X$i"; $cred = 3; $cap = ($cid < 200 ? 150 : 50); $title = "CS Course $i"; /* for five params, pass five character types to bind_param with five values */ $stmt->bind_param("isiis", $cid, $prof, $cred, $cap, $title); $stmt->execute(); } $stmt->close(); } else { printf("Error: %sn", $mysqli->error); }
Types of prepared statement params • • i d s b integer double string blob
Querying with a prepared statement echo "<table border=1>"; $stmt = $mysqli->prepare("select cid, prof, cred, cap, title from courses where cid >= ? and cid < ? "); $cidmin = 165; $cidmax = 170; $stmt->bind_param("ii", $cidmin, $cidmax); if ($stmt->execute()) { $stmt->bind_result($cid, $prof, $cred, $cap, $title); while($stmt->fetch()){ echo "<tr>"; echo "<td>". htmlspecialchars($cid). "</td>"; echo "<td>". htmlspecialchars($title). "</td>"; echo "<td>". htmlspecialchars($prof). "</td>"; echo "<td>". htmlspecialchars($cred). "</td>"; echo "<td>". htmlspecialchars($cap). "</td>"; echo "</tr>"; } } $stmt->close();
Walk through another site skeleton Browse at http: //web. engr. oregonstate. edu/~scaffidc/courses/cs 290/skeleton 2/ Download at http: //web. engr. oregonstate. edu/~scaffidc/courses/cs 290/lectures/site_skeleton 2. zip Good points: Demonstrates PHP-db integration Bad points: No input validation or data-editing; same page title on every page
We’re just scratching the surface… • Other features supported by databases – – – – Auto-assigned keys (covered later in this course) Indexes (covered later in this course) Transactions Aggregates, grouping, sorting Views and synthetic/computed columns Triggers and stored procedures Import, export, and backup Replication • For details, take the database courses – And/or cover these in your How-To?
Activity Spend the rest of class working on homework http: //web. engr. oregonstate. edu/~scaffidc/courses/cs 290/homework. shtml
- Slides: 22
