[RESOLVED] Damsel in DB query distress

Tank_Girl

I trying to do something I know is not a difficult task, but I'm no mysql/php guru either.

We are serious World of Warcraft addicts, with a fairly decent guild website.
I've setup a phpBB forum (a few mods), and WoWroster (serious mods).
I'm pretty good at picking apart code and piecing it together, but this one has me confused.

Both the phpBB and the WoWRoster share the same db, so authentication is the same. Table prefixes separate them, phpbb and roster .
Since the forums are meant for guild members only, and the roster list is updated daily, I would like the phpBB registration to check first IF the $username is found in the roster_members ['name'] before continuing with the rest of the registration process. Maybe with error message of, $lang['GuildMember_invalid']

Basically the REVERSE of what phpBB does to check if the username is already taken in the "phpBB member" list.

I believe the file to be edited to do this is the forums/includes/functions_validate.php in phpBB.

I have no clue how to set this up. The extent of my skill is I can make a list echo of the roster members. (mysql stuff has me baffled)

Can anyone help at all?

I've put the functions_validate.php into a text file as it is a fairly long code.
http://www.golwow.net/phpbbvalidation.txt

Some code I've been playing with but not sure where to put it or if it will work.
Since the WoWroster and phpBB are in the same database.
it can use the global info to connect I think.

global $db, $lang
   $rosterquery=" SELECT * FROM roster_members WHERE name='$username'";
   $rosterresult=mysql_query($rosterquery);
   $rosternum=mysql_numrows($rosterresult);
   $error = true;
   $error_msg .= ( ( isset($error_msg) ) ? '<br />' : '' ) . $lang['GuildMember_invalid'];

sneakyimp

go WoW! I love the very notion of that game. Let me know if you're ever in Antonidas.

First off, there appears to be a function called validate_username() right? There are a number of checks in there...here's the one that checks to see if the username exists in the database:

	$sql = "SELECT username 
		FROM " . USERS_TABLE . "
		WHERE LOWER(username) = '" . strtolower($username) . "'";
	if ($result = $db->sql_query($sql))
	{
		while ($row = $db->sql_fetchrow($result))
		{
			if (($userdata['session_logged_in'] && $row['username'] != $userdata['username']) || !$userdata['session_logged_in'])
			{
				$db->sql_freeresult($result);
				return array('error' => true, 'error_msg' => $lang['Username_taken']);
			}
		}
	}
	$db->sql_freeresult($result);

so we'll want to do something like that only we want to make sure it does exist in your roster db.

try adding this code in that function toward the end:

	$rostersql=" SELECT name
		FROM roster_members
		WHERE LOWER(name)='" . strtolower('$username') . "'";
	if ($result = $db->sql_query($rostersql))
	{
		if ($db->sql_numrows($result) < 1)
		{
			$db->sql_freeresult($result);
			return array('error' => true, 'error_msg' => $lang['GuildMember_invalid']);
		}
	}
	$db->sql_freeresult($result);

IMPORTANT it must be before this line:

	return array('error' => false, 'error_msg' => '');

Tank_Girl

well I've been testing this in unfortunately it blocks every username.
I placed this code right after the username cleanup.
I figured this would be the best place for it.

	// Remove doubled up spaces
	$username = preg_replace('#\s+#', ' ', trim($username)); 
	$username = phpbb_clean_username($username);

I can be looking right at the table roster_members in the same DB as phpbb_user (table used in checking if username is taken), and see a "name" in the 'name' field.
I copy and paste and still get the error message this person is not in the guild.

I thought this would have been an easy task.
I really dunno where to go from here. the code doesn't bring any error, and returns the error message, but something is not right in the actual checking the username to the roster. It's not matching up. hmm

tomhath

Try selecting and echoing the first ten or so usernames with "WHERE 1=1 LIMIT 10". Also put other characters around the name when you echo it to see if something else is there:

echo ">>>$name<<<";

sneakyimp

i think i made a mistake in my code...i have single quotes around $username which means it will never actually interpret the var....try this instead:

	$rostersql=" SELECT name
		FROM roster_members
		WHERE LOWER(name)='" . strtolower($username) . "'";
	if ($result = $db->sql_query($rostersql))
	{
		if ($db->sql_numrows($result) < 1)
		{
			$db->sql_freeresult($result);
			return array('error' => true, 'error_msg' => $lang['GuildMember_invalid']);
		}
	}
	$db->sql_freeresult($result);

in the event it still does not work, try to get a peak at what sql it's actually generating with that statement (it's quite helpful to have a log-writing function). Had you done so before it might have been obvious what the problem is.

Tank_Girl

THANK YOU SNEAKYIMP!!!
It works beautifully!

Here all it was, was just the single quotes.

I've put it all together in a mod for others..

//ROSTER CHECK MOD by tankgirl
//This will check your WoWRoster for the guild member, before they can register for yout phpBB forums.
//Special thanks sneakimp from PHPBuilder for all the help!
//Only works in WoWRoster/phpBB tables sharing the same database.

OPEN
forums/language/lang_english/lang_main.php

LOOK FOR
##################################

$lang['Empty_message_email'] = 'You must enter a message to be e-mailed.';

##################################

AFTER ADD
##################################

//
//------------------- WoWRoster Check BEGIN ------------------
//

$lang['GuildMember_invalid'] = 'This username  is not yet a valid Guild Member.';

//
//------------------- WoWRoster Check END ------------------
//

##################################

OPEN
forums/includes/functions_validate.php

LOOK FOR
##################################

$username = phpbb_clean_username($username);

##################################

AFTER ADD
##################################

//
//------------------- WoWRoster Check BEGIN ------------------
//

$rostersql=" SELECT name
        FROM roster_members
        WHERE LOWER(name)='" . strtolower($username) . "'";
    if ($result = $db->sql_query($rostersql))
    {
        if ($db->sql_numrows($result) < 1)
        {
            $db->sql_freeresult($result);
            return array('error' => true, 'error_msg' => $lang['GuildMember_invalid']);
        }
    }
    $db->sql_freeresult($result); 

//
//------------------- WoWRoster Check END ------------------
//

##################################

SAVE AND CLOSE ALL

Piranha

tomhath wrote:
Try selecting and echoing the first ten or so usernames with "WHERE 1=1 LIMIT 10". Also put other characters around the name when you echo it to see if something else is there:

Don't use "WHERE 1=1" or something simular. It is better and faster to just omit the WHERE-part. I have used it myself some time ago, but read a post on this forum that basically the database checks if 1 equals 1 once for every row in the table.

sneakyimp

I'll happily take gold as payment xD

Tank_Girl

I would more than gladly send some gold compensation, if you find yourself on the Sisters of Elune realm lol

Tank_Girl

I've upgraded to phpbb3.
Anyone have a clue where the "/includes/functions_validate.php" equivalent file would be?