vb.org Archive
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   SQL syntax help (https://vborg.vbsupport.ru/showthread.php?t=50040)

Tryfwar 03-12-2003 06:15 PM
SQL syntax help
 
I was hoping that somebody might be able to help me with this problem:

Firstly, I have created a new table in my forums db called userclass and in it there are 2 fields: userclassid & title.

Then i have added a field to the user table called 'userclassid' as well and have successfully been able to modify the user.php to enable me to assign a class title to each user.

In addition to this i have added a field to the usergroup table called 'rollcall' which is simply a 1 or 0, so i can assign wether or not i wish for these usergroups to appear in the rollcall. (just like the showgroups function does for the showgroups.php)

The problem i have is in this query that was originaly from the showgroups.php, i cannot work out how i should write it :(

I would like it so that it will list users in groups of class and only those who have been assigned '1' in the usergroup.rollcall table.

The below Query displays them almost as i want but it lists each user 5 times!! lol oh and it also seems to display users regardless of the WHERE clause :/


PHP Code:
$users = $DB_site->query("
    SELECT
        $classfieldselect userclass.title, user.userid, user.username, user.userclassid,  user.userclassid,
         user.usergroupid
        FROM userclass
        LEFT JOIN user ON (userclass.userclassid = user.userclassid)
        LEFT JOIN userfield ON (userfield.userid = user.userid)
        LEFT JOIN usergroup ON usergroup.rollcall
        WHERE usergroup.rollcall = 1
    "); 
Thanks in advance to anyone who can help :)

Xenon 03-12-2003 06:57 PM
try that one:

PHP Code:
$users = $DB_site->query("
    SELECT DISTINCT
        $classfieldselect userclass.title, user.userid, user.username, user.userclassid,  user.userclassid,
         user.usergroupid
        FROM user
        LEFT JOIN userclass ON (userclass.userclassid = user.userclassid)
        LEFT JOIN userfield ON (userfield.userid = user.userid)
        LEFT JOIN usergroup ON (usergroup.usergroupid = user.usergroupid)
        WHERE usergroup.rollcall = 1
    "); 

Tryfwar 03-12-2003 07:06 PM
woah! nice one !!! :)

Works a treat!!! :D :D :D

Thanks alot Xenon :) ... juuuuuust one more thing: its in reverse alphabetical order, anyway to switch that ? :)

The usernames are i mean.

Xenon 03-12-2003 07:27 PM
welcome :)

add ORDER BY username ASC after the where clause ;)

Tryfwar 03-12-2003 07:53 PM
Thanks a million :) made me very happy :)

Tryfwar 03-13-2003 11:22 AM
how can i make it so that it WONT list members who haven't yet got a 'userclassid' please?

I've tried using the NOT 0 syntax but i keep getting errors :(

Xenon 03-13-2003 11:30 AM
use that where:[sql]WHERE usergroup.rollcall = 1 AND user.userclassid != 0[/sql]

Tryfwar 03-13-2003 11:35 AM
Code:

 SELECT DISTINCT
        userclass.title, user.userid, user.gender, user.username, user.userclassid,
        user.usergroupid
        FROM user
        LEFT JOIN userclass ON (userclass.userclassid = user.userclassid !=0)
        LEFT JOIN userfield ON (userfield.userid = user.userid)
        LEFT JOIN usergroup ON (usergroup.usergroupid = user.usergroupid)
                WHERE usergroup.rollcall = 1 ORDER BY username ASC
    ");
just worked it out!
err its different to yours

Xenon 03-13-2003 11:35 AM
use mine :)

Tryfwar 03-13-2003 11:37 AM
have hehe, thanks again Xenon, your a star :)


All times are GMT. The time now is 06:05 AM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

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.01201 seconds
  • Memory Usage 1,732KB
  • 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
  • (1)bbcode_code_printable
  • (2)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (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
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete