MDBitz Development Resources

PHP Database Objects PDOs for short is a light-weight data abstraction layer for PHP. What this means is that it allows you to use an interface for performing data manipulations instead of using db specify functions such as mysql_query. So that if you needed to migrate to a different database you would not have to rewrite your code instead you simply change the connection driver. In addition to portability of the code you also protect against SQL-Injection as the prepare method will call the underlying quote method of the specific driver so that your input gets escaped properly.

Connecting to a database with PDO

To connect to a database you need to instantiate the PDO object. The constructor espects 3 parameters: connection string, username, and password. The connection string specifies the driver, host, and optional database or schema to connect to and has the following format: driver:host=hostname:port;dbname=database

$pdo = new PDO("mysql:host=localhost;dbname=mysql", "username", "password");

When establishing a connection if there is an error connecting an exception will be thrown. In this thrown exception the stack trace will reveal the username and password used to access the server. It is very important that you capture these exceptions and either throw an excpetion that does not share this information or define your own handlers. Below is a full example for connection to a databse with PDO and handling the Exception if the connection fails.

$driver = "mysql";
$host_name = 'localhost';
$user_name = 'root';
$password = 'root';
$db_name = 'MY_DB';
 
try {
    $db = new PDO("$driver:host=$host_name;dbname=$db_name", $user_name, $password);
    echo 'Connected to database';
}
catch(PDOException $e) {
    echo $e->getMessage();
}

Preparing and Executing a Query

There are 2 main ways to prepare queries when using PDOs. The first method is to define your sql with ? placeholders for variables.

$sql = 'SELECT name, phone_num, active
    FROM Users
    WHERE name like ?';
$statement = $pdo>prepare( $sql );

The second option is define the query with named variables by use of the : character. In the below query we defined the named variable :name.

$sql = 'SELECT name, phone_num, active
    FROM Users
    WHERE name like :name';
$statement = $pdo>prepare( $sql );

The PDOStatement object is returned from the prepare function. The next step will be to execute the statements with your variables. If you used the ? method you simply pass an array of variables.

$statement->execute(array('A%'));

While if you used the named parameter way you will pass in an assocaited array of the variables

$statement->execute( array( ":name" => "A%" ) );

Now that you have executed the query me can fetch the results in various different methods.

Fetching and Using the Result

Now that you have executed your query you want your results right? To do this you utilize either the fetch or fetchAll method. Which will return the next result row or an array of all the rows. You can even specify how you want the data returned as: FETCH_ASSOC returns an associated array by the column name and FETCH_BOTH returns it by both the index of the column and the name.

$result = $sth->fetch(PDO::FETCH_ASSOC);

$result = $sth->fetchAll(PDO::FETCH_BOTH);

Now that you have the data simply use it however you need in your application. If you returned it as an associated array you can simply get the property.

echo $result->name;

Closing your Connection

To close your connection you simply need to set all your references to the PDO object to null. If you fail to close the connection it will be closed automatically upon the completion of the script. Good practice is to close it once you are finished.

$pdo = null;

Resources

• PHP.net – PHP Data Objects
FETCH, fetchAll, MySQL, PDO, PDOStatement, PHP, SQL, SQL Injection

When developing a new system or software you will often find yourself unsure on the proper naming schema to utilize, or you may find out you need to comply with a company’s standard nomenclature. When this happens you may want to take note of the sp_rename stored procedure available in Sybase. This procedure allows you to rename a table, column, or index object be forewarned though that renaming a table can break stored procedures or queries that utilize the table and they will need to be updated appropriately.

Renaming a Database Table with sp_rename

To rename a table all you need to do is execute the procedure passing the name of the table to be modified and the new name for the table.

EXEC SP_RENAME MY_TABLE_OLD, MY_TABLE_NEW

Renaming a Table Column with sp_rename

To rename a column you need to specify the table and column in the first parameter and then in the 2nd parameter only put the new name of the column.

