PHP database interfaces

[TerryP]

Is there any standard set of classes or perhaps module of functions that provides a wrapper-interface around PHP's various dbtype_functions, and chucks exceptions on error, insane usage, or just plane stupidity? That can be quickly installed into a web hosts webroot/includes/ ?


For something I'm planning on doing, I'd really rather not have to hunt down and debate, "Is it worth making this handle errors now or wait until it breaks later" when trying to clean up some old code. So I figure, make new work use exceptions and refactor as necessary when fixing stuff. I however, don't really know a lot of PHP lol. I ended up helping out, using my knowledge of C++/Perl to work on PHP scripts, and eventually the PHP Reference Manual to learn more specifics -- but beyond that, I don't know what to look for.

I'm not spending any more hours dealing with other peoples failure to check errors. But I also don't want to write a module myself, if it's already been written and used everywhere else but by my predecessors.

/**
 * just groaning out loud, if anyone cares for more details
 */

I'm stuck maintaining code, that frankly I would rather work in octal then look at... Since the crap is prone to breaking in occasionally subtle ways, when ever someone so much as farts! I'm thinking of making use of Exceptions now that PHP4 is EOL (and we've been on PHP5 for ages). One of the things I think it would do most help in, is dealing with database operations:


A lot of the code using the database looks like this:

// very close to some real code on the site, but made to look nicer
global $dbi, $cmsOrSiteSpeicificThingdepreciatedLongTimeAgoAndRemovedNowWithoutMention, $alotofGlobals;
$dontworknomore=$cmsOrSiteSpeicificThingdepreciatedLongTimeAgoAndRemovedNowWithoutMention;
... 

$query4 = sql_query("SELECT foo,bar from table_name inner join four times on $dontworkanymore = $xyz where blah blah blah", $dbi);
if ($query4) {
?></td></tr></table><?
while($foo = sql_fetch_array($query4, $dbi)) {
?>
<table width="53%" border="1" align="left" cellpadding="1" cellspacing="1">
  <tr>
    <td width="74%" height="100"><?
if ($something){
	do_something_he_wrote_in_20_other_modules_wihout_an_inc("header message");

	echo "<center><table><tr><td width=80><b>some text</b></td><td><b>some more
    text</b></td></tr>";
	
    echo "<tr><td>".$foo['foo']."</td><td>".$foo['bar']." [ <a href='cmsstuff.php?x=Whatever&y=whatever&alot=ofthis&something=".$foo['bar']."&important=anulllvalue'>link text</a> ]</td></tr>"; }
    else {
  ?> <div>don't even get me started</div> <?
}
}
  }
// never handles $query4 failing, the array fetch failing, or returning bad values, et cetera.

So, since I'm the one that actually brought the idea of a "library" and defining constants to the websites custom modules.... As I am stuck fixing things time after time after time and again, whenever something breaks or most be changed. I think it would be better if things change to using exceptions, since there are lots of things that can blow up, but are assumed to remain as unchanging as my hatred for the original programmer.


I spent 3 hours Saturday night, trying to prove that a section of code that by every possible reading, should be valid, yet didn't do what it was supposed to, until I finally gave up and went to sleep. Today, when I had to edit things in the DB manually, I got a punch in the face. It was working exactly as it should, only it was getting garbage in and putting garbage out without looking, but wouldn't display said garbage -- appearing as if nothing happened at all.


When in fact the DB was full of crap, thanks to something that got dropped without anyone knowing about it.

[deemon]

I've only used one - adodb. I think it's possible to just drop it somewhere and include necessary stuff.

[roddierod]

I'll second ADOdb. I used it against MS SQL and MySQL on Windows and FreeBSD. But I still have my one library of wrappers around ADOdb and the built in database function. Everything I've done is in PHP5.

Maintaining that code must be fun

[Carpetsmoker]

Just a general comment:

Mixing PHP and HTML like you did in the above example is a bad idea and leads to very unmaintainable and ugly code very fast.

[deemon]

"Leads to" in that context is an understatement

[TerryP]

ADOdb looks interesting, but I don't think it's quite what I'm looking for.

Odds are, I'll probably need to play around a bit myself. Another thing I've added to my priority list, is iterators. I personally don't like explicit iterators in languages like C++/Java but in this case, I think it might be worth while. What I'm thinking is something more like this:

require_once 'ourlibname/dbl.php';
require_once 'functions.php';

$dbl = init_sas_dbl($dbi);  // return the db layer object or explode, 
                            // so I don't have to check it every file.

echo table_start("header message");

try {
    $data = $dbl->query("select statement");

    while($data->hasNext()) {
       do something to $data->next() in some shape or form
    }
} catch (SASSQLException $e) {
    // deal with problem -- empty row, accessing null values, etc
}

echo table_end();

At least, that's probably what it would look like at first, if I had to implement it. I think PHP5 has an interface that can be used to allow foreach'ing over an object, so I'll need to look it up.


It might not be a great way to do it, but it can't be any worse then what I've already got to stare at...




@Carpetsmoker, I agree lol.


The first time I was asked to work on a module, I came back with comments about bleeding eyes. I like PHP has a language, but on sasclan.org most of it looks like that, or worse... There are still bits of HTML out there, that I still can't find in HTML 2,3,4.01 references! The most eye-appeasing code around consists of PHP, SQL, HTML, and JavaScript by the tangle and without end in such style as the above, dating back to 2005 or worse.

Maintaining consists of two people, me (vim/sftp) and my friend (DreamWeaver), and I'm the only one generally fluent in (X)HTML. I did create a small utility class to handle the HTML output, hoping that using it would improve legibility but I'm the only one who uses it. If I ever meet the original author of most of our custom modules, he'll probably loose a few teeth.

[roddierod]

PHP5 using the MS SQL functions, they have the same for mysql_ and other I think.:

$this->queryarray = mssql_query($sql);

if(mssql_num_rows($this->queryarray)>1){
  while($row = mssql_fetch_assoc($this->queryarray)){
   <do stuff>
   mssql_next_result($this->queryarray);
  }
}