Using PHP and connect to MS SQL?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mapperkids
Forum Newbie
Posts: 15
Joined: Mon Feb 11, 2008 8:53 pm

Using PHP and connect to MS SQL?

Post by mapperkids »

Hi,

We have few application that is written in PHP w/MySQL database, but now we want to use the same PHP but connect to MS SQL server and IIS website with PHP add on. instead of MySQL/Unix, other than convert the db itself, what is the code changes involve in PHP side.

I'm expected just change the connection statement to point to MS SQL server and remain all the tables / fields names the same then will be off to go.

Anyone try that kind of project before and give me some idea?
toraj58
Forum Newbie
Posts: 12
Joined: Sun Jun 15, 2008 12:40 am

Re: Using PHP and connect to MS SQL?

Post by toraj58 »

try PEAR DB.

i have an example here for you and then benefits of PEAR DB.

Code: Select all

 
<?php
$host = 'localhost';
$uname = 'bp5am';
$passwd = 'bp5ampass';
$db_name = 'comicsite';
$db_type = 'mysql';
require_once('DB.php');
$dsn = "$db_type://$uname:$passwd@$host/$db_name";
$conn = DB::connect($dsn);
if (DB::isError($conn)) {
die ("Unable to connect: " . $conn->getMessage() . "\n");
}
$sql = "SELECT * FROM superhero s, league l WHERE l.id = s.league_id";
$result = $conn->query($sql);
if (DB::isError($result) {
die ("Query ($sql) failed: " . $result->getMessage() . "\n");
}
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo $row['alias'] . " (" . $row['real_name'] . ") is a member of "; 
echo "the " . $row['league'] . " League.<br>";
}
?>
 
The PEAR DB method takes more lines, and the syntax is a little different (which can be especially diffi-
cult if you are not familiar with objects). So why should you bother to use PEAR DB?
Remember that PEAR DB acts as an abstraction layer to help you interface with your database. If you
look closely at the PEAR DB code, you’ll notice that none of the functions refer to the type of database
you are accessing. In fact, they are quite generic.

Now you should see the benefits of using PEAR DB’s abstraction layer. In fact, by keeping the $db_type
variable (or an SQL_DB_TYPE) constant in an included file, all you should have to do to make your entire
Web site work with the new database is change the $db_type variable from mysql to oci8—theoreti-
cally, of course. This assumes that every SQL statement you used is compatible with both MySQL and
Oracle (which is doubtful) and that every PEAR DB function you used for MySQL is compatible with
Oracle (also doubtful). But at least you will have a lot less work to do.

There are definite benefits to using PEAR DB. For example:
? It enables you to easily change your code to work with any database backend supported by
PEAR.
? It provides built-in error checking and error handling.
? It creates defaults for common methods (such as fetchRow()).
? It offers extended functionality—PEAR DB offers database functions that PHP does not provide
natively. For example, a method (DB::isManip) tells you whether or not a SQL statement
manipulates data or simply retrieves it. PHP has no equivalent function.

There are caveats to using PEAR DB as well:
? You must use SQL compatible with other databases or change your SQL statements when you
change DB servers. You would have to do that without PEAR DB, of course, but you should
know that PEAR DB does not solve this problem.
? PEAR DB is not as efficient as native PHP code, because you are running things in an abstraction
layer, and there may be some very complicated things going on “under the hood.”
? Not all database-specific functions are available through PEAR DB. For example, MySQL’s func-
tion mysql_fetch_object has no PEAR DB equivalent. Workarounds exist for some functions
(such as casting the results of a fetchRow to an object); of course, that defeats the purpose of
having an abstraction layer.
So, should you install PEAR and use PEAR DB? Perhaps you should. You need to weigh the benefits
against potential problems and determine if it’s right for you. In the meantime, if you are working on a
big PHP project, make sure you are absolutely sure what database you will be accessing. You can avoid
a lot of headaches in the future (your IT Manager will thank you for that).

Touraj Ebrahimi [toraj_e] [at] [yahoo] [dot] [com]
Post Reply