Database Programming

75 %
25 %
Information about Database Programming
Education

Published on February 12, 2014

Author: sicarii_13

Source: slideshare.net

Description

PHP provides access to a great number of different database systems, many of which are relational in nature and can be interrogated using Structured Query Language (SQL).

DATABASE PROGRAMMING Henry Osborne

DATABASE A comprehensive collection of related data organized for convenient access, generally in a computer

RELATIONAL DATABASE A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The relational database was invented by E. F. Codd at IBM in 1970. http://searchsqlserver.techtarget.com/definition/relational-database

INDICES • Make it possible to organize the data in a table according to one or more columns. • One of the cardinal elements of relational databases • Can usually be created on one or more columns of a table • Can also be declared as unique • Primary keys are a special type of unique index that is used to determine the “natural” method of uniquely identifying a row in a table

RELATIONSHIPS • One-to-one: at most, one row in the child table can correspond to each row in the parent table • One-to-many: an arbitrary number of rows in the child table can correspond to any one row in the parent table • Many-to-many: an arbitrary number of rows in the child table can correspond to an arbitrary number of rows in the parent table

DATA TYPES int or integer smallint real float char varchar Signed integer number, 32 bits in length. Signed integer number, 16 bits in length. Signed floating-point number, 32 bits in length. Signed floating-point number, 64 bits in length. Fixed-length character string. Variable-length character string.

CREATING DATABASES CREATE DATABASE <dbname>; CREATE SCHEMA <dbname>;

CREATE TABLES CREATE TABLE <tablename> ( <col1name> <col1type> [<col1attributes>], [... <colnname> <colntype> [<colnattributes>]] );

CREATE TABLES CREATE TABLE book ( id INT NOT NULL PRIMARY KEY, isbn VARCHAR(13), title VARCHAR(255), author VARCHAR(255), publisher VARCHAR(255) );

CREATING INDICES AND RELATIONSHIPS CREATE INDEX <indexname> ON <tablename> (<column1>[, ..., <columnn>]); CREATE INDEX book_isbn ON book (isbn);

CREATING INDICES AND RELATIONSHIPS CREATE TABLE book_chapter ( isbn VARCHAR(13) REFERENCES book (id), chapter_number INT NOT NULL, chapter_title VARCHAR(255) );

DROPPING OBJECTS DROP TABLE book_chapter; DROP SCHEMA my_book_database;

ADDING/MANIPULATING DATA INSERT INTO <tablename> VALUES (<field1value>[, ..., <fieldnvalue>]); OR INSERT INTO <tablename> (<field1>[, ..., <fieldn>]) VALUES (<field1value>[, ..., <fieldnvalue>]);

ADDING/MANIPULATING DATA INSERT INTO book (isbn, title, author) VALUES (’0812550706’, ’Ender’s Game’, ’Orson Scott Card’);

ADDING/MANIPULATING DATA To update records, you can use the UPDATE statement. UPDATE book SET publisher = ’Tor Science Fiction’, author = ’Orson S. Card’ WHERE isbn = ’0812550706’;

REMOVE DATA DELETE FROM book; DELETE FROM book WHERE isbn = ’0812550706’;

RETRIEVE DATA SELECT * FROM book; SELECT * FROM book WHERE author = ’Ray Bradbury’; SELECT * FROM book WHERE author = ’Ray Bradbury’ OR author = ’George Orwell’; SELECT * FROM book WHERE author = ’Ray Bradbury’ AND publisher LIKE ’%Del Ray’;

SQL JOINS SELECT * FROM book INNER JOIN book_chapter ON book.isbn = book_chapter.isbn;

OUTER JOINS SELECT book.title, author.last_name FROM author LEFT JOIN book ON book.author_id = author.id;

OUTER JOINS SELECT book.title, author.last_name FROM author RIGHT JOIN book ON book.author_id = author.id;

