Zend PHP5 certification Exam study review 5: Database and SQL

Although the GUIDE says the Exam is database neutral, but in fact, it is somewhat regarding the Mysql database. Anyway, you should have good understanding with the SQL, such as joins, indices, primary keys, multi query, ect. Regarding PHP, you should read something about the new mysqli extension as well as the PDO object.
SQL-92

Although SQL is considered a “standard” language, it is somewhat limited in relation to the realworld needs of almost any application. As a result, practically every database system in existence implements its own “dialect” of SQL, while, for the most part, maintaining full compatibility with SQL-92. This makes writing truly portable applications very challenging.

data types
int 32 byte
smallint 16 byte
real 32 byte
float 64 byte

All of these data types are converted into either integers or floating-point numbers when they are retrieved into a PHP variable, which is not normally a problem. However, you need to be aware of the precision and range of each data type when you write data from a PHP script into a database table, since it’s quite possible that you will cause an overflow (which a databas system should at least report as a warning).

SQL-92 defines two string types
char
varchar

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

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

SQL character strings act differently 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
A database system’s ability to represent dates goes well beyond PHP’s—thus opening the door to all sorts of potential problems, which are best solved by keeping all of your date-manipulation operations inside the database itself, and only extract dates in string form when needed.

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

Create

CREATE DATABASE/SCHEMA <dbname>;

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

Indices and Relationship
CREATE INDEX <indexname> ON <tablename> (<column1>[, …, <columnn>]);

index instructs the DBMS to store additional information about the data in that column, to make locating data within it as fast as possible.

Foreign-key relationships are created either when a table is created, or at a later date with an altering statement.

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

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

To create a one-to-one relationship, you can simply make the connective columns of a one-to-many relationship the primary key of the child table.

Drop

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

INSERT INTO <tablename>
(<field1>[, …, <fieldn>])
VALUES
(<field1value>[, …, <fieldnvalue>]);

UPDATE book SET publisher = ’Tor Science Fiction’;

DELETE FROM book WHERE..

SELECT * FROM

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

Inner join
An inner join returns rows from both tables only if keys from both tables can be found that satisfies the join conditions.

Note that inner joins only work well with assertive conditions—negative conditions often return bizarre-looking results.

outer join
Outer joins return all records from one table, while restricting the other table to matching records, which means that some of the columns in the results will contain NULL values. This is a powerful, yet sometimes confusing, feature of SQL database engines.

Left joins
Left joins are a type of outer join in which every record in the left table that matches the WHERE clause (if there is one) will be returned regardless of a match made in the ON clause of the right table.

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

Advanced Database Topics

Mysql and Mysqli extension for PHP.


transactions and prepared statements
Transactions allow you to merge multiple queries into one atomic operation, either they ALL execute successfully, or none do

BEGIN TRANSACTION #name;
… queries here
COMMIT;

Prepared statements (They’re in MySQLi I promise) allow you to increase speed of repeated queries, and isolate data from command.
First you Prepare the statement, then you bind parameters to it, then you execute it.
Two variants of prepared statements are available:
Bound parameters
The bound-parameter variant allows you to store a query on the MySQL server, with only the iterative data being repeatedly sent to the server, and integrated into the query for execution.

Bound results
The bound-result variant allows you to use sometimes-unwieldy indexed or associative arrays to pull values from result sets by binding PHP variables to corresponding retrieved fields, and then use those variables as necessary

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

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

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


After a query has been prepared and executed, you can bind variables to the retrieved fields by using $stmt->bind_result.

Create a 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 WHERE Name=?”))
{
mysqli_stmt_bind_param($stmt, “s”, $city);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $province);
mysqli_stmt_fetch($stmt);
printf(“%s is in district %s\n”, $city, $province);
mysqli_stmt_close($stmt);
}
mysqli_close($link);

PDO
PDO stands for PHP Data Objects, and it presents a consistent object oriented method to interact with various databases.
PDO on its own is not able to access any databases, a database specific PDO driver must also be installed.
In some situations PDO actually allows for greater performance than the native database driver (e.g. MySql with prepared statements).


Connecting with PDO

Regardless of which database you are connecting to, you create a new instance of the same object. The connection type is defined in the first parameter.

define(USERNAME, “preinheimer”);
define(PASSWORD, “sillyPassword”);
$pdoConnection = new PDO(‘mysql:host=localhost;dbname=example’, USERNAME, PASSWORD);

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

foreach ($pdoConnection-> query(“SELECT * FROM users”) AS $user)
{
echo “User number {$user[‘id’]} has a username of {$user[‘userName’]}\n”;
}

Prepared Statements
$query = “SELECT * FROM posts 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();

Closing the connection
To close a PDO connection, simply set the variable containing the PDO object to null.

$pdoConnection = null;

.
Databases are likely the fastest datastore you can access.
.SQL is a standardized language, most DBMS providers tweak or extend it in some way.
.The power lies in the SQL syntax which can be used to locate, update or remove data with an almost terrifying level of complexity.
.Indices are a great idea, use EXPLAIN or equivalent syntax to confirm their use.

No Comments - Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *

*