Chapter 10. Database Access

10.1. Introduction

Many SQL examples in this chapter use a table of information about Zodiac signs. The table's structure is:

CREATE TABLE zodiac (
  id INT UNSIGNED NOT NULL,
  sign CHAR(11),
  symbol CHAR(13),
  planet CHAR(7),
  element CHAR(5),
  start_month TINYINT,
  start_day TINYINT,
  end_month TINYINT,
  end_day TINYINT,
  PRIMARY KEY(id)
);

And the data in the table is:

INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19);
INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20);
INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21);
INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22);
INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22);
INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22);
INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23);
INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',20,24,11,21);
INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,21);
INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19);
INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,18);
INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20);

if (! $dbh = OCILogon('david', 'foo!bar','ORAINST')) {
    die("Can't connect: ".OCIError());
}

if (! $sth = OCIParse($dbh,'SELECT * FROM zodiac')) {
    die("Can't parse query: ".OCIError());
}

if (! OCIExecute($sth)) {
    die("Can't execute query: ".OCIError());
}

$cols = OCINumCols($sth);
while (OCIFetch($sth)) {
    for ($i = 1; $i <= $cols; $i++) {
        print OCIResult($sth,$i);
        print " ";
    }
    print "\n";
}

if (! $dbh = pg_connect('dbname=test user=david password=foo!bar')) {
    die("Can't connect: ".pg_errormessage());
}

if (! $sth = pg_exec($dbh,'SELECT * FROM zodiac')) {
    die("Can't execute query: ".pg_errormessage());
}

for ($i = 0, $j = pg_numrows($sth); $i < $j; $i++) {
    $ar = pg_fetch_row($sth,$i);
    foreach ($ar as $col) {
        print "$col ";
    }
    print "\n";
}

if (! $dbh = mysql_connect('localhost','david','foo!bar')) {
    die("Can't connect: ".mysql_error());
}

mysql_select_db('test');

if (! $sth = mysql_query('SELECT * FROM zodiac')) {
    die("Can't execute query: ".mysql_error());
}

while ($ar = mysql_fetch_row($sth)) {
    foreach ($ar as $col) {
        print "$col ";
    }
    print "\n";
}

Each example prints out all the data in the zodiac table, one row per line, with spaces between each field, as shown here:

Aries Ram Mars fire 3 21 4 19 
Taurus Bull Venus earth 4 20 5 20 
Gemini Twins Mercury air 5 21 6 21 
Cancer Crab Moon water 6 22 7 22 
Leo Lion Sun fire 7 23 8 22 
Virgo Virgin Mercury earth 8 23 9 22 
Libra Scales Venus air 9 23 10 23 
Scorpio Scorpion Mars water 20 24 11 21 
Sagittarius Archer Jupiter fire 11 22 12 21 
Capricorn Goat Saturn earth 12 22 1 19 
Aquarius Water Carrier Uranus air 1 20 2 18 
Pisces Fishes Neptune water 2 19 3 20 

require 'DB.php';
$dbh = DB::connect('mysql://david:foo!bar@localhost/test');
$sth = $dbh->query('SELECT * FROM zodiac');
while ($row = $sth->fetchRow()) {
    print join(' ',$row)."\n";
}

While it's possible to write SQL that works on different databases with a minimum of changes, tuning a database for speed and efficiency is not portable. Having portable database interactions can be a useful goal, but it needs to be balanced with the likelihood of your code being used with multiple databases. If you're writing code for wide distribution, working with many databases is a plus. If your code is an internal project, however, you probably don't need to be as concerned with database independence.

For example, if $_REQUESTS['excuse'] is "Ferris wasn't sick," and magic_quotes_gpc is on, this query executes successfully:

$dbh->query("INSERT INTO excuses (truth) VALUES ('" . $_REQUESTS['excuse'] . ')');