Making your MOD DBAL Compliant

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
Xore
Former Team Member
Posts: 2594
Joined: Wed Apr 16, 2003 7:42 pm
Location: the wastelands between insomnia and clairvoyance
Contact:

Making your MOD DBAL Compliant

Post by Xore » Fri Sep 19, 2003 8:56 am

This post is for the purpose of helping MOD Authors make non-DBAL Compliant Mods DBAL Compliant. For the most part, this process should be EASY. If you are having any troubles, however, feel free to post here, and i'm sure there will be more than enough input from people to help you out.

Note: Discussion/Argument of actions soon to take place will not be tolerated in this thread.
Camels in the Mist
<noise type="random" source="camel" />
Cash Mod

Xore
Former Team Member
Posts: 2594
Joined: Wed Apr 16, 2003 7:42 pm
Location: the wastelands between insomnia and clairvoyance
Contact:

Post by Xore » Fri Sep 19, 2003 4:23 pm

so, some of the more useful ones will be as follows

(note, that $db is automatically instantiated in your script if you've included common.php)
  • sql_query()

    Code: Select all

    $db->sql_query($sql);
    This command will execute a sql query on your database. It returns a resultset, which you can grab as follows:

    Code: Select all

    $sql = "SELECT foo FROM bar";
    $result = $db->sql_query($sql);
    however, to be safe, phpbb generally wraps this function in an if block and kills the script if anything goes wrong.

    Code: Select all

    		$sql = "SELECT * FROM foobar";
    		if ( !($result = $db->sql_query($sql)) )
    		{
    			message_die(GENERAL_ERROR, 'Error retrieving foobar data', '', __LINE__, __FILE__, $sql);
    		}
    
    if you're making calls that don't give output, (ie, UPDATE or DELETE) then you can ignore the $result as follows

    Code: Select all

    		$sql = "DELETE FROM foo WHERE key = 'bar' ";
    		if ( !$db->sql_query($sql) )
    		{
    			message_die(GENERAL_ERROR, 'Error deleting foobar data', '', __LINE__, __FILE__, $sql);
    		}
    
    So. assuming we have a $result, what can we do with it? We have quite a few useful functions that DBAL gives us to approximate the built-in DBMS-specific commands.
  • sql_numrows()

    Code: Select all

    $db->sql_numrows($result)
    tells you how many rows are in the $result
  • sql_affectedrows()

    Code: Select all

    $db->sql_affectedrows()
    will tell you (after an update or delete) how many rows were affected
  • sql_numfields()

    Code: Select all

    $db->sql_numfields($result)
    will tell you how many fields there are in the result that you have obtained.
  • sql_fieldname() and sql_fieldtype()

    Code: Select all

    $db->sql_fieldname($offset, $result)

    Code: Select all

    $db->sql_fieldtype($offset, $result)
    will tell you the name/type of the database field at a specific offset
  • sql_fetchrow()

    Code: Select all

    $db->sql_fetchrow($result)
    This is your standard method of obtaining data from a result. Often in phpbb code, you will see something of this nature:

    Code: Select all

    		$sql = "SELECT foo FROM bar";
    		if ( !($result = $db->sql_query($sql)) )
    		{
    			message_die(GENERAL_ERROR, 'Error retrieving data', '', __LINE__, __FILE__, $sql);
    		}
    		while ( $row = $db->sql_fetchrow($result) )
    		{
    			do_stuff_with_$row();
    		}
    if, on the other hand, you don't want to do stuff to $row specifically, you can just pull out the entire thing and dump it in an array as follows:
  • sql_fetchrowset()

    Code: Select all

    		$sql = "SELECT foo FROM bar";
    		if ( !($result = $db->sql_query($sql)) )
    		{
    			message_die(GENERAL_ERROR, 'Error retrieving data', '', __LINE__, __FILE__, $sql);
    		}
    		if ( !($myarray = $db->sql_fetchrowset($result)) )
    		{
    			message_die(GENERAL_ERROR, 'Query returned no data', '', __LINE__, __FILE__, $sql);
    		}
  • sql_fetchfield()
  • sql_rowseek()
    I must admit, i'm not very familiar with these functions, i've never used them personally. If anyone has anything to contribute, it would be appreciated.
  • sql_nextid()
    This is a useful command for determining an id number for an item that you've just inserted, ie auto-incremented field.

    Code: Select all

    $newid = $db->sql_nextid()
  • sql_freeresult()
    Frees the result

    Code: Select all

    $db->sql_freeresult($result)
So, i hope this has been of some help to you all.

cheers
Camels in the Mist
<noise type="random" source="camel" />
Cash Mod

User avatar
Moogie
Registered User
Posts: 363
Joined: Thu Aug 22, 2002 11:39 am
Location: UK
Contact:

Post by Moogie » Fri Sep 19, 2003 9:14 pm

I think my MODs are compliant, from the looks of what's been posted here (though I'm not sure I entirely get what's got to change and to what :? ), but I was wondering whether someone could take a look at them to confirm? (hope I'm alright asking this here, seemed like the best place to post this.)
Moogie

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut » Fri Sep 19, 2003 9:40 pm

