Skip to content

A Common PHP/MySQL User Database

I’m an admin. Most of what I do with PHP and MySQL is maintain. [On rare, rare occasions, I work on code. Now that I'm back in graduate school, rare is also defined as "never".] One problem that I see that desperately needs solving: a common user database that has great, simple API hooks.

What’s the point?

If you have more than one package that uses PHP to manipulate and represent MySQL data, you’ve undoubtedly wished for commonality in your user databases. If you run a Weblog-as-CMS and a forum, you’re probably wishing, “Man, why can’t WordPress and phpBB talk to each other?” [I mention those two only because they're the most popular such choices; I've largely moved from phpBB to SMF, and I'm also experimenting with TextPattern as a WordPress replacement in situations where it might be more attuned to my needs.] I think they should.

What are the parameters you’d need?

This database needs to be dead simple, because having data in a separate table [or database] just adds complexity to the query, and this complexity will add overhead. So, with a minimalist ethos being the wind beneath our wings, let’s take off:

User_id: A simple incrementing counter. Because this is stored as an integer, it’ll be useful to have for your API hooks, because you’re passing smaller and more digestible data than the username [see below].

Username: User’s email address. Having a separate username and email makes no sense to me—and because people do change their email addresses and often forget who all has the old one, having the username as the user-email solves all sorts of issues. Not only is this good for the user, but it’s also good for the administrator—no more table lookups to find the username of someone who emails you with a lost account request!

Password: Stored as an MD5 hash of the user input. This is industry standard.

Date created: Automagically generated.

Date modified: Automagically generated. This is important because it’s good to have a record of when stuff is changed for security purposes.

User handle / display name: It’s important to have this in the system, because a good package like this should never display the user’s email address to all the screen-scraping spammers of the world. Also, we don’t want confusion when people change their email addresses.

And that’s really it. Authentication should be handled with cookies, and API hooks should support sessions.

This would be a hugely beneficial open-source project, and should be licensed liberally rather than with the GPL, because I don’t want to see commercial products locked out of this. Commonality is an important thing.

[And yes, some will argue that this is very close to LDAP, or that you should make it Kerberos-like. Knock yourselves out---I haven't seen a good implementation of either in the Web-based scripting environment of the day.]

Posted in Things That Should Be Built, But Not By Me.

By Geof F. Morris
10/5/2005

8 comments

Ridding Yourself of Shadow Topics in phpBB

I’ll eventually write this into a modification, but phpBB moderators and administrators know that, when you move a topic, you’re given the option to create a “shadow topic”, one which will point users to the new topic. What’s not apparent is how to delete these shadow topics or how to have them expire.

It’s actually pretty simple: in phpbb_topics, there’s a field called “topic_moved_id”. When you move a topic and specify that a shadow topic be created, a new topic is added to the database. This topic then owns all the posts associated with the old topic, which becomes the “shadow topic”. The “topic_moved_id” field exists simply to let phpBB know the association between the topic and its shadow.

To remove the fields, all you have to is delete any topic_id that has a non-null topic_moved_id field. Now, the easiest way to do this would be DELETE FROM phpbb_topics WHERE topic_moved_id > 0;, but that deletes all the shadow topics. That’s decidedly not cool; I decided to leave the last 7-ish days of shadow topics.

So, I pulled up my handy-dandy UNIX timestamp converter, fed in 04 17 2005 00 00 00, and got 1113696000 as a timestamp. Working from there, the SQL query gets only marginally harder: DELETE FROM phpbb_topics WHERE topic_moved_id > 0 AND topic_time < 1113696000;. I executed that, and 748 shadow topics were gone from The Rumor Forum.

To turn this into a worthwhile phpBB modification, I just need to mimic how to put this in the Administration Panel in 2.0.x. I’ve got a couple modifications already installed that I can mimic, but for now, it’s worth just posting this go-by for anyone else to use.

Posted in phpBB Modifications.

By Geof F. Morris
4/24/2005

5 comments

Allow Mods to See Invisible Compliance

As a note, I checked the compliance of my Allow Moderators to See Invisible mod with phpBB v2.0.13 last night, and it still works with an out-of-the-box installation.

Posted in phpBB Modifications.

By Geof F. Morris
4/1/2005

No comments

GFMorris.org Running WP 1.5

Yes, it’s been forever since I’ve written code. There’s many reasons for this. One has been dealing with the administrivia of dealing with a deluge of comment spam.

I’m now running WP 1.5 and hoping for some more inspiration in the code department. I’m sorely lacking…

Posted in General.

By Geof F. Morris
2/24/2005

1 comment

0.0.1: Mission Statement and Mission Objectives.

In developing ShowInfo, I’ve been following the database design process laid out by Michael J. Hernandez in his text, Database Design for Mere Mortals™. [Readers of my regular Weblog will note that I picked this book up some time ago].

Hernandez’s first step is to develop a mission statement and mission objectives for the database being developed. He explains his rationale as follows:

The first phase in the database design process involves defining a mission statement and mission objectives for the database. The mission statement establishes the purpose of the database and provides you with a distinct focus for your design work.

– pg. 79, Database Design for Mere Mortals™

I’ve seen too many projects out there that seem to have never had an initial focus, instead choosing to hack around and then let the focus develop as time and code pass. I’m prone to that myself, so I’m being regimental in holding myself to this standard.

As such, I’ve developed the following mission statement for Show Info:

ShowInfo Mission Statement

The purpose of ShowInfo is to maintain show information, allow users to indicate their presence at a show and view those attending, and to facitilitate sharing of concert experiences after the show.

ShowInfo Mission Objectives

  • ShowInfo must maintain data pertinent to attending concerts.
  • ShowInfo must allow users to indicate shows that they will/might attend, as well as shows that they have attended in the past.
  • ShowInfo must allow users to maintain setlist and concert photo data.
  • ShowInfo must create reports of future shows.
  • ShowInfo must archive data about previous shows.

The second step in the ShowInfo development process will be developing the preliminary table and field lists.

Posted in dev-ShowInfo.

By Geof F. Morris
7/31/2004

3 comments