hi.pe.kr 날으는물고기·´″°³о♡

While writing some PHP Training materials for Pale Purple, I thought I'd add an updated guide on PHP and database access. I've already done one on PEAR::DB, but PEAR::MDB2 is it's successor and has a slightly different API.... and as PEAR::DB is now deprecated, it's probably about time I rewrote it anyway.

What is PEAR::MDB2?

MDB2 is yet another database API for PHP. It's written in PHP, so can be used in a cross platform manner. Because it's written in PHP, it's not going to be as 'fast' as the PDO library, however it's portability may make up for that.

Compared to the native bundled legacy PHP libraries (mysqli_*, pgsql_*, sqlite_*, mysql_), changing which database you are using can be as simple as changing the connection parameters to your database. The PDO libraries offer the same advantage, however they require PHP5.

Getting started

It's recommended you install it using the Pear installer, like the following :

pear install MDB2
pear install MDB2_Driver_$db

Where $db would be one of e.g. mysql, pgsql, sqlite, ori8, mssql etc.

You'll note, that unlike PEAR::DB, it's necessary to download a specific driver for each database you wish to support.

Connecting to a database

One of the advantages of MDB2 over PEAR::DB is that it offers a number of different ways of providing a DB connection - either lazy (MDB2::factory()), optimistic (MDB2::connect()) or singleton (MDB2::singleton()) . I personally tend to just go with the lazy 'factory' method where the connection isn't created until it's used.

As with PEAR::DB, you specify which database to connect to using a JDBC style URL, which looks something like : $db_driver://$user:$password@$host/$database_name, so for example pgsql://web:password@dbserver/my_database would be a valid url.

<?php 
require_once("MDB2.php");
$con = MDB2::factory($url);

Error checking

As always, there is plenty of scope for something to go wrong. Perhaps the database server is offline, or your username/password are incorrect. In any case, error handling with MDB2 follows the PEAR convention of using PEAR::isError($return_value). So in our case, our code could now look a bit like :

<?php
require_once("MDB2.php");
$con = MDB2::factory($url);
if(PEAR::isError($con)) {
    die("Error while connecting : " . $con->getMessage());
}
// use $con

Security (SQL Injection)

When ever talking to a relational database, care has to be taken that any parameters passed into a query have been appropriately sanitised. Failure to sanitise data will make your site vulnerable to SQL injection, data loss or compromise. If you don't know what SQL Injection is, try reading the following article at lwn.net.

Issuing Queries on the database

Select queries (returning data)

<?php
// load library as above.
// connect to db as above. ($con = ... )
// check $con validity as per above example.

$sql = "SELECT * FROM the_table";
$resultset = $con->query($sql);
if(PEAR::isError($resultset)) {
    die('Failed to issue query, error message : ' . $resultset->getMessage());
}

while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    foreach($row as $field => $value) {
        echo "$field / $value \n";
    }
}

Non returning queries (update, insert, delete)

This is largely the same as above, apart from the fact there is no need to do the while loop / fetchRow section, so the code becomes somewhat simpler and would look a bit like :

// load library
// connect to db, and check for errors
$sql = "UPDATE table SET column = 5 WHERE id = 1";
$result = $con->query($sql);
if(PEAR::isError($result)) {
    die("Failed to issue query, error message : " . $result->getMessage();
}

If you need to add in user supplied data into the query, you can use $save_var = $con->quote($var). An optional second parameter can be given to determine whether the data should be reformatted as a boolean, date, integer or text.

Prepared statements (or how to safely pass parameters into a database query)

A safer (and potentially better performing) approach to insert user supplied data into queries is to use Prepared Statements. These can be faster, as if the underlying database supports it, the statement itself can be compiled and cached, so saving the database engine from having to reparse the same SQL each time. They are safer, because SQL injection is not possible when using them - because the database knows what goes where. Some database engines (e.g. MySQL v4) do not support prepared statements, in which case, like PEAR::DB, PEAR::MDB2 will emulate the functionality in the background.

A prepared statement example:

<?php
// load library, get connection etc (as per above)

$sql = "UPDATE table SET column = ? WHERE id = ?";
$types = array('integer', 'text');
$statement = $con->prepare($sql, $types, MDB2_PREPARE_MANIP);
$data = array(5, 'blah');
$affected_rows = $statment->execute($data);
if(PEAR::isError($affected_rows)) {
    // die etc.
}

MDB2_PREPARE_MANIP is required if you wish to manipulate data, and not return any values. MDB2_PREPARE_RESULT is used if you wish to run a prepared statement and have data returned, for instance :

The $types array is not required (you could replace it with TRUE), as MDB2 can automatically handle it.

<?php
// load library, get db connection etc.
$types = array("text");
$statement = $con->prepare("SELECT * FROM table WHERE column = ?", $types, MDB2_PREPARE_RESULT);
$data = array('foo');
$resultset = $statement->execute($data);
if(PEAR::isError($resultset)) {
    die('failed... ' . $resultset->getMessage());
}
$statement->Free();
while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    echo "Found : " . $row['column'] . "\n";
}

Suggestions for Usage

I'm not particularly keen on writing the same thing twice, so I tend to use functions like the following :

<?php
require_once("MDB2.php");
function connect() {
    $con = MDB2::factory($url);
    if(PEAR::isError($con)) {
        die("Error while connecting : " . $con->getMessage());
    }
    return $con;
}

Then something like the following to undertake queries :

function execute_query($sql, $values=array()) {
    $con = connect();
    $results = array();
    if(sizeof($values) > 0) {
        $statement = $con->prepare($sql, TRUE, MDB2_PREPARE_RESULT);
        $resultset = $statement->execute($values);
        $statement->free();
    }
    else {
        $resultset = $con->query($sql);
    }
    if(PEAR::isError($resultset)) {
        die('DB Error... ' . $resultset->getMessage());
    }

    while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
        $results[] = $row;
    }
    return $results;
}

Thanks for reading; feel free to leave comments/suggestions (or questions).

http://pear.php.net/reference/Auth-1.3.0/Auth/_Auth-1.3.0_Container_MDB2_php.html

http://pecl.php.net/package/imagick

이 사이트에 방문하시어 최종 업데이트 된 제품을 다운로드 합니다. 

wget http://pecl.php.net/get/imagick-2.2.1.tgz

# tar zxf imagick-2.2.1.tgz
# cd imagick-2.2.1.tgz
# phpize

# ./configure  --with-php-config=/usr/local/php/bin/php-config

 에러가 발생할 수 있습니다. 

checking ImageMagick MagickWand API configuration program... configure: error: not found. Please provide a path to MagickWand-config or Wand-config program.

# yum -y install ImageMagick-devel

Transaction Check Error:
  file /etc/fonts/conf.d/30-aliases-fedora.conf from install of fontconfig-2.4.1-7.el5 conflicts with file from package fontconfig-2.4.1-6.el5
  file /usr/share/hal/fdi/information/10freedesktop/20-video-quirk-pm-el5-lenovo.fdi from install of hal-0.5.8.1-35.el5 conflicts with file from package hal-0.5.8.1-25.el5
  file /usr/share/hal/fdi/policy/10osvendor/10-power-mgmt-policy.fdi from install of hal-0.5.8.1-35.el5 conflicts with file from package hal-0.5.8.1-25.el5

사실 전에 설치할 때는 이런 에러가 없었는데, 에러가 나서 다음 두 가지 프로그램을 더 설치하도록 하겠습니다.

# yum -y install fontconfig

# yum -y install hal

그리고 다시 한번 반복해줍니다.

# yum -y install ImageMagick-devel

# make
# make install

Installing shared extensions:     /usr/local/php/lib/php/extensions/no-debug-non-zts-20060613/

이렇게 해주면 정상적으로 처리가 됩니다.

그런 다음 확장자를 php.ini에 등록시켜 줘야 합니다.
다음 사항을 수정시켜줍니다. 또는 추가해줍니다.  

; Directory in which the loadable extensions (modules) reside.
;extension_dir = "./"
extension_dir = "/usr/local/php/lib/php/extensions"
enable_dl = On
extension="no-debug-non-zts-20060613/imagick.so" 

아파치 재시작해줘야 합니다.

그리고 한가지 더 phpinfo를 보게 되면 다음과 같은 옵션이 보여야 합니다.

출처 : 한빛 네트워크