SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: sql query?

  1. #1
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query?

    How can I check whether two comma delimited strings in a sql query contain the same values (not in the same order)? The values will all integers.

    any ideas (i'm trying to keep this in the sql query)?

    domains.id in ( user.domainid )

    PHP Code:

          $sql 
    "SELECT user.username, user.password, user.domainid, domains.id, domains.domain
                  FROM   user, levels, groups, domains
                  WHERE
                         (
                           IF ( 
    $this->multipledomain, domains.domain in ('$this->domain') , domains.domain = '$this->domain' )
                         ) AND 
                           domains.id in ( user.domainid )
                           AND
                         (
                           IF ( 
    $this->multiplegroups, user.groupid in ('$this->group') , user.groupid = '$this->group')
                         ) AND
                         (
                           IF ( 
    $this->multiplelevels, groups.levelid in ('$this->level') , groups.levelid = '$this->level')
                         ) AND
                         user.username      =  '
    $this->username' AND
                         user.password      =  '
    $this->password'
                 "

    ps this can be in any other sql server if you have an answer

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're in for much more heartache than just this query

    it seems user.domainid is a list of keys

    that violates first normal form

    can you change the table design?

    if so, many of your queries will be dramatically simplified

    post your current table design if you want to pursue this


    rudy

  3. #3
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just did a quick dump

    Code:
    CREATE TABLE domains (
      id int(5) NOT NULL auto_increment,
      domain varchar(100) NOT NULL default '',
      active tinyint(1) NOT NULL default '1',
      description text NOT NULL,
      UNIQUE KEY id (id,domain)
    ) TYPE=InnoDB COMMENT='List of domains';
    
    #
    # Dumping data for table `domains`
    #
    
    INSERT INTO domains VALUES (1, 'developer.co.uk', 1, 'Examples of development work by Andrew Johnstone');
    INSERT INTO domains VALUES (2, 'andrewj.no-ip.com', 1, 'development site on localhost');
    # --------------------------------------------------------
    
    #
    # Table structure for table `groups`
    #
    
    CREATE TABLE groups (
      id int(5) NOT NULL auto_increment,
      title varchar(100) NOT NULL default '',
      domainid int(5) NOT NULL default '0',
      levelid varchar(50) NOT NULL default '',
      description text NOT NULL,
      UNIQUE KEY id (id)
    ) TYPE=InnoDB COMMENT='Group of authenticated user types';
    
    #
    # Dumping data for table `groups`
    #
    
    INSERT INTO groups VALUES (1, 'Admin', 1, '1', '');
    # --------------------------------------------------------
    
    #
    # Table structure for table `levels`
    #
    
    CREATE TABLE levels (
      id int(5) NOT NULL auto_increment,
      title varchar(100) NOT NULL default '',
      groupid int(5) NOT NULL default '0',
      description text NOT NULL,
      PRIMARY KEY  (id)
    ) TYPE=InnoDB COMMENT='Group of authenticated user types';
    
    #
    # Dumping data for table `levels`
    #
    
    INSERT INTO levels VALUES (1, 'Top', 1, 'Top Level');
    # --------------------------------------------------------
    
    #
    # Table structure for table `user`
    #
    # copy from vbulletin (will be altered, but will do for now:D)
    #
    
    CREATE TABLE user (
      userid int(10) unsigned NOT NULL auto_increment,
      groupid smallint(5) unsigned NOT NULL default '0',
      username varchar(50) NOT NULL default '',
      password varchar(50) NOT NULL default '',
      email varchar(50) NOT NULL default '',
      styleid smallint(5) unsigned NOT NULL default '0',
      parentemail varchar(50) NOT NULL default '',
      coppauser smallint(6) NOT NULL default '0',
      homepage varchar(100) NOT NULL default '',
      icq varchar(20) NOT NULL default '',
      aim varchar(20) NOT NULL default '',
      yahoo varchar(20) NOT NULL default '',
      signature mediumtext NOT NULL,
      adminemail smallint(6) NOT NULL default '0',
      showemail smallint(6) NOT NULL default '0',
      invisible smallint(6) NOT NULL default '0',
      usertitle varchar(250) NOT NULL default '',
      customtitle smallint(6) NOT NULL default '0',
      joindate int(10) unsigned NOT NULL default '0',
      cookieuser smallint(6) NOT NULL default '0',
      lastvisit int(10) unsigned NOT NULL default '0',
      lastactivity int(10) unsigned NOT NULL default '0',
      lastpost int(10) unsigned NOT NULL default '0',
      posts smallint(5) unsigned NOT NULL default '0',
      timezoneoffset varchar(4) NOT NULL default '',
      emailnotification smallint(6) NOT NULL default '0',
      buddylist mediumtext NOT NULL,
      ignorelist mediumtext NOT NULL,
      pmfolders mediumtext NOT NULL,
      receivepm smallint(6) NOT NULL default '0',
      emailonpm smallint(6) NOT NULL default '0',
      pmpopup smallint(6) NOT NULL default '0',
      avatarid smallint(6) NOT NULL default '0',
      options smallint(6) NOT NULL default '15',
      birthday date NOT NULL default '0000-00-00',
      maxposts smallint(6) NOT NULL default '-1',
      startofweek smallint(6) NOT NULL default '1',
      ipaddress varchar(20) NOT NULL default '',
      referrerid int(10) unsigned NOT NULL default '0',
      nosessionhash smallint(6) NOT NULL default '0',
      insite smallint(5) unsigned NOT NULL default '0',
      domainid int(5) NOT NULL default '0',
      PRIMARY KEY  (userid,domainid,groupid),
      KEY inforum (insite)
    ) TYPE=InnoDB;
    
    #
    # Dumping data for table `user`
    #
    
    INSERT INTO user VALUES (1, 1, 'andy', '900150983cd24fb0d6963f7d28e17f72', '', 0, '', 0, '', '', '', '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, '', 0, '', '', '', 0, 0, 0, 0, 15, '0000-00-00', -1, 1, '', 0, 0, 0, 2);
    INSERT INTO user VALUES (2, 0, 'andy', '225e8a3fe20e95f6cd9b9e10bfe5eb69', '', 0, '', 0, '', '', '', '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, '', 0, '', '', '', 0, 0, 0, 0, 15, '0000-00-00', -1, 1, '', 0, 0, 0, 2);
    [EDIT]
    Sry for the quick reply, just getting ready to goto the mtv after party...

    Basically i'm verifying users under the domain name, group and level. They can belong to numerous domains / groups or levels, thus using "IN" in the query. I was using the "IF" statements to verify a boolean, as the administrator will have the choice whether users can join multiple domains under the same a/c, along with groups and levels.

    What would you suggest?
    Last edited by Andrew-J2000; Feb 22, 2003 at 08:10.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what i would suggest involves subqueries and unions, so you'll have to break it down into multiple queries that you run one after the other

    select user.domain from user

    select groups.domainid from user join groups

    not sure how levels fits into this


    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •