writing an sql query

[ap0c]

2.3.x version
What this script is supposed to do is allow a member of the view only usergroup the right to post on the forums after this script is run. When a user registers they are placed in a view only usergroup (9.)
Once a user decides they want to post, they click on a link that runs this script.

This script was written to check that users ipaddress against those in the registered usergroup, post. ipaddress and the banned usergroup.(The banned usergroup(8) check isnt in place as I'm not sure how to add that to this query)

This script doesnt cause any mqsql errors, but also doesnt work. Anyone know what I'm doing wrong?


require("./global.php");
global $DB_site,$session,$ipaddress;
print "Checking for Posting Permissions<br>";
$userid = $bbuserinfo['userid'];
print "$userid<br>";
print "$ipaddress<br>";

$tmpipcheck=$ipaddress
$result=$DB_site->query("SELECT user.*,post.* FROM user LEFT JOIN post on user.id = post.userid WHERE usergroupid=2 AND ipaddress='$tmpipcheck'");
if ($myrows=mysql_fetch_row($result));{
}WHILE ($myrows=mysql_fetch_row($result));{
eval("standarderror(\"".gettemplate("error_sorrynoposting")."\");");
exit;
}Else{

If ($bbuserinfo['usergroupid']==9){
$DB_site->query("UPDATE user SET usergroupid=2 WHERE userid='$userid'");
eval("standarderror(\"".gettemplate("error_youcanpostnow")."\");");
}else{
eval("standarderror(\"".gettemplate("error_alreadyregistered")."\");");
exit;
}
}
}




mods, if this thread is in the wrong place, please move it, just let me know where;)

[Lesane]

There can be more problems but change this:

user.id = post.userid

to:

user.userid = post.userid

[Natch]

I'm not sure this looks right ... if ($myrows=mysql_fetch_row($result));{
}WHILE

Looks to me like you are starting and ending your if prior to any result of the if being true ...

[g-force2k2]

Maybe something like this would work better? Also includes checking the banned usergroup, hopefully it helps or gives you some ideas.

if ( $chk = $DB_site->query_first ( "SELECT userid, usergroupid FROM user WHERE ipaddress='$ipaddress' ) ) :
if ( $chk['usergroupid'] == 8 ) :
//banned error here
elseif ( $chk['usergroupid'] == 2 ) :
//already registered error
elseif ( $chk['usergroupid'] == 9 ) :
$DB_site->query ( "UPDATE user SET usergroupid=2 WHERE userid=$chk[userid]" ) ;
//redirect here
endif ;
endif ;

Cheers,
g-force2k2

[ap0c]

Maybe something like this would work better? Also includes checking the banned usergroup, hopefully it helps or gives you some ideas.





Cheers,
g-force2k2that looks really good! I'll give it a try:)
thanks

[ap0c]

I had to add an else to the final check because the registered template wouldnt show up when it was placed under the registered users check

elseif ( $chk['usegroupid'] == 2 ) :

elseif ( $chk['usergroupid'] == 9 ) :
$DB_site->query ( "UPDATE user SET usergroupid=2 WHERE userid=$chk[userid]" ) ;
eval("standarderror(\"".gettemplate("error_youcanpostnow")."\");");//redirect here
else :
eval("standarderror(\"".gettemplate("error_sorrynoposting")."\");"); //already registered error




other than that it works perfectly.

Thanks again.

Now to add the post check, which will be harder since it cant be checked by the usergroupid

[g-force2k2]

I had to add an else to the final check because the registered template wouldnt show up when it was placed under the registered users check

other than that it works perfectly.

Thanks again.

Now to add the post check, which will be harder since it cant be checked by the usergroupid

Actually I updated my code had a spelling error for the variable usergroupid so the error should eval where I had intended it too. If you want to try matching it against the post ipaddress to then you can do.

if ( $chk = $DB_site->query_first ( "
SELECT userid, usergroupid
FROM user
LEFT JOIN post USING (userid)
WHERE user.ipaddress='$ipaddress' AND post.ipaddress='$ipaddress'" ) ) :
if ( $chk['usergroupid'] == 8 ) :
// banned error here
elseif ( $chk['usergroupid'] == 2 ) :
// already registered error
elseif ( $chk['usergroupid'] == 9 ) :
$DB_site->query ( "UPDATE user SET usergroupid=2 WHERE userid=$chk[userid]" ) ;
// redirect here
endif ;
elseif ( $chk['userid'] ) :
// user exists just hasn't posted or current ipaddress doesn't match the posted one?
if ( $chk['usergroupid'] == 8 ) :
// banned error
elseif ( $chk['usergroupid'] == 2 ) :
// already registered, but haven't posted? error
elseif ( $chk['usergroupid'] == 9 ) :
$DB_site->query ( "UPDATE user SET usergroupid=2 WHERE userid=$chk[userid]" ) ;
// redirect here
endif ;
else :
// failed check perhaps current ipaddres isn't the one they registered with?
// don't know if vb3 updates ipaddress field with each visit so... check to see if user actually exists!
if( !$chk2 = $DB_site->query_first ( "SELECT usergroupid FROM user WHERE userid=$bbuserinfo[userid]" ) ) :
// user isn't registered
// register redirect or need to register error?
else :
// user exists ipaddress field is invalid perhaps update now?
if ( $chk2['usergroupid'] == 2 ) :
// already registered error
elseif ( $chk2['usergroupid'] == 8 ) :
// banned error
elseif ( $chk2['usergroupid'] == 9 ) :
// update from viewed usergroup to one that can post.
$DB_site->query ( "UPDATE user SET usergroupid=2 WHERE userid=$chk[userid]" ) ;
// redirect here
endif ;
endif ;
endif ;

There's alot to think about when using such code, then again I would probably do it an entirely different way. Hope this gives you some ideas.

Cheers,
g-force2k2

[ap0c]

it was late, I didnt notice the usergroupid was spelled wrong:D

Right now all these checks are done manually, which takes quite a bit of time on certain days. I'm trying to figure out a way to make it automatic so the other admins can spend the time on something else.

I figure, a user presses a button which links to this script, runs it, the account is approved or not. Since the forum this will run on isn't going to be updated to vb3, a cron job probably wouldnt work, or be hard to set up with 2.x.x. So I thought this would be the best way. There are about 20-25,000 members in the view only group right now. Some people can wait a year or more before deciding they want to post.

[ap0c]

I'm running into an error running the first query
Invalid SQL: SELECT userid, usergroupid FROM user LEFT JOIN post USING (userid) WHERE user.ipaddress='xx.xxx.xx.xx' AND post.ipaddress='xx.xxx.xx.xx'
mysql error: Column: 'userid' in field list is ambiguous
mysql error number: 1052

changed ipaddress to xx.

using user.userid gave a 1064 error

[g-force2k2]

I'm running into an error running the first query
Invalid SQL: SELECT userid, usergroupid FROM user LEFT JOIN post USING (userid) WHERE user.ipaddress='xx.xxx.xx.xx' AND post.ipaddress='xx.xxx.xx.xx'
mysql error: Column: 'userid' in field list is ambiguous
mysql error number: 1052

Try changing:

SELECT userid, usergroupid

To

SELECT user.userid, usergroupid

Regards,
g-force2k2