Recipe 10.3. Connecting to an SQL Database

Recipe 10.3. Connecting to an SQL Database 10.3.1. Problem You want access to a SQL database to store or retrieve information. Without a database, dynamic web sites aren't very dynamic. 10.3.2. Solution Create a new PDO object with the appropriate connection string. Example 10-8 shows PDO object creation for a few different kinds of databases. Connecting with PDO <?php // MySQL expects parameters in the string $mysql = new PDO('mysql:host=db.example.com', $user, $password); // Separate multiple parameters with ; $mysql = new PDO('mysql:host=db.example.com;port=31075', $user, $password) $mysql = new PDO('mysql:host=db.example.com;port=31075;dbname=food', $user, $password) // Connect to a local MySQL Server $mysql = new PDO('mysql:unix_socket=/tmp/mysql.sock', $user, $password) // PostgreSQL also expects parameters in the string $pgsql = new PDO('pgsql:host=db.example.com', $user, $password); // But you separate multiple parameters with ' ' $pgsql = new PDO('pgsql:host=db.example.com port=31075', $user, $password) $pgsql = new PDO('pgsql:host=db.example.com port=31075 dbname=food', $user, $password) // You can put the user and password in the DSN if you like. $pgsql = new PDO("pgsql:host=db.example.com port=31075 dbname=food user=$user password =$password"); // Oracle // If a database name is defined in tnsnames.ora, just put that in the DSN $oci = new PDO('oci:food', $user, $password) // Otherwise, specify an Instant Client URI $oci = new PDO('oci:dbname=//db.example.com:1521/food', $user, $password) // Sybase (If PDO is using FreeTDS) $sybase = new PDO('sybase:host=db.example.com;dbname=food', $user, $password) // Microsoft SQL Server (If PDO is using MS SQL Server libraries) $mssql = new PDO('mssql:host=db.example.com;dbname=food', $user, $password); // DBLib (for other versions of DB-lib) $dblib = new PDO('dblib:host=db.example.com;dbname=food', $user, $password); // ODBC -- a predefined connection $odbc = new PDO('odbc:DSN=food'); // ODBC -- an ad-hoc connection. Provide whatever the underlying driver needs $odbc = new PDO('odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ= C:\\data\\food.mdb;Uid=Chef'); // SQLite just expects a filename -- no user or password $sqlite = new PDO('sqlite:/usr/local/zodiac.db'); $sqlite = new PDO('sqlite:c:/data/zodiac.db'); // SQLite can also handle in-memory, temporary databases $sqlite = new PDO('sqlite::memory:'); // SQLite v2 DSNs look similar to v3 $sqlite2 = new PDO('sqlite2:/usr/local/old-zodiac.db'); ?> 10.3.3. Discussion If all goes well, the PDO constructor returns a new object that can be used for querying the database. If there's a problem, a PDOException is thrown. As you can see from Example 10-8, the format of the DSN is highly dependent on which kind of database you're attempting to connect to. In general, though, the first argument to the PDO constructor is a string that describes the location and name of the database you want and the second and third arguments are the username and password to connect to the database with. Note that to use a particular PDO backend, PHP must be built with support for that backend. Use the output from phpinfo( ) to determine what PDO backends your PHP setup has. 10.3.4. See Also Recipe 10.6 for querying an SQL database; Recipe 10.6 for modifying an SQL database; documentation on PDO at http://www.php.net/PDO.