[Zend PHP5 Cerification] Lectures -- 6. Database and SQL

Youwill not be tested on database specific code

  • *InTheory* In reality many cross database experts have stated a beliefthat the exam has a MySQL base.

Abasic understanding of SQL and it’s concepts is required

 

SomeKey Terminology

DataBaseManagement System (DBMS)

Database

Table

Columnor Field

Recordor Column

Query

PrimaryKey

 

 

 

 

AlthoughSQL is considered a “standard” language, it is somewhat limitedin relation to the realworld needs of almost any application. As aresult, practically every database system in existence implements itsown “dialect” of SQL, while, for the most part, maintaining fullcompatibility with SQL-92. This makes writing truly portableapplications very challenging.

datatypes
int               32 byte
smallint        16byte
real             32 byte
float            64 byte

All of these data types are converted into eitherintegers or floating-point numbers when they are retrieved into a PHPvariable, which is not normally a problem. However, you need to beaware of the precision and range of each data type when you writedata from a PHP script into a database table, since it’s quitepossible that you will cause an overflow (which a databas systemshould at least report as a warning).

SQL-92 defines twostring types
char
varchar

In both cases, a string columnmust be given a length (usually between 1 and 255 characters,although some database systems do not follow this rule), which meansthat any string coming from PHP, where it can have an arbitrarylength, can be truncated, usually without even a warning, thusresulting in the loss of data.

Strings in SQL are enclosed bysingle quotation strings.
There a few important items to note:first of all, standard SQL does not allow the insertion of anyspecial escape sequences like \n. In addition, single quotation marksare normally escaped using another quotation mark; however, and thisis very important, not all database systems follow this convention.Luckily, however, almost every database access extension thatsupports PHP also provide specialized functions that will take careof escaping all the data for you.

SQL character strings actdifferently from PHP strings—in most cases, the former are “true”text strings, rather than collections of binary characters;therefore, you won’t be able to store binary data in an SQL string.Most database systems provide a separate data type (usually called“BLOB” for Binary Large OBject) for this purpose.

datetime
Adatabase system’s ability to represent dates goes well beyondPHP’s—thus opening the door to all sorts of potential problems,which are best solved by keeping all of your date-manipulationoperations inside the database itself, and only extract dates instring form when needed.

NULL
Columns that allow NULLvalues cannot be used as part of a primary key.

 

 

Relationships

Mostmodern database systems fall into the relational category, the basisof which is the relationship between various tables

Relationshipslink records based on some common data, relationships can be one toone, one to many, many to many

Databasesystems can be configured to enforce those relationships to maintainreferentialintegrity

 

Indices

Databasesare smart and fast, but the database designer has foreknowledge ofhow the database

willbe used

Withthis knowledge the designer can create an “index” on appropriatecolumns

Thisindex instructs the DBMS to store additional information about thedata in that column, to make locating data within it as fast aspossible

 

 

 

 

 

 

 

 

Create

CREATEDATABASE/SCHEMA <dbname>;

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

 

 

Indicesand Relationship

CREATEINDEX <indexname> ON <tablename> (<column1>[,..., <columnn>]);

index instructs the DBMS to storeadditional information about the data in that column, to makelocating data within it as fast as possible.

Foreign-keyrelationships are created either when a table is created, or at alater date with an altering statement.

CREATE TABLEbook_chapter (
isbn VARCHAR(13) REFERENCES book(id),
chapter_number INT NOT NULL,
chapter_titleVARCHAR(255)
);

This code creates a one-to-manyrelationship between the parent table book and the child tablebook_chapter based on the isbn field. Once this table is created, youcan only add a row to it if the ISBN you specify exists in book.

Tocreate a one-to-one relationship, you can simply make the connectivecolumns of a one-to-many relationship the primary key of the childtable.

 

 

Dropand Delete

 

Bevery careful with these operations. Especially when delete, check ifthere is an id provided!

 

 

Addingand Manipulating Data
This is done by means of the INSERTstatement, which takes on two forms:
INSERT INTO <tablename>VALUES (<field1value>[, ..., <fieldnvalue>]);

INSERTINTO <tablename>
(<field1>[, ...,<fieldn>])
VALUES
(<field1value>[, ...,<fieldnvalue>]);

UPDATE book SET publisher= ’Tor Science Fiction’;

DELETE FROM bookWHERE..

SELECT * FROM ...
 

 SQLJoins
There are two basic types of joins:inner joins and outer joins. In both cases, joins create a linkbetween two tables based on a common set of columns (keys).

Innerjoin
An inner join returns rows from bothtables only if keys from both tables can be found that satisfies thejoin conditions.

Note that inner joins only work well withassertive conditions—negative conditions often returnbizarre-looking results.

