Tag: PDO

PHP PDO Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

PARAM

PDO::PARAM_BOOL (integer) – Represents a boolean data type.
PDO::PARAM_NULL (integer) – Represents the SQL NULL data type.
PDO::PARAM_INT (integer) – Represents the SQL INTEGER data type.
PDO::PARAM_STR (integer) – Represents the SQL CHAR, VARCHAR, or other string data type.
PDO::PARAM_LOB (integer) – Represents the SQL large object data type.
PDO::PARAM_STMT (integer) – Represents a recordset type. Not currently supported by any drivers.
PDO::PARAM_INPUT_OUTPUT (integer) – Specifies that the parameter is an INOUT parameter for a stored procedure. You must bitwise-OR this value with an explicit PDO::PARAM_* data type.

FETCH

PDO::FETCH_LAZY (integer) – Specifies that the fetch method shall return each row as an object with variable names that correspond to the column names returned in the result set.
PDO::FETCH_LAZY – creates the object variable names as they are accessed. Not valid inside PDOStatement::fetchAll().
PDO::FETCH_ASSOC (integer) – Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set. If the result set contains multiple columns with the same name, PDO::FETCH_ASSOC returns only a single value per column name.
PDO::FETCH_NAMED (integer) – Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set. If the result set contains multiple columns with the same name, PDO::FETCH_NAMED returns an array of values per column name.
PDO::FETCH_NUM (integer) – Specifies that the fetch method shall return each row as an array indexed by column number as returned in the corresponding result set, starting at column 0.
PDO::FETCH_BOTH (integer) – Specifies that the fetch method shall return each row as an array indexed by both column name and number as returned in the corresponding result set, starting at column 0.
PDO::FETCH_OBJ (integer) – Specifies that the fetch method shall return each row as an object with property names that correspond to the column names returned in the result set.
PDO::FETCH_BOUND (integer) – Specifies that the fetch method shall return TRUE and assign the values of the columns in the result set to the PHP variables to which they were bound with the PDOStatement::bindParam() or PDOStatement::bindColumn() methods.
PDO::FETCH_COLUMN (integer) – Specifies that the fetch method shall return only a single requested column from the next row in the result set.
PDO::FETCH_CLASS (integer) – Specifies that the fetch method shall return a new instance of the requested class, mapping the columns to named properties in the class. Note: The magic __set() method is called if the property doesn’t exist in the requested class
PDO::FETCH_INTO (integer) – Specifies that the fetch method shall update an existing instance of the requested class, mapping the columns to named properties in the class.
PDO::FETCH_FUNC (integer) – Allows completely customize the way data is treated on the fly (only valid inside PDOStatement::fetchAll()).
PDO::FETCH_GROUP (integer) – Group return by values. Usually combined with PDO::FETCH_COLUMN or PDO::FETCH_KEY_PAIR.
PDO::FETCH_UNIQUE (integer) – Fetch only the unique values.
PDO::FETCH_KEY_PAIR (integer) – Fetch a two-column result into an array where the first column is a key and the second column is the value. Available since PHP 5.2.3.
PDO::FETCH_CLASSTYPE (integer) – Determine the class name from the value of first column.
PDO::FETCH_SERIALIZE (integer) – As PDO::FETCH_INTO but object is provided as a serialized string. Available since PHP 5.1.0. Since PHP 5.3.0 the class constructor is never called if this flag is set.
PDO::FETCH_PROPS_LATE (integer) – Call the constructor before setting properties. Available since PHP 5.2.0.

ATTR

PDO::ATTR_AUTOCOMMIT (integer) – If this value is FALSE, PDO attempts to disable autocommit so that the connection begins a transaction.
PDO::ATTR_PREFETCH (integer) – Setting the prefetch size allows you to balance speed against memory usage for your application. Not all database/driver combinations support setting of the prefetch size. A larger prefetch size results in increased performance at the cost of higher memory usage.
PDO::ATTR_TIMEOUT (integer) – Sets the timeout value in seconds for communications with the database.
PDO::ATTR_ERRMODE (integer) – See the Errors and error handling section for more information about this attribute.
PDO::ATTR_SERVER_VERSION (integer) – This is a read only attribute; it will return information about the version of the database server to which PDO is connected.
PDO::ATTR_CLIENT_VERSION (integer) – This is a read only attribute; it will return information about the version of the client libraries that the PDO driver is using.
PDO::ATTR_SERVER_INFO (integer) – This is a read only attribute; it will return some meta information about the database server to which PDO is connected.
PDO::ATTR_CONNECTION_STATUS (integer)
PDO::ATTR_CASE (integer) – Force column names to a specific case specified by the PDO::CASE_* constants.
PDO::ATTR_CURSOR_NAME (integer) – Get or set the name to use for a cursor. Most useful when using scrollable cursors and positioned updates.
PDO::ATTR_CURSOR (integer) – Selects the cursor type. PDO currently supports either PDO::CURSOR_FWDONLY and PDO::CURSOR_SCROLL. Stick withPDO::CURSOR_FWDONLY unless you know that you need a scrollable cursor.
PDO::ATTR_DRIVER_NAME (string) – Returns the name of the driver.
PDO::ATTR_ORACLE_NULLS (integer) – Convert empty strings to SQL NULL values on data fetches.
PDO::ATTR_PERSISTENT (integer) – Request a persistent connection, rather than creating a new connection. See Connections and Connection management for more information on this attribute.
PDO::ATTR_STATEMENT_CLASS (integer)
PDO::ATTR_FETCH_CATALOG_NAMES (integer) – Prepend the containing catalog name to each column name returned in the result set. The catalog name and column name are separated by a decimal (.) character. Support of this attribute is at the driver level; it may not be supported by your driver.
PDO::ATTR_FETCH_TABLE_NAMES (integer) – Prepend the containing table name to each column name returned in the result set. The table name and column name are separated by a decimal (.) character. Support of this attribute is at the driver level; it may not be supported by your driver.
PDO::ATTR_STRINGIFY_FETCHES (integer)
PDO::ATTR_MAX_COLUMN_LEN (integer)
PDO::ATTR_DEFAULT_FETCH_MODE (integer) – Available since PHP 5.2.0
PDO::ATTR_EMULATE_PREPARES (integer) – Available since PHP 5.1.3.

