CertCities.com  -- The Ultimate Site for Certified IT Professionals
Post Your Mind in the CertCities.com Forums Share share | bookmark | e-mail
 Microsoft®
 Cisco®
 Security
 Oracle®
 A+/Network+™
 Linux/Unix
 More Certs
 Newsletters
 Salary Surveys
 Forums
 News
 Exam Reviews
 Tips
 Columns
 Features
 PopQuiz
 RSS Feeds
 Press Releases
 Contributors
 About Us
 Search
 

Advanced Search
 Free Newsletter
  Sign-up for the #1 Weekly IT
Certification News
and Advice.
Subscribe to CertCities.com Free Weekly E-mail Newsletter
CertCities.com

See What's New on
Redmondmag.com!

Cover Story: IE8: Behind the 8 Ball

Tech-Ed: Let's (Third) Party!

A Secure Leap into the Cloud

Windows Mobile's New Moves

SQL Speed Secrets


CertCities.com
Let us know what you
think! E-mail us at:
ccfeedback@certcities.com


 
 
...Home ... Editorial ... Columns ..Column Story Friday: April 18, 2014


 Notes from Underground  
James      Ervin
James Ervin


  • PRINTABLE FORMAT
  • E-MAIL STORY
  • POST YOUR COMMENTS
  • MORE COLUMNS
  •  Introductory Database Access with PHP
    This tutorial walks you through using PHP with the various vendor extensions of SQL, including Microsoft SQL Server 2000.
    by James Ervin  
    9/24/2003 -- One of PHP’s strongest features as a scripting language for Web applications is its diverse database support: the “What Can PHP Do?” page lists over 20 database platforms that PHP can communicate with. The PHP-MySQL combination powers numerous high-profile Web sites worldwide. However, accessing other database platforms with PHP is occasionally frustrating, since the PHP documentation falters in less traveled areas, leaving it to the extensive user community to fill in the gaps.

    In this article, we’ll introduce PHP’s database connectivity capabilities, access a Microsoft SQL Server 2000 database from Linux using PHP and some freely available utilities as a test case, and run a basic database query.

    Building PHP
    PHP can manipulate file-based databases, including the Berkeley Database, the GNU Database Manager, and cdb (constant database) formats. These are supported by PHP’s database abstraction layer (dba), a set of generalized functions for extracting information from file-based databases. The dba functions are useless, however, unless you choose one or more databases at compile time. The supporting libraries for the databases you specify must already be present on the system—otherwise the compilation will fail, and you’ll end up with an installation of PHP that can’t access any databases, except in theory. The following configuration line adds support for the Berkeley Database (version 4) to our PHP installation, and assumes that the Berkeley DB application and libraries already reside in the directory /usr/local/db:

    ./configure --with-db4=/usr/local/db

    Multiple databases can be specified in the configure command. If, like most people, you’re using PHP in conjunction with Apache, and have built PHP as a Dynamic Shared Object (DSO), you can add additional database support later on by simply rebuilding PHP; however, if you’re not using dynamic modules, you might end up rebuilding Apache entirely. If all this sounds complex, not to worry—there’s a substantial PHP user community, and there are numerous installation kits that simplify the process (in fact, a Google search for “php installation kits linux” turns up several results, including this one). Most Linux distributions include their own build of PHP, as well, so that’s the first place to check.

    Database Connectivity
    File-based databases are stored on local disk space, and can be accessed without traversing the network. Full-fledged databases such as Oracle, however, typically reside on a dedicated server, and can only be accessed via the network. This raises issues of security as well as method:

    • What communication protocols does the database support? This question refers to the language(s) the database uses to communicate, or the session, presentation, and application layers of the Open Systems Interconnect (OSI) model, generally speaking. There’s a standard query language to which most database products conform, called SQL (technically, the name is not an acronym, though it’s often assumed to stand for “Structured Query Language” or “Standard Query Language”). Most vendors extend the SQL language, to make their product more attractive. Thus, SQL Server 2000 uses a superset of the SQL language called Transact-SQL, Oracle uses PL-SQL, and so on.
    • What network protocols can be used to contact the database? This question refers to the network and transport layers (3 and 4) of the OSI model. Our test case, SQL Server 2000, supports several network protocols, including the outdated NetBEUI, Novell’s IPX/SPX, and the most important candidate, TCP over IP, which we abbreviate to TCP/IP.
    • Can the database be accessed securely, using some form of encryption?

    Full-fledged databases, rather than being supported by the database abstraction layer already described, have their own supporting PHP module(s), which provide more specialized functions for interacting with the database. Similarly, these modules must be specified at compile time and the prerequisite applications installed; otherwise only PHP’s built-in MySQL support will be available in the final PHP build.

    Database Clients: Microsoft SQL Server 2000
    The OSI model, as a generalized reference model of network architecture, doesn’t always fit neatly onto real-world applications. As this Windows FAQ entry on SQL Server shows, the way in which SQL Server communicates with its clients is actually much more complex than this space will allow. Nonetheless, the OSI model is a useful rubric. For our purposes, the important point is that we plan to write queries using SQL, or some superset of SQL; we need to find a database client that can perform the remainder of the work—opening the network connection, sending the query, retrieving the data, and so on. All we care about is the data.

    To include support for a particular database in PHP, the database client must be installed somewhere on the system. In some cases, this might be a commercial client, such as those provided by Oracle or Sybase, and installation and configuration is as simple (or as complex) as following the vendor’s instructions. In other cases, source code for the database client might be available (MySQL, PostgreSQL, etc.), and it might be necessary to compile the software yourself or locate a precompiled binary for your system on the Internet. In our sample case, we need a SQL Server 2000 client for Linux. Since Microsoft isn’t on the best of terms with the Linux community, it’s doubtful that we’ll find a suitable product straight from the vendor, so we’ll have to look elsewhere.

    SQL Server happens to use the Tabular Data Stream (TDS) protocol to communicate with its clients (and as already mentioned, can transmit TDS messages over TCP/IP). According to the TDS reference (PDF format) published by Sybase, the TDS protocol resides at the application and presentation layers of the OSI model -- meaning that it basically insulates SQL programmers from having to deal with the lower-level tasks such as opening and closing TCP/IP connections cleanly and allows them to focus on optimizing their queries -- so this sounds like what we need.

    Sybase also uses a version of the TDS protocol, and in point of fact Microsoft acquired both the Transact-SQL and TDS technologies from Sybase. Though commercial implementations of the TDS protocol are available via clients such as the Sybase Open Client or Microsoft’s own (Windows-based) utilities, an Open Source implementation of the TDS protocol for Unix systems is available: FreeTDS. Configuration and compilation of FreeTDS is straightforward with few prerequisites, but Linux packages (RPM format) are available.

    Assuming that the FreeTDS installation is in /usr/local/freetds, the following configuration line will add Sybase support to a pre-4.3.0 PHP installation:

    ./configure --with-sybase=/usr/local/freetds

    In a pre-4.3.0 PHP installation, Sybase functions can be used to successfully connect to a Microsoft SQL Server database. PHP 4.3.0 (released Dec. 27 2002) added direct Microsoft SQL Server support via the following configure command. The FreeTDS libraries are still required:

    ./configure --with-mssql=/usr/local/freetds

    After successfully compiling PHP with FreeTDS support, the database client still needs to be configured before we can use PHP to execute queries.

    Configuring FreeTDS
    FreeTDS configuration is simple, consisting primarily of modifications to one file -- in our case, /usr/local/freetds/etc/freetds.conf:

    [global]
          tds version = 8.0
          try domain login = no
          try server login = yes
          

    [myserver]
          host = sqlserver.your.site.com
          port = 1433

    The foregoing is NOT a complete freetds.conf file; rather, I’ve only included the important directives. The “global” section contains directives that pertain to all servers you hope to attach to. Global settings can be modified and extended in the individual sections. Names for the individual servers (in bold, above) are arbitrary, and have no other import -- they don’t even reflect DNS names. However, they will be used in your PHP code when you issue a query.

    • First, you’ll note the “tds version” directive. This specifies the version of the TDS protocol to be used (outlined here). We happen to be connecting to a SQL Server 2000, as indicated by the “8.0.”
    • Microsoft SQL Server provides two methods of authentication: SQL Server authentication, which requires that a user be defined in SQL Server independent of Windows usernames, and integrated Windows authentication, which requires that a user be defined in Microsoft Windows itself. The next two directives indicate that we prefer SQL Server authentication to Microsoft Windows domain authentication. Unfortunately, this means that our authentication is occurring in clear-text, so blocking port 1433 (the SQL Server communication port) at the firewall is recommended.

    A First Query
    Now we’re ready to create our first SQL query. The best way to explain coding is by example, so here’s the PHP page in its entirety:

    $dbh = sybase_connect("myserver","sa","password");

    if (! sybase_select_db('master',$dbh)) {
         echo("Database down!");
         exit();
    }

    $q=sybase_query("
         select name from sysobjects
         where xtype='U'

       ",$dbh);

    while($row=sybase_fetch_array($q)) {
         printf("%s
    ",$row[0]);
    }

    ?>

    This PHP page, which performs a simple query to retrieve the names of all the system tables on the SQL Server 2000 installation, is divided into four relatively distinct sections (note that we’re using the Sybase functions rather than the SQL Server functions, indicating that this is an older PHP installation):

    1. The $dbh line opens the database connection, using the “myserver” name established previously in the freetds.conf file, followed by the username and password. All SQL Server installations have a built-in user, the “sa” or system administrator user, which has complete control over the database. Technically, it’s not a good idea to use this username for everyday tasks, so it would be a good idea to have the SQL Server administrator set up a user with more restricted access for this purpose. The same principle applies to any networked database -- grant exactly as much access as required, and no more.
    2. The “if” statement selects the default database. SQL Server has several built-in databases (master, model, msdb). If this operation fails, it’s probable that the database is down, or that our username and password have been disabled. In either case, the PHP engine issues an error and exits immediately.
    3. If we’ve successfully established connectivity, we run the query and store the output in the variable “$q.” The text in bold is our actual SQL query -- which, as you might have guessed, can only be constructed by knowing something about the structure of the database in advance. In this case, I know that SQL server has a table called “sysobjects,” that that table has at least two columns called “name” “xtype,” and that one of the values the “xtype” column can have in each row is “S,” indicating that the “name” table is a system table. A query with a value of “U” would give me the user tables, and so on. These column names and values are specific to SQL Server, of course.
    4. The last section of code parses the output into human-readable form. In this case, the results are a one-dimensional array, so we fetch the rows one at a time from the array and print out the first (and only) element of each. To make reading a bit easier, we print each row of output on a separate line by separating each with a break HTML tag (remember, PHP is typically used to create Web pages, so your code should generate valid HTML).

    The results look like this in our browser:

    sysobjects
    sysindexes
    syscolumns
    systypes
    syscomments

    … and 14 more table names

    It’s not a very prepossessing or important query, but it’s a start.

    Moving On
    SQL is a very powerful and complex language, and the various vendor extensions even more so. It’s entirely possible to make a career of nothing but database programming. For those just getting started, however, there’s still considerable value in picking up a smattering of the language. I’d suggest the following resources, in addition to the PHP manual itself:

    Additionally, links to the homepages for nearly all the databases supported by PHP are included on the “Obtaining PHP” section of the PHP FAQ.

    Questions? Comments? Post 'em below!


    James Ervin is alone among his coworkers in enjoying Michelangelo Antonioni films, but in his more lucid moments suspects that they're not entirely wrong.

     


    More articles by James Ervin:
  • Secure Shell Tips and Tricks
  • *Nix: The Year In Review
  • DNS Caching for Fun and Profit
  • Endmail Part I: The War on Spam

  • -- advertisement --


    There are 360 CertCities.com user Comments for “Introductory Database Access with PHP”
    Page 1 of 36
    9/27/03: Anonymous from California says:Thankf for the informative article. It answers a lot of questions.
    10/17/03: Anonymous says:I got high on weed laced with PHP in the 70's.
    1/27/04: AKUMA GEORGE SAGWE from KISII-KENYA says:Thanks you very much, I would like send me a booklet for questions on excel through my account for my own practices May God Bless you
    3/21/05: JOHN MWANGI from KENYA says:it is good for starters really good but i wanted a code or function which i can call everytime i write a php script to access the database like <?php $conn=mssql_connect("SERVERONE","sa",""); if ($conn) { echo ""; } else { echo "Woops..Couldn't connect!! ".mssql_get_last_message(); } mssql_select_db("JKUATMAINDB", $conn); ?
    5/4/05: José Manuel González from Cuba says:Your article is, above all, clear and pretty good for those who want to start having a good idea of how PHP works with databases. I'd have liked to have that iformation when I decided to do so. Actually, I read it hoping to find information about logining to SQL Server from PHP using a windows domain account with the MSSQL functions included with PHP. I already achive a connection to a native SQL account, but it's been imposible to me to do it with the domain account. I've done lots of things I've read from the PHP community with no result. Also it is not clear wether the connection most people have done has to do with Active Directory accounts. Could you help me?
    7/26/05: tius from indonesia says:please.. give me introductory database access with PHP
    9/14/05: Milton from Ecuador says:Hola como puedo conectarme con ADODB php con la base de datos SYBASE. Gracias. Saludos
    1/4/06: Brian Veldboom from Netherlands says:I have general computer skills and I want to learn how to make dynamic pages. Is there a (easy) way to learn learn how to set up a web server, dbase server and an application server. I installed Xamp the systems are running but I don't get it. Secondly is there a quick tutorial who can show how to make a field (smallest form) put information in it. Press send and find the information in a simpel mysql dbase. Without codes Who can this boy? to make this:“comments onIntroductory Database Access with PHP” with the
    8/18/06: Kayode from Nigeria says:Please i am interested in the Access with PHP. That is great work.
    6/9/10: Anonymous says:Tebedu
    First Page   Next Page   Last Page
    Your comment about: “Introductory Database Access with PHP”
    Name: (optional)
    Location: (optional)
    E-mail Address: (optional)
    Comment:
       

    -- advertisement (story continued below) --

    top