vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   writing an sql query (https://vborg.vbsupport.ru/showthread.php?t=60962)

ap0c 01-30-2004 02:06 PM
writing an sql query
 
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 01-30-2004 02:29 PM
There can be more problems but change this:

user.id = post.userid

to:

user.userid = post.userid

Natch 01-30-2004 02:54 PM
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 01-30-2004 04:36 PM
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 01-30-2004 09:18 PM
Quote:
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 01-31-2004 06:15 AM
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 01-31-2004 06:42 AM
Quote:
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 01-31-2004 02:45 PM
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 02-01-2004 04:16 PM
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 02-01-2004 04:25 PM
Quote:
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


All times are GMT. The time now is 09:04 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01098 seconds
  • Memory Usage 1,788KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (9)bbcode_php_printable
  • (3)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete