Home arrow PHP arrow Page 2 - Databases and PHP

Relational Databases and SQL - PHP

If you're interested in learning how to access databases from PHP, you've come to the right place. This four-part series will focus on the PEAR DB system. This article is excerpted from chapter eight of the book Programming PHP, Second Edition, written by Kevin Tatroe, Rasmus Lerdorf, and Peter MacIntyre (O'Reilly, 2006; ISBN: 0596006810). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Databases and PHP
  2. Relational Databases and SQL
  3. PEAR DB Basics
  4. Data Source Names
By: O'Reilly Media
Rating: starstarstarstarstar / 8
June 14, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A Relational Database Management System (RDBMS) is a server that manages data for you. The data is structured into tables, where each table has a number of columns, each of which has a name and a type. For example, to keep track of science fiction books, we might have a “books” table that records the title (a string), year of release (a number), and the author.

Tables are grouped together into databases, so a science fiction book database might have tables for time periods, authors, and villains. An RDBMS usually has its own user system, which controls access rights for databases (e.g., “user Fred can update database authors”).

PHP communicates with relational databases such as MySQL and Oracle using the Structured Query Language (SQL). You can use SQL to create, modify, and query relational databases.

The syntax for SQL is divided into two parts. The first, Data Manipulation Language, or DML, is used to retrieve and modify data in an existing database. DML is remarkably compact, consisting of only four verbs:SELECT,INSERT,UPDATE, andDELETE. The set of SQL commands used to create and modify the database structures that hold the data is known as Data Definition Language, or DDL. The syntax for DDL is not as standardized as that for DML, but as PHP just sends any SQL commands you give it to the database, you can use any SQL commands your database supports.

The SQL Command file for creating this sample library database is available in a file called library.sql.

Assuming you have a table calledbooks, this SQL statement would insert a new row (check web site companion for source files):

  INSERT INTO books VALUES (4, 'I, Robot', '0-553-29438-5', 1950)

This SQL statement inserts a new row but specifies the columns for which there are values:

  INSERT INTO books (authorid, title, ISBN, pub_year) VALUES (4, 'I, Robot, '0-553-29438-5', 1950)

To delete all books that were published in 1979 (if any), we could use this SQL statement:

  DELETE FROM books WHERE pub_date = 1979

To change the year for Roots to 1983, use this SQL statement:

  UPDATE books SET pub_year=1983 WHERE title='Roots'

To fetch only the books published in the 1980s, use:

  SELECT * FROM books WHERE pub_year > 1979 AND pub_year < 1990

You can also specify the fields you want returned. For example:

  SELECT title, pub_year FROM books WHERE pub_year > 19790 AND pub_year < 1990

You can issue queries that bring together information from multiple tables. For example, this query joins together thebookandauthortables to let us see who wrote each book:

  SELECT authors.name, books.title FROM books, authors
 
WHERE authors.authorid = books.authorid.

For more on SQL, see SQL in a Nutshell, by Kevin Kline (O’Reilly).



 
 
>>> More PHP Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: