#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31

    Select value from table where varname = '3 different things''


    I have a table that I am trying to select the info from the value column where the varname column equals illegalusernames, minusernamelength & maxusernamelength.

    Something like:
    Code:
    "SELECT value AS illegalusernames FROM ".TABLE_PREFIX."setting WHERE varname = 'illegalusernames'"
    "SELECT value AS minusernamelength FROM ".TABLE_PREFIX."setting WHERE varname = 'minusernamelength'"
    "SELECT value AS maxusernamelength FROM ".TABLE_PREFIX."setting WHERE varname = 'maxusernamelength'"
    I want to do this without having to pull 3 different queries.
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Sounds like you have a table of type Entity, Attribute, Value. Those are very difficult to query.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    Why don't you simply combine the conditions with OR? A shorter solution is the IN syntax:

    Code:
    SELECT
        varname,
        value
    FROM
        (your TABLE_PREFIX) setting
    WHERE
        varname IN ('illegalusernames', 'minusernamelength', 'maxusernamelength')

    Comments on this post

    • Nullified agrees : This is what I was looking for. thanks.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Code:
    DROP TABLE IF EXISTS eav_hell;
    CREATE TABLE eav_hell
    (entity INT NOT NULL
    ,attribute VARCHAR(50) NOT NULL
    ,value VARCHAR(50) NOT NULL
    ,PRIMARY KEY(entity,attribute)
    );
    
    INSERT INTO eav_hell VALUES
    (1,'illegalusernames','Administrator'),
    (1,'minusernamelength','6'),
    (1,'maxusernamelength','50'),
    (2,'illegalusernames','Guest'),
    (2,'minusernamelength','12'),
    (2,'maxusernamelength','50');
    
    SELECT * FROM eav_hell;
    +--------+-------------------+---------------+
    | entity | attribute         | value         |
    +--------+-------------------+---------------+
    |      1 | illegalusernames  | Administrator |
    |      1 | maxusernamelength | 50            |
    |      1 | minusernamelength | 6             |
    |      2 | illegalusernames  | Guest         |
    |      2 | maxusernamelength | 50            |
    |      2 | minusernamelength | 12            |
    +--------+-------------------+---------------+
    
    SELECT entity
         , MAX(CASE WHEN attribute = 'illegalusernames' THEN value END) illegalusernames
         , MAX(CASE WHEN attribute = 'maxusernamelength' THEN value END) maxusernamelength
         , MAX(CASE WHEN attribute = 'minusernamelength' THEN value END) minusernamelength
      FROM eav_hell
     GROUP
        BY entity;
    
    +--------+------------------+-------------------+-------------------+
    | entity | illegalusernames | maxusernamelength | minusernamelength |
    +--------+------------------+-------------------+-------------------+
    |      1 | Administrator    | 50                | 6                 |
    |      2 | Guest            | 50                | 12                |
    +--------+------------------+-------------------+-------------------+
    Now, a moment's silence for those poor, discarded data types...
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Well, what's your ingenious solution for this problem? I'm sure the CMS developers of the world will happily implement it.

    Or do you suggest dropping the relational model completely?
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31
    Originally Posted by cafelatte
    Code:
    DROP TABLE IF EXISTS eav_hell;
    CREATE TABLE eav_hell
    (entity INT NOT NULL
    ,attribute VARCHAR(50) NOT NULL
    ,value VARCHAR(50) NOT NULL
    ,PRIMARY KEY(entity,attribute)
    );
    
    INSERT INTO eav_hell VALUES
    (1,'illegalusernames','Administrator'),
    (1,'minusernamelength','6'),
    (1,'maxusernamelength','50'),
    (2,'illegalusernames','Guest'),
    (2,'minusernamelength','12'),
    (2,'maxusernamelength','50');
    
    SELECT * FROM eav_hell;
    +--------+-------------------+---------------+
    | entity | attribute         | value         |
    +--------+-------------------+---------------+
    |      1 | illegalusernames  | Administrator |
    |      1 | maxusernamelength | 50            |
    |      1 | minusernamelength | 6             |
    |      2 | illegalusernames  | Guest         |
    |      2 | maxusernamelength | 50            |
    |      2 | minusernamelength | 12            |
    +--------+-------------------+---------------+
    
    SELECT entity
         , MAX(CASE WHEN attribute = 'illegalusernames' THEN value END) illegalusernames
         , MAX(CASE WHEN attribute = 'maxusernamelength' THEN value END) maxusernamelength
         , MAX(CASE WHEN attribute = 'minusernamelength' THEN value END) minusernamelength
      FROM eav_hell
     GROUP
        BY entity;
    
    +--------+------------------+-------------------+-------------------+
    | entity | illegalusernames | maxusernamelength | minusernamelength |
    +--------+------------------+-------------------+-------------------+
    |      1 | Administrator    | 50                | 6                 |
    |      2 | Guest            | 50                | 12                |
    +--------+------------------+-------------------+-------------------+
    Now, a moment's silence for those poor, discarded data types...
    ........?¿
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by Jacques1
    Or do you suggest dropping the relational model completely?
    actually, how about adopting it?

    the EAV scheme is pants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by r937
    actually, how about adopting it?
    Great. So we'll have 100 configuration tables with 50 columns each and some hack to prevent more than one row being inserted? Lovely, that's so much better than this bad, bad key value approach. Let's tell those damn amateur developers of Drupal, Joomla, Magento and whatnot that they've got it all wrong.

    Seriously: I do understand your concerns from an academic point of view. But this is about real life. And this sometimes means that practical issues are more important than having a textbook perfect data model.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by Jacques1
    Let's tell those damn amateur developers of Drupal, Joomla, Magento and whatnot that they've got it all wrong.
    no, let's not

    actually, you can go ahead, but i won't


    look... look at all...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Yeah, your love for yourself is endless.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by Jacques1
    Yeah, your love for yourself is endless.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo