1 / 27

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. Courses. Students. Setting up a database. Simple approach: Get an ENGR database

rudyard-bentley
Download Presentation

Databases

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Databases http://www.flickr.com/photos/torkildr/3462607995/

  2. Overview of databases • Database = structured storage for your data • Each database can have several tables Grades Courses Students

  3. 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.

  4. 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. It is also possible to have a "composite key" – two or more columnsthat as a combination are unique for each row.

  5. Other columns in the table Other columns in the table can be strings (aka "varchars"), ints, etc. The data types supported vary by database. MySQL supports these…http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html

  6. Foreign keys Sometimes, a non-primary column in one table references the primary key in another table. This is called a "foreign key constraint."

  7. PHP Built-in Database Libraries • mysql – This was a standard for some time. Still used in legacy applications, deprecated as of PHP 5.5.0. • mysqli – A replacement for mysql. Big features include transactions and prepared statements • PDO – A more abstract interface. Adds complexity but makes it possible to switch out database drivers. (eg. Switch MySQL for MS SQL Server)

  8. mysqli Constructor • All documentation from php.net as of 1/2014 • We need $host, $username, $passwd and $dbname • If using non default ports or sockets, you need to specify it here • Because they are the first 3 arguments, we need not specify them by name

  9. Example Connection <?php $mysqli = new mysqli("oniddb.cws.oregonstate.edu","scaffidc-db","mYRBEDC15SlLJXmv","scaffidc-db"); if(!$mysqli || $msqli->connect_errno){ echo "Connection error " . $mysqli->connect_errno . " " . $mysqli->connect_error; } else { echo "Connected!!!"; } mysqli_close($mysqli); ?>

  10. 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

  11. 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

  12. Let’s create some tables! <?php $mysqli = new mysqli("oniddb.cws.oregonstate.edu","scaffidc-db","mYRBEDC15SlLJXmv","scaffidc-db"); if ($mysqli->connect_errno) { printf("Connect failed: %s\n", $mysqli->connect_error); exit(); } else { printf("Connected.\n"); } $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, onidvarchar(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(); ?>

  13. 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,'amorgan2','A. Morgan')"); $mysqli->query("insert into students(sid,onid,name) values(931905000,'rholdt','R. Holdt')"); … /* watch out for, and remove, extra carriage returns above */

  14. 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>"; …

  15. 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>"; …

  16. 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>"; …

  17. 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 Courses

  18. 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 jQuery 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.

  19. 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: %s\n", $mysqli->error); }

  20. Types of prepared statement params • i integer • d double • s string • b blob

  21. 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(); }

  22. Walking Through php.net Examples • You will want to become familiar with PHP.net so we will look at their examples • http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php • This one page shows how to do almost every operation you will need for this class in terms of database use • The exceptions are UPDATEs and DELETEs but they are very similar to INSERT and SELECT respectivley • The manual is licensed under the creative commons attribution 3.0 license http://creativecommons.org/licenses/by/3.0/

  23. Example: Minimalist db editor (part 1) <!DOCTYPE html><html><head> <script src="jquery-1.8.2.min.js"></script> <script src="jquery.validate.min.js"></script> <script> $(document).ready(function() {$("form").validate();}); </script> <style> .courses td { border: 1px solid black; } </style> </head><body> <form method="post"> cid: <input name="cid" class="required digits" maxlength="3" minlength="3"><BR> title: <input name="title" class="required" maxlength="200"><BR> prof: <input name="prof" maxlength="64"><BR> cred: <input name="cred" class="required digits" maxlength="1" minlength="1"><BR> cap: <input name="cap" class="required digits" maxlength="2" minlength="2"><BR> <input type="submit" value="OK"> </form>

  24. Example: Minimalist db editor (part 2) <?php ini_set('display_errors', 'On'); $mysqli = new mysqli("oniddb.cws.oregonstate.edu","scaffidc-db","mYRBEDC15SlLJXmv","scaffidc-db"); if ($mysqli->connect_errno) { printf("Connect failed: %s\n", $mysqli->connect_error); exit(); } $cid = array_key_exists("cid", $_REQUEST) ? $_REQUEST["cid"] : 0; $title = array_key_exists("title", $_REQUEST) ? $_REQUEST["title"] : ''; $prof = array_key_exists("prof", $_REQUEST) ? $_REQUEST["prof"] : ''; $cred = array_key_exists("cred", $_REQUEST) ? $_REQUEST["cred"] : 0; $cap = array_key_exists("cap", $_REQUEST) ? $_REQUEST["cap"] : 0;

  25. Example: Minimalist db editor (part 3) if ($cid <= 0) echo ""; else if (!preg_match('/^[0-9]{3}$/', $cid)) echo "Invalid cid"; else if (!preg_match('/^[0-9]$/', $cred)) echo "Invalid cred"; else if (!preg_match('/^[0-9]{2}$/', $cap)) echo "Invalid cap"; else if ($cid > 0) { $rs = $mysqli->query("select cid from courses where cid = ".$cid); if ($rs->num_rows == 0) { $stmt = $mysqli->prepare("insert into courses(prof,cred,cap,title,cid) values(?,?,?,?,?)"); } else { $stmt = $mysqli->prepare("update courses set prof=?,cred=?,cap=?,title=? where cid=?"); } $stmt->bind_param("siisi", $prof, $cred, $cap, $title, $cid); $stmt->execute(); }

  26. Example: Minimalist db editor (part 4) echo "<table class=courses>"; echo "<tr><td>Course<td>Title<td>Prof<td>Cred<td>Capacity</tr>"; 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>"; $mysqli->close(); ?> </body></html>

  27. 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?

More Related