ERRMODE

PDO::ERRMODE_SILENT (integer) – Do not raise an error or exception if an error occurs. The developer is expected to explicitly check for errors. This is the default mode. See Errors and error handling for more information about this attribute.
PDO::ERRMODE_WARNING (integer) – Issue a PHP E_WARNING message if an error occurs. See Errors and error handling for more information about this attribute.
PDO::ERRMODE_EXCEPTION (integer) – Throw a PDOException if an error occurs. See Errors and error handling for more information about this attribute.
PDO::ERR_NONE (string) – Corresponds to SQLSTATE ‘00000’, meaning that the SQL statement was successfully issued with no errors or warnings. This constant is for your convenience when checking PDO::errorCode() or PDOStatement::errorCode() to determine if an error occurred. You will usually know if this is the case by examining the return code from the method that raised the error condition anyway.

CASE

PDO::CASE_NATURAL (integer) – Leave column names as returned by the database driver.
PDO::CASE_LOWER (integer) – Force column names to lower case.
PDO::CASE_UPPER (integer) – Force column names to upper case.

Sumber: PHP.Net

Jom PDO

Memandangkan sekarang kita sudah mula menggunakan PHP dan kaedah PDO untuk berinteraksi dengan pengkalan data mySQL serta ada kawan-kawan yang bertanya mengenai PDO, jadi saya akan buat satu tutorial ringkas procedural PHP dan PDO untuk pengkalan data mySQL. Sebelum itu untum rujukan mengenai PDO, sila rujuk artikel MySQLi vs PDO.

Contoh kod sambungan ke pengkalan data:

<?php
try {
$conn = new PDO(‘mysql:host=localhost;dbname=datasaya’, “root”, “abc123”);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
}
catch(PDOException $e) {
die(‘ERROR: ‘ . $e->getMessage());
}
?>

Code di atas adalah untuk membuat sambungan ke pengkalan data mySQL dengan datasaya adalah nama pengkalan data, root adalah pengguna pengkalan data dan abc123 adalah katalaluan pengguna pengkalan data.

Contoh kod untuk tambah data:

<?php
$s = “INSERT INTO pengguna SET nama = :nama, katalaluan = :pass”;
$q = $conn->prepare($s);
$q->execute(array(
‘nama’ => $_POST[‘nama’],
‘pass’ => $_POST[‘pass’]
));
?>

Kod di atas akan menambah nilai yang dibawa melalui kaedah $_POST iaitu nama dan pass ke dalam pengkalan data.

Contoh kod untuk memilih data:

<?php
$s = “SELECT * FROM pengguna WHERE nama LIKE :nama”;
$q = $conn->prepare($s);
$q->execute(array(
‘nama’ => ‘%’.$_POST[‘nama’].’%’
));
?>

Kod di atas akan memilih data dimana field nama pengkalan datanya adalah yang berpadan dengan nilai yang dihantar melalui kaedah $_POST tersebut

Contoh kod untuk mengubah data:

<?php
$s = “UPDATE pengguna SET nama = :nama WHERE id = :id”;
$q = $conn->prepare($s);
$q->execute(array(
‘nama’ => $_POST[‘nama’],
‘id’ => $_POST[‘id’]
));
?>

Kod ini pula adalah untuk mengemaskini maklumat data kepada nilai nama yang baru untuk pengguna id berkenaan

Contoh kod untuk menghapus data:

<?php
$s = “DELETE FROM pengguna WHERE id = :id”;
$q = $conn->prepare($s);
$q->execute(array(
‘id’ => $_POST[‘id’]
));
?>

Dan kod ini pula adalah untuk menghapus data bagi id pengguna berkenaan

Jadi setakat ini dulu asas kepada pengaturcaraan PHP menggunakan kaedah PDO untuk berinteraksi dengan mySQL.

MySQLi vs PDO

Seperti semua sedia maklum, versi PHP terkini adalah 5.5.7. Antara isu yang dibangkitkan oleh segelintir programmer adalah fungsi mysql_* yang telah dideprecated mulai PHP 5.5.0. Jadi walaupun kita masih menggunakan server yang menggunakan versi PHP sebelum 5.5.0, adalah disarankan agar kita menukar fungsi mysql_* kita kepada fungsi MySQLi atau PDO.

Kedua-dua fungsi ini mempunyai persamaan dan perbezaan yang tidak terlalu ketara. Dengan adanya fungsi-fungsi ini, konsep prepared statements, multiple statements dan transactions dapat digunakan dengan lebih mudah dan berkesan. Selain itu, pada fungsi mysql_*, fungsi mysql_real_escape_string dan concatenating biasanya ditentukan oleh programmer itu sendiri. Ini adalah amat berbahaya kepada sistem yang hendak dibangunkan kerana mudah terdedah kepada SQL Injection.


MySQLi

MySQLi Extension (mysql improved) membolehkan kita mengakses fungsi yang terdapat pada MySQL versi 4.1.13 ke atas. MySQLi adalah versi yang lebih baik berbanding MySQL sebelumnya. Terdapat banyak fungsi baru yang ada boleh kita gunakan.

MySQLi biasanya menggunakan OOP (Object-Oriented Programming) tetapi kita masih boleh menggunakan procedural programming.

Contoh OOP dalam MySQLi:

$conn = new mysqli(“localhost”, “root”, “”, “database”);
if ($conn->connect_error) {
die(‘Database connection failed: ‘.$conn->connect_error, E_USER_ERROR);
}

Contoh Procedural API:

$conn = mysqli_connect(“localhost”, “root”, “”, “database”);
if (mysqli_connect_errno()) {
die(‘Database connection failed: ‘.mysqli_connect_error(), E_USER_ERROR);
}

Sumber:
+ W3Schools
+ Pontikis.net


PDO

Selain MySQLi, PDO (PHP Data Object) juga adalah satu lagi pilihan yang adalah untuk berinteraksi dengan MySQL. Berbanding dengan MySQLi, PDO hanya ditulis menggunakan OOP. Kod-kod yang ditulis menggunakan PDO biasanya lebih selamat dan kemas.

Berbeza dengan MySQLi yang hanya boleh berinterasi dengan MySQL, PDO boleh berinteraksi dengan banya database. Selain daripada MySQL, PDO boleh berinteraksi dengan IBM, Informix, ODBC, PostgreSQL, SQLite, Oracle dan sebagainya.

PDO juga mempunyai banyak kaedah error handling. Dalam PDO, exception mode membolehkan anda mengetahui ralat (error) dengan lebih konsisten dan ini membantu anda untuk menangani isu-isu tadi dengan lebih cepat dan mudah.

Contoh PDO:

$conn = new PDO(‘mysql:host=localhost;dbname=database;charset=utf8’, ‘root’, ”);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Sumber:
+ HashPHP.Org
+ TutPlus.Com


Apakah Prepared Statements dan Kenapa Ia Penting?

Prepared statements adalah fungsi yang lazim digunakan pada MySQLi dan PDO. Andaikan kita mempunyai satu string yang mempunyai simbol ‘ (single quote). Cthnya, “Pak Mat’s House”. Di dalam MySQL, kita perlu menggunakan fungsi real_escape_string(). Tetapi menggunakan MySQLi atau PDO, kita hanya perlu gunakan fungsi prepare(). Kebiasannya, melalui fungsi ini sql statement datatype akan ditentukan melalui fungsi bind_param (MySQLi) atau bindParam (PDO) sebelum di proses (melalui fungsi excute()).

Contoh MySQLi:

$sql = ‘SELECT field FROM table WHERE field = ? ‘;
$data = ‘Pak Mat’ House’;
$stmt = $conn->prepare($sql);
/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */
$stmt->bind_param(‘s’,$data);
$stmt->execute();

Contoh PDO:

$sql = ‘SELECT field FROM table WHERE field = :field’;
$data = ‘Pak Mat’ House’;
$stmt = $conn->prepare($sql);
$stmt->bindParam(‘:field’, $data, PDO::PARAM_STR);
$stmt->execute();

Jadi kita perhatikan disini, user input akan difilter terlebih dahulu sebelum diproses. Ini akan menjadikan query kita lebih selamat. Fungsi di atas juga amat efektif (performance increase) sekiranya kita membuat sql statement berulang-ulang kali.

Kedua-duanya mempunyai kelebihan dan kekurangan sendiri. Ada juga pertanyaan manakah yang lebih bagus. Saya menjawab bergantung kepada citarasa masing-masing yang mana lebih anda suka :). Tapi sekiranya ada kemungkinan aplikasi itu menggunakan multiple database (cth MySQL, Oracle, IBM), jadi PDO adalah pilihan paling tepat. Saya sendiri menyarankan menggunakan PDO meskipun anda hanya menggunakan MySQL.