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?

PHP Code:

 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 ...

PHP Code:

    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.

PHP Code:

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]

Originally Posted by g-force2k2

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





Cheers,
g-force2k2

that 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

PHP Code:

    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]

Originally Posted by 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

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.

PHP Code:

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

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

PHP Code:

 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]

Originally Posted by ap0c

I'm running into an error running the first query

PHP Code:

 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:

PHP Code:

       SELECT userid, usergroupid 


To

PHP Code:

       SELECT user.userid, usergroupid 


Regards,
g-force2k2