Moogie,
Your MOD cannot contain commands specifically for MySQL. I see this one in the first few lines of the first file I open of your MOD.

Code: Select all

$crow = mysql_fetch_array($cresult);
To fix this, you'd use

Code: Select all

$crow = $db->sql_fetchrow($cresult);
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

wGEric
Former Team Member
Posts: 8805
Joined: Sun Oct 13, 2002 3:01 am
Location: Friday
Name: Eric Faerber
Contact:

Post by wGEric » Fri Sep 19, 2003 9:44 pm

Moogie wrote: I think my MODs are compliant, from the looks of what's been posted here (though I'm not sure I entirely get what's got to change and to what :? ), but I was wondering whether someone could take a look at them to confirm? (hope I'm alright asking this here, seemed like the best place to post this.)


Your MODs are mostly DBAL compliant. Jelly pointed out one. There are also a few other problems that you may want to consider fixing.

One is to use the phpBB template system. If you use this there is no need to put chunks of HTML in your php files. It is ok to put a little HTML in them.

Second is to use the Constants. Using the constants that are found in includes/constants.php will make things easier for the people that use your MOD. For example in your sql statements you write out the tables, (phpbb_users), if you use its constant (USERS_TABLE) people who don't have the prefix phpbb_ won't have to go through and change all instances of phpbb_ to what ever their prefix is.

If you can fix these problems and update your install files to comply with the MOD Template we could get your MOD into the DB. :wink:
Eric

User avatar
MarkDHamill
Registered User
Posts: 3805
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Post by MarkDHamill » Sat Sep 20, 2003 11:56 pm

I've been using DBAL for my beta, but my SQL is very complex and uses heavy MySQL functions. I have no idea how to write this stuff for every database out there, but it sure works fine on MySQL. I also don't have access to those databases.

As an example, am I responsible for making this DBAL code, tuned for MySQL, work in for all the databases out there?

Code: Select all

   $sql2 = "select c.cat_title, f.forum_name, t.topic_title, u.username as \"Posted by\", 
      post_time, from_unixtime(post_time, \"%d %b %Y, %h:%m %p\") as \"Display Time\",
      if(length(pt.post_text)<=" . $extract_size . ",pt.post_text,concat(substring(pt.post_text,1," . $extract_size. ") ,\"...\")) as \"Post Text\", p.post_id, t.topic_id, f.forum_id
      from " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t, " . FORUMS_TABLE . " f, " . USERS_TABLE . " u, " .
      CATEGORIES_TABLE . " c, " . POSTS_TEXT_TABLE . " pt
      where p.topic_id = t.topic_id and t.forum_id = f.forum_id and p.poster_id = u.user_id and
      f.cat_id = c.cat_id and p.post_id = pt.post_id and
      post_time > " . $code . " and f.forum_id in (" . $forum_list . ") 
      order by c.cat_order, f.forum_order, t.topic_title, post_time";

   // Uncomment next line to see SQL used
   // $msg .= "**DEBUG**\r\n" . $sql2 . "\r\n**DEBUG**\r\n";

   if ( !($result2 = $db->sql_query($sql2)))
     {
       message_die(GENERAL_ERROR, 'Unable to execute retrieve message summary for user', '', __LINE__, __FILE__, $sql);
     }

Xore
Former Team Member
Posts: 2594
Joined: Wed Apr 16, 2003 7:42 pm
Location: the wastelands between insomnia and clairvoyance
Contact:

Post by Xore » Sun Sep 21, 2003 1:16 am

@MarkDHamill

What i would suggest is quite simple. sample the DBAL layer (ie, check what DBMS you're using). If it's mysql, use the query you've got. If it's not mysql, just pull the data without the extra functions, and implement the extra stuff via php. from what i see in your post, it looks like it shouldn't take any time at all, as php has functions for those things already :-)

some useful links:

strlen()
substr()
date()
You may also want to look up phpbb's date functions, although i can't remember what it is off the top of my head.
Camels in the Mist
<noise type="random" source="camel" />
Cash Mod

AbelaJohnB
Former Team Member
Posts: 5674
Joined: Fri Jul 06, 2001 11:56 pm

Post by AbelaJohnB » Mon Oct 13, 2003 6:17 am

topic was split, see here for split

Dimentox
Registered User
Posts: 6
Joined: Thu Mar 14, 2002 5:04 pm

Assoc array

Post by Dimentox » Tue Apr 27, 2004 6:24 am

I use mysql fetch object..
there is no db code for that?
What about an assoc array?

Xore
Former Team Member
Posts: 2594
Joined: Wed Apr 16, 2003 7:42 pm
Location: the wastelands between insomnia and clairvoyance
Contact:

Re: Assoc array

Post by Xore » Tue Apr 27, 2004 4:03 pm

Dimentox wrote: I use mysql fetch object..
there is no db code for that?
What about an assoc array?


all arrays are automatically associative when using
sql_fetchrow() and
sql_fetchrowset()
Camels in the Mist
<noise type="random" source="camel" />
Cash Mod

Silverhawk060
Registered User
Posts: 237
Joined: Mon Jun 16, 2003 4:40 am
Contact:

Post by Silverhawk060 » Thu Jun 24, 2004 7:39 am

i think this topic should be stickied

Graham
Former Team Member
Posts: 8462
Joined: Tue Mar 19, 2002 7:11 pm
Location: UK
Contact:

Post by Graham » Thu Jun 24, 2004 11:14 am

Silverhawk060 wrote: i think this topic should be stickied

It once was :)

It's now linked in the Announcement since there were getting to be too many sticky topics.
"So Long, and Thanks for All the Fish"

phpBB Useful Links: Knowledge Base | Userguide | Forum Search | MOD Database | Styles Database
My Links: Blog!

Silverhawk060
Registered User
Posts: 237
Joined: Mon Jun 16, 2003 4:40 am
Contact:

Post by Silverhawk060 » Thu Jun 24, 2004 12:42 pm

ahhh i see, i was wondering where it dissapeared to cause i remember it being a sticky :P

User avatar
AlleyKat
Registered User
Posts: 1037
Joined: Wed Jul 16, 2003 5:06 pm
Location: Odense, Denmark
Contact:

Post by AlleyKat » Sun Jul 18, 2004 9:06 am

This should, ofcourse, also be a KB doc?
Image Dansk phpBB support (uofficielt)
Find unofficial support in your language here.
Help translate Mozilla extensions @ Babelzilla - Your Language Counts Too!

User avatar
battye
Extension Customisations
Extension Customisations
Posts: 10933
Joined: Wed Feb 11, 2004 11:02 am
Location: Australia
Contact:

Post by battye » Sat May 28, 2005 4:21 am

Is there a $db->sql_close function?
Customisations Team Member

Post Reply

Return to “[2.0.x] MOD Writers Discussion”