PHP DATA OBJECTS (PDO) • The standard distribution of PHP 5.1 and greater includes PDO and the drivers for SQLite by default • There are many other database drivers for PDO, including: • Microsoft SQL Server • Firebird • MySQL • Oracle • PostgreSQL, and • ODBC

PHP DATA OBJECTS (PDO) • Once installed, the process for using each driver is, for the most part, the same because PDO provides a unified data access layer to each of these database engines. • There is no longer a need for separate mysql_query() or pg_query() functions. • PDO provides a single object-oriented interface to these databases.

DATABASE CONNECTION $dsn = ’mysql:host=localhost;dbname=library’; $dbh = new PDO($dsn, ’dbuser’, ’dbpass’);

ERROR HANDLING try { $dsn = ’mysql:host=localhost;dbname=library’; $dbh = new PDO($dsn, ’dbuser’, ’dbpass’); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // All other database calls go here } catch (PDOException $e) { echo ’Failed: ’ . $e->getMessage(); }

DATABASE QUERY WITH PDO $author = ’’; if (ctype_alpha($_GET[’author’])){ $author = $_GET[’author’]; } // Escape the value of $author with quote() $sql = ’SELECT author.*, book.* FROM author LEFT JOIN book ON author.id = book.author_id WHERE author.last_name = ’ . $dbh->quote($author); // Execute the statement and echo the results $results = $dbh->query($sql); foreach ($results as $row) { echo "{$row[’title’]}, {$row[’last_name’]}n"; }

DATABASE QUERY WITH PDO $results = $dbh->query($sql); $results->setFetchMode(PDO::FETCH_OBJ); foreach ($results as $row) { echo "{$row->title}, {$row->last_name}n"; }

DATABASE QUERY WITH PDO $sql = "INSERT INTO book (isbn, title, author_id, publisher_id) VALUES (’0395974682’, ’The Lord of the Rings’, 1, 3)"; $affected = $dbh->exec($sql); echo "Records affected: {$affected}";

DATABASE PROGRAMMING

Add a comment

Related presentations

Related pages

Database Programming

Database programming refers to the process of establishing and configuring a set of records or files. Several programs exist for database programming.
Read more

Database Programming -Jobs und -Wettbewerbe | Freelancer

Arbeiten Sie an den neuesten auf Freelancer veröffentlichten Database Programming -Jobs. Finden Sie Programmierer und Webdesigner, die auf Ihre Database ...
Read more

Database - Wikipedia, the free encyclopedia

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically ...
Read more

Mastering C# Database Programming: Amazon.de: Jason Price ...

Jason Price - Mastering C# Database Programming jetzt kaufen. ISBN: 0025211441831, Fremdsprachige Bücher - Sprachen & Tools
Read more

Database Programming: Amazon.de: Robert A. Wray, Philip J ...

Database Programming: Amazon.de: Robert A. Wray, Philip J. Pratt: Fremdsprachige Bücher Amazon.de Prime testen Fremdsprachige Bücher. Los. Alle ...
Read more

C++ Database Programming | Database Programming

Out of all of the different information that programmers are required to know and understand at the drop of a hat, there are some that are more complicated ...
Read more

Computer Programming/Database Programming - Wikibooks ...

Database Programming Introduction . A database is a repository of information managed by a database engine which ensures integrity of data and fast access ...
Read more

what do database programmers do? - Programmers Stack Exchange

what do database programmers do? ... Oracle is not just a database but a complete programming environment, including forms and report designers.
Read more

Database programming tutorial: What are databases? | lynda ...

Database programming tutorial: What are databases? | lynda.com ... Database programming tutorial: First normal form | lynda.com - Duration: 4:39.
Read more

Mastering C# Database Programming ebook | weltbild.de

eBook Shop: Mastering C# Database Programming von Jason Price als Download. Jetzt eBook herunterladen & bequem mit Ihrem Tablet oder eBook Reader lesen.
Read more