EXEC SP_RENAME MY_TABLE_NEW.old_id, new_id

Renaming a Table Index with sp_rename

To rename an index you need to specify the table and index in the first parameter and then in the 2nd parameter only put the new name of the index.

EXEC SP_RENAME MY_TABLE_NEW.id_idx, new_id_idx

It is important to note that if you have a column and index with the same name for a table you can specify a third parameter saying that you want to rename the index or column

EXEC SP_RENAME MY_TABLE_NEW.client_id, new_client_id, "index"

Resources

• Sybase Online : sp_rename function reference

How many times have you found yourself defining multiple find or search methods for obtaining data in your Web Applications. For example for a Users table you may have a findByName, findById, FindByEmail, and etc. Utilizing the iBATIS framework and SQL Maps we can have all these sql statements condensed into a single dynamic select statement.

To illustrate how this would be completed lets define our User table as the following:

Traditionally to create the above find methods you would create a sql statement for each query. Using iBATIS’s Dynamic SQl we are able to add them all into one statement by use of the dynamic element. To help illustrate lets start with a basic select SQL Map.

<select id="User.find" parameterClass="com.mdbitz.model.User" resultMap="userMap">
    SELECT * 
    FROM USERS
    WHERE 
        FIRST_NAME LIKE #firstName:VARCHAR#
        AND LAST_NAME LIKE #lastName:VARCHAR#
</select>

Using this simple map as a base we could extends it by adding a dynamic clause that only adds the where clauses if a property is set in the parameter object. To do this we would use the isNotNull element that outputs its content only if a property is not null. An updated dynamic map would look like:

<select id="User.find" parameterClass="com.mdbitz.model.User" resultMap="userMap">
    SELECT * 
    FROM USERS
    <dynamic prepend="WHERE">
        <isNotNull property="firstName" prepend="AND">
        FIRST_NAME LIKE #firstName:VARCHAR#
        </isNotNull>
        <isNotNull property="lastName" prepend="AND">
        LAST_NAME LIKE #lastName:VARCHAR#
        </isNotNull>
    </dynamic>
</select>

Using these dynamic statements you could extend this to do any type of find by that you need. You could query by name, phone number, email, id, or any combination of them. In addition to the property you could also use isNull, isEmpty, isNotEmpty, isGreaterThan, IsGreaterEqual, and etc. Instead of dozens of SQL Map statements for each desired search or query. Dynamic SQL allows you to condense your sql query into simpler more robust statements.

Resources

• iBATIS – official site

As a certain project at work evolves I find that more and more of my time is spent on optimizing the T-SQL queries that are utilized. Recently I have come across the issue that an index was not getting utilized in a sql query when I was using the UPPER or LOWER function to do an ignore case comparison. As it turns out that using a function on a column causes a table scan and will not use the index ever.

To resolve this issue we fell back on regular expressions as T-SQL does not support the ILIKE function. What this means is that we can use regular expression sets for each character in our String. To illustrate lets look at the following basic SQL string.

SELECT * FROM USERS WHERE UPPER(LAST_NAME) LIKE "DENTON%"

If our table had an index on the LAST_NAME column then it would be ignored. To use our index we would have to rewrite the query to be:

SELECT * FROM USERS WHERE LAST_NAME LIKE "[dD][eE][nN][tT][oO][nN]%"

This may seem odd, but running this second query will utilize the column index. Another item to note is that you can only use the regular expressions in LIKE clauses and not = clauses.

Resources

• Sybase Manual • Matching character strings: like

The NOEXEC option of SQL is used to specify that the SQL statements are not to be executed. Utilizing this command with the SHOWPLAN option enables us to quickly see the query plans for a SQL statement without having the query actually execute. This gives us a quick output of the steps the DB Server uses to execute the queries and if table scans or indexes are used.

Proper Usage

SET SHOWPLAN ON
SET NOEXEC ON
go
 
SELECT ID, F_NAME, L_NAME 
FROM USERS u WHERE u.GENDER = "F"
go