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

Dev Shed Forums Sponsor:

September 8th, 2012, 09:47 AM

Nullified

Contributing User

Join Date: Dec 2003

Posts: 159

Time spent in forums: 19 h 23 m 37 sec
Reputation Power: 30

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.

__________________
Option9 Hosting LLC. - Quality Web Hosting Services
Personal, Business & Mature Content Shared Web Hosting

September 8th, 2012, 11:55 AM

Guelphdad

Hockey face

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Nov 2001

Location: St. Catharines, Canada

Posts: 8,141

Time spent in forums: 2 Months 3 Weeks 3 Days 20 h 34 m 13 sec
Reputation Power: 1315

Sounds like you have a table of type Entity, Attribute, Value. Those are very difficult to query.

September 8th, 2012, 11:56 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,837

Time spent in forums: 1 Month 2 Weeks 1 Day 5 h 25 m 30 sec
Reputation Power: 811

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 security • The mystery of errors and exceptions

September 8th, 2012, 12:52 PM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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

September 8th, 2012, 01:45 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,837

Time spent in forums: 1 Month 2 Weeks 1 Day 5 h 25 m 30 sec
Reputation Power: 811

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?

September 8th, 2012, 04:16 PM

Nullified

Contributing User

Join Date: Dec 2003

Posts: 159

Time spent in forums: 19 h 23 m 37 sec
Reputation Power: 30

Quote:

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

........?¿

September 8th, 2012, 07:26 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,359

Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 15 sec
Reputation Power: 4140

Quote:

Originally Posted by Jacques1

Or do you suggest dropping the relational model completely?

actually, how about adopting it?

the EAV scheme is pants

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

September 8th, 2012, 08:23 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,837

Time spent in forums: 1 Month 2 Weeks 1 Day 5 h 25 m 30 sec
Reputation Power: 811

Quote:

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.