outerjoin
Outer joins return all records fromone table, while restricting the other table to matching records,which means that some of the columns in the results will contain NULLvalues. This is a powerful, yet sometimes confusing, feature of SQLdatabase engines.

Left joins
Leftjoins are a type of outer join in which every record in the lefttable that matches the WHERE clause (if there is one) will bereturned regardless of a match made in the ON clause of the righttable.

Right join
Rightjoins are analogous to left joins—only reversed: instead ofreturning all results from the “left” side, the right joinreturns all results from the “right” side, restricting resultsfrom the “left” side to matches of the ON clause.

AdvancedDatabase Topics

Mysql and Mysqliextension for PHP.

 

Handlemulti-queries / result sets feature.
transactionsand prepared statements
Transactions allow youto merge multiple queries into one atomic operation, either they ALLexecute successfully, or none do

BEGINTRANSACTION #name;
… queries here
COMMIT;

Preparedstatements (They’re in MySQLi I promise) allow you to increasespeed of repeated queries, and isolate data from command.
Firstyou Prepare the statement, then you bind parameters to it, then youexecute it.
Twovariants of prepared statements are available:
Boundparameters
The bound-parameter variant allows you to store a queryon the MySQL server, with only the iterative data being repeatedlysent to the server, and integrated into the query forexecution.

Bound results
The bound-result variant allowsyou to use sometimes-unwieldy indexed or associative arrays to pullvalues from result sets by binding PHP variables to correspondingretrieved fields, and then use those variables as necessary

booleanmysqli_stmt_bind_param (mysqli_stmt stmt, string types, mixed &var1[, mixed &varN)
or
classmysqli_stmt {
boolean bind_param (string types, mixed &var1 [,mixed &varN])
}

The types parameter represents thedatatypes of each respective variable to follow (represented by&var1, … &varN) and is required to ensure the mostefficient encoding of this data when it’s sent to theserver.

booleanmysqli_stmt_bind_result (mysqli_stmt stmt, mixed &var1 [, mixed&varN…])
or
classmysqli_stmt {
boolean bind_result (mixed &var1 [, mixed&varN])
}


Aftera query has been prepared and executed, you can bind variables to theretrieved fields by using $stmt->bind_result.


Createa statement object
$link =mysqli_connect("localhost", "u", "p","ex");

$stmt =mysqli_stmt_init($link);
or
$stmt= $mysqli->stmt_init();

 

Example:
$link =mysqli_connect("localhost", "u", "p","ex");
$city = "Montreal";
$stmt =mysqli_stmt_init($link);
if ($stmt = mysqli_stmt_prepare ($stmt,"SELECT Province FROM City WHEREName=?"))
{
mysqli_stmt_bind_param($stmt, "s",$city);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt,$province);
mysqli_stmt_fetch($stmt);
printf("%s is indistrict %s\n", $city,$province);
mysqli_stmt_close($stmt);
}
mysqli_close($link);


PDO
PDOstands for PHP Data Objects, and it presents a consistent objectoriented method to interact with various databases.
PDO on its ownis not able to access any databases, a database specific PDO drivermust also be installed.
In some situations PDO actually allows forgreater performance than the native database driver (e.g. MySql withprepared statements).


Connectingwith PDO

Regardlessof which database you are connecting to, you create a new instance ofthe same object. The connection type is defined in the firstparameter.

define(USERNAME, "preinheimer");
define(PASSWORD,"sillyPassword");
$pdoConnection = newPDO('mysql:host=localhost;dbname=example', USERNAME,PASSWORD);

Executing a Query
Toexecute a query, access the query method of the created PDO object.It can be iterated over to access the various rows in theresult.

foreach ($pdoConnection-> query("SELECT * FROMusers") AS $user)
{
echo "User number {$user['id']}has a username of {$user['userName']}\n";
}

PreparedStatements
$query = "SELECT * FROMposts WHERE topicID = :tid AND poster = :userid";
$statement= $pdoConnection->prepare($query, array(PDO::ATTR_CURSOR,PDO::CURSOR_FWDONLY));
$statement->execute(array(':tid' =>100, ':userid' => 12));
$userAPosts =$statement->fetchAll();
$statement->execute(array(':tid' =>100, ':userid' => 13));
$userBPosts =$statement->fetchAll();

Closingthe connection
Toclose a PDO connection, simply set the variable containing the PDOobject to null.

$pdoConnection= null;

.Databases are likely thefastest datastore you can access.

.SQLis a standardized language, most DBMS providers tweak or extend it insome way.
.The power lies in the SQL syntax which can be used tolocate, update or remove data with an almost terrifying level ofcomplexity.
.Indices are a great idea, use EXPLAIN or equivalentsyntax to confirm their use.

posted @ 2010-06-29 17:27  DavidHHuan  阅读(492)  评论(0编辑  收藏  举报