PHP Database Interaction (mysql_*(), mysqli_*(), MDB2)

Database interaction in PHP is generally performed with one of:

  • Database-specific interface functions (e.g. mysql_*(), pgsql_*(), mysqli_*())
  • PEAR MDB (supercedes PEAR DB) (abstracted, DB-independent - recommended).

I strongly recommend MDB, to write portable code which can be used with different DBMSs with minimal effort.

mysql_*() functions

The 'classic' and most popular are the mysql_*() functions (documented fully at http:uk.php.net/mysql). The common ones are: * mysql_connect(string server, string username, string password) * mysql_query(string query [, resource link_identifier]) * mysql_fetch_assoc(resource result) A quick overview would be: <code php> $dbh = mysql_connect('localhost', 'user', 'pass'); $sth = mysql_query('SELECT * FROM mysql.user', $dbh); while ($row = mysql_fetch_assoc($sth)) { print “Found user: {$row['User']}\n”; } </code> ==== mysqli_*() functions ==== The mysqli extension (“MySQL Improved”) is for MySQL > 4.1, and was introduced in PHP 5, compiled with the –with-mysqli option. Full documentation is at http:uk.php.net/mysqli

It can be used with a procedural interface in a manner similar to the classic mysql_*() functions, or with a new object-oriented interface:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
 
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
   printf("Select returned %d rows.\n", $result->num_rows);
 
   while ($row = $result->fetch_assoc()) {
       printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
   }
}

PEAR MDB2

The MDB2 Database Abstraction Layer from PEAR provides a nice, abstracted method to deal with the database. I recommend it, since it allows you to write portable code which can be ported to a different database server without many changes required (for a basic script, just changing the connect call is likely to be sufficient). It is a merge of the old DB and Metabase abstraction layers (DB is still supported for bugfixes, but has been superceded by MDB2).

It provides:

  • an OO-style query API
  • portability features that make programs written for one DBMS work with other DBMS's
  • a DSN (data source name) format for specifying database servers
  • prepare/execute (bind) emulation for databases that don't support it natively
  • a result object for each query response
  • portable error codes
  • formats fetched: rows as associative arrays, ordered arrays or objects
  • transactions support
  • table information interface

It can be used to interact with:

  • mysql / mysqli (whichever extension is installed)
  • pgsql (PostgreSQL)
  • oci8 (Oracle)
  • sqllite
  • msql
  • mssql (Microsoft SQL Server)
  • sybase
  • informix
  • fbsql
  • ibase
  • odbc

MDB2 is basically PHP's equivalent of Perl's DBI CPAN module.

It is documented in detail at:

* http:pear.php.net/manual/en/package.database.mdb2.php However, here is a brief overview: === Connecting === <code php> require_once 'MDB2.php'; $host = 'localhost'; $type = 'mysql'; $db_name = 'my_database'; $dsn = “$type:$user:$pass@$host/$db_name”; $dbh =& MDB2::connect($dsn);

or, more quickly: $dbh =& MDB2::connect('mysql:usr:pw@localhost/dbname');

check the result isn't an error: if (PEAR::isError($dbh)) { die($dbh→getMessage()); } </code> === Performing quick queries with query() or exec() === <code php> perform a query: $res =& $dbh→query('SELECT * FROM mysql.user');

check the result isn't an error: if (PEAR::isError($res)) { die($res→getMessage()); } use exec() for manipulation queries (i.e. those which do not return rows - e.g. INSERT, UPDATE, DELETE, ALTER) $res =~ $dbh→exec('INSERT INTO ....'); Again, check for an error if (PEAR::isError($res)) {

  die($res->getMessage());

} </code>

Fetching results

You can fetch results of a query with:

  • fetchRow() [fetch an entire row]
  • fetchOne() [fetch a single field from the row]
  • fetchAll() [fetch all rows in the result set]
  • fetchCol() [read a single column from all rows in result set]

Each one can take a fetch mode, which is one of the following constants:

  • MDB2_FETCHMODE_ORDERED [default] - An ordered array, with column numbers as keys
  • MDB2_FETCHMODE_ASSOC - an associative array (column names as keys)
  • MDB2_FETCHMODE_OBJECT - an object (column names as properties)

It's highly recommended to use MDB2_FETCHMODE_ASSOC or MDB2_FETCHMODE_OBJECT for code clarity.

You can set the fetch mode each time you call a fetch method and/or you can set the default fetch mode for the whole MDB2 instance by using the setFetchMode() method.

while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    print $row['field'];
}
 
// or, since we don't want to have to use the ugly MDB2_FETCHMODE_ASSOC on
// every fetch*() call, we use setFetchMode() (preferably just after
// connecting, as that's a clear + logical place to do it:
$dbh->setFetchMode(MDB2_FETCHMODE_ASSOC);
 
// then we can just use:
while ($row = $res->fetchRow()) {
    print $row['field'];
}

Prepare and Execute statements

prepare() and execute() is very helpful if you need to run the same query several times, but with different values in it (for example, inserting lots of rows into a DB one by one).

The initial prepare() allows the database engine to analyse the query and work out the execution plan only once, and re-use that information each time the query is called.

The ability to use placeholders is also useful to help avoid SQL injection attacks, as the values are automatically quoted.

prepare() can handle different types of placeholders. By default all placeholders are handled as strings. However passing an array of data types as the second parameter makes it possible to set a specific type for each placeholder.

Since DML (data manipulation language - INSERT, UPDATE, DELETE) statements have different return values than data fetches the prepare() accepts a third parameter. This parameter should be set to MDB2_PREPARE_MANIP for DML statements. For data reads it should either be set to MDB2_PREPARE_RESULT, an array of data types for each of the columns in the result set or TRUE in order to automatically detect the data types in the result set.

After preparing the statement, you can execute the query. This means to assign the variables to the prepared statement. To do this, execute() requires one argument a scalar or array with the values to assign.

$sth = $dbh->prepare('INSERT INTO numbers (number) VALUES (?)', 
    array('integer'), MDB2_PREPARE_MANIP);
$sth->execute(1);
$sth->execute(8);

When a prepared statement has multiple placeholders, you must use an array to pass the values to execute(). The first entry of the array represents the first placeholder, the second the second placeholder, etc. The order is independent of the type of placeholder used.

$types = array('integer', 'text', 'text');
$sth = $dbh->prepare('INSERT INTO numbers VALUES (?, ?, ?)', 
    $types, MDB2_PREPARE_MANIP);
 
$data = array(1, 'one', 'en');
$affectedRows = $sth->execute($data);

When using named placeholders the data array needs to be an associative array with the keys matching the placeholder names.

$types = array('integer', 'text', 'text');
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (:id, :text, :lang)');
 
$data = array('id' => 1, 'name' => 'one', 'lang' => 'en');
$affectedRows = $sth->execute($data);

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement. Similarly, identifiers (i.e. table names and column names) can not be used because the names get validated during the prepare phase.

Freeing results

When you're done with a result set, it can be freed with free().

~~DISCUSSION~~

 
php/database.txt · Last modified: 2010/02/26 10:45 (external edit)
 
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki