SELECT statement with multiple WHERE conditions

Question

I am trying to create a SELECT statement that requires multiple WHERE conditions. Is that possible. Here is the scenario. I have a table with the following structure.

FormID (Type Int)
SubmissionID (Type Int)
FieldName (Type text)
FieldValue (Type text)

The data would be something like this. Table Name: SubmissionsValue

FormID - SubmissionID - FieldName - FieldValue 
24 ------ 5 ---------- username ------ johnk
24 ------ 5 ---------- firstname ------ John
24 ------ 5 ---------- lastname ------ King 
24 ------ 5 ---------- address ------ 100 some street 
24 ------ 5 ---------- email ------ johnemail@gma.com 

24 ------ 9 ---------- username ------ cathym 
24 ------ 9 ---------- firstname ------ Catherine 
24 ------ 9 ---------- lastname ------ Mirusia 
24 ------ 9 ---------- address ------ 100 some other street 
24 ------ 9 ---------- email ------ cathyemail@gma.com

32 ------ 20 ---------- username ------ johnk
32 ------ 20 ---------- firstname ------ John
32 ------ 20 ---------- lastname ------ King 
32 ------ 20 ---------- address ------ 100 some street 
32 ------ 20 ---------- email ------ johnemail@gma.com
32 ------ 20 ---------- AdultTicket------ 5
32 ------ 20 ---------- SingleTicket------ 3
32 ------ 20 ---------- Total ------ $60.00

Apparently this is an Entity-Attribute-Value (EAV) design, I have been told!

As you can see the "FieldName", "FieldValue" etc are the actual names of the Fields of the table. Also entries from multiple forms from a same user can appear in the table. So my keyfield are the user emailID and the formID.

Then fieldnames are stored in the table along with it's fieldvalue for each online form.

Therefore "username", "firstname", "lastname", "address" and "email" are the actual fields in form and when the User submit their information, it gets stored in the table as shown above.

I want to show the details of the User in another area using their emailID as the key field.

Essentially this is what has to happen ($user->email gives the email of the current User):

SELECT username WHERE (FieldName = "email") AND (FieldValue = " $user->email")

But in reality to get the values "johnk" and "cathym" in my example, what it seems that I have to do is the following but I am not sure how to code that:

SELECT FieldValue WHERE (FieldName = "username") 
       (WHERE (FieldName = "email")
       AND (FieldValue = " $user->email")) 

Can someone please help me with this scripting?

This is for a Joomla site using RSForm!Pro. RSForm!Pro stores the form input in the above manner!!!

=== EDIT ====

Clarification: The table data shown above is all from one table. I just added a space between the two sets of data for readability.

You can essentially ignore FormID and SubmissionID. The backend keeps the entries from various different forms all in one table and therefore, it tracks the formID and the submissionID. So you can see that there will be various formID and submissionID in that table, eventually.

In this example, the data was entered from the same form, but two different submissions - 5 & 8.

In this case, I simply want to autopopulate the fields of a new form with the information of the User who is logged in.

Last Name: John King
User Address: 100 Some Street
etc...

Answer 1

You can self join the table:

SELECT t2.FieldValue FROM <table> t1 
       INNER JOIN <table> t2 ON t1.formId = t2.formId 
       WHERE t1.fieldName = 'email' 
         AND t1.fieldValue = '$user->email' 
         AND t2.fieldName = 'username'

I think this will give you the desired result, it is self joining the same table, so you are able to return the correct values. I can't test this right now unfortunately but hopefully this is what you're after.

Basically the table can only return x number of rows, and the rows can't reference other returned rows (as far as I know), so instead of referencing Null values, the table is set to self-join (t1, t2) on the FormId value (the connection between all associated rows) so for each FormId the data to be checked by the WHERE clause is already all of the rows for each FormId.

This is impossible in your current SQL because you don't know the formID and have no way of finding it (It could possibly also be done with a sub-select, but I didn't entertain that route).

So the first part of the new SQL returns the whole block for where FormId = number, and then checks that block for where the email address = name@whatever and then returns the corresponding username, in this example.

I hope this helps.

---

Please also research Prepared Statements and how to correctly code PHP and SQL without leaving your database open to abuse and risk. Please read this link posted by Terminus. PHP The Right Way Is also a very good reference for this subject.

Answer 2

One possibility to solve this is to use subqueries.

SELECT t.FieldValue
FROM table t
WHERE t.FormID = (
  SELECT min(FormID)
  FROM table
  WHERE (FieldName = "email")
    AND (FieldValue = "$user->email")
) AND t.SubmissionID = (
  SELECT min(SubmissionID)
  FROM table
  WHERE (FieldName = "email")
    AND (FieldValue = "$user->email")
) AND t.FieldName ="username"

Another possibility is to join the table with itself.

SELECT u.FieldValue
FROM table u
  JOIN table e ON u.FormID = e.FormID
    AND u.submissionID = e.submissionID
WHERE e.FieldName ="email"
  AND e.FieldValue = "$user->email"
  AND u.FieldName = "username"

The subquery - solution could be slower depending on how well MySQL optimizes the query.

Answer 3

Data set

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(formID INT NOT NULL             -- Cannot be NULL
,SubmissionID INT NOT NULL       -- Cannot be NULL
,FieldName VARCHAR(20) NOT NULL  -- Cannot be NULL
,FieldValue VARCHAR(100) NOT NULL -- Could be NULL. Whether or not this can be NULL has important consequences for some of what follows!!
,PRIMARY KEY(formid,submissionid,fieldname)
);

INSERT INTO my_table VALUES
(24,5,'username', 'johnk'),
(24,5,'firstname','John'),
(24,5,'lastname', 'King'),
(24,5,'address', '100 some street'),
(24,5,'email', 'johnemail@gma.com'),
(24,9,'username', 'cathym'),
(24,9,'firstname','Catherine'),
(24,9,'lastname', 'Mirusia'),
(24,9,'address', '100 some other street'),
(24,9,'email','cathyemail@gma.com');

E.g.:

SELECT x.formid
     , x.submissionid
     , MAX(CASE WHEN fieldname = 'username' THEN fieldvalue END) username
     , MAX(CASE WHEN fieldname = 'firstname' THEN fieldvalue END) firstname
     , MAX(CASE WHEN fieldname = 'lastname' THEN fieldvalue END) lastname
     , MAX(CASE WHEN fieldname = 'address' THEN fieldvalue END) address
     , MAX(CASE WHEN fieldname = 'email' THEN fieldvalue END) email
  FROM my_table x
 GROUP 
    BY formid
     , submissionid
HAVING email = 'johnemail@gma.com';

+--------+--------------+----------+-----------+----------+-----------------+-------------------+
| formid | submissionid | username | firstname | lastname | address         | email             |
+--------+--------------+----------+-----------+----------+-----------------+-------------------+
|     24 |            5 | johnk    | John      | King     | 100 some street | johnemail@gma.com |
+--------+--------------+----------+-----------+----------+-----------------+-------------------+

or

SELECT x.formid
     , x.submissionid
     , x.fieldvalue email
     , MAX(CASE WHEN y.fieldname = 'username' THEN y.fieldvalue END) username
     , MAX(CASE WHEN y.fieldname = 'firstname' THEN y.fieldvalue END) firstname
     , MAX(CASE WHEN y.fieldname = 'lastname' THEN y.fieldvalue END) lastname
     , MAX(CASE WHEN y.fieldname = 'address' THEN y.fieldvalue END) address
  FROM my_table x
  JOIN my_table y
    ON y.formid = x.formid
   AND y.submissionid = x.submissionid
   AND y.fieldname <> x.fieldname
 WHERE x.fieldname= 'email'
   AND x.fieldvalue = 'johnemail@gma.com'
 GROUP 
    BY formid
     , submissionid
     , email;

+--------+--------------+----------+-----------+----------+-----------------+-------------------+
| formid | submissionid | username | firstname | lastname | address         | email             |
+--------+--------------+----------+-----------+----------+-----------------+-------------------+
|     24 |            5 | johnk    | John      | King     | 100 some street | johnemail@gma.com |
+--------+--------------+----------+-----------+----------+-----------------+-------------------+

or

SELECT email.formid
     , email.submissionid
     , email.fieldvalue email
     , username.fieldvalue username
     , firstname.fieldvalue firstname
     , lastname.fieldvalue lastname
     , address.fieldvalue address
  FROM my_table email
  LEFT
  JOIN my_table username
    ON username.formid = email.formid
   AND username.submissionid = email.submissionid
   AND username.fieldname = 'username'
  LEFT
  JOIN my_table firstname
    ON firstname.formid = email.formid
   AND firstname.submissionid = email.submissionid
   AND firstname.fieldname = 'firstname'
  LEFT
  JOIN my_table lastname
    ON lastname.formid = email.formid
   AND lastname.submissionid = email.submissionid
   AND lastname.fieldname = 'lastname'
  LEFT
  JOIN my_table address
    ON address.formid = email.formid
   AND address.submissionid = email.submissionid
   AND address.fieldname = 'address'
 WHERE email.fieldname = 'email'
   AND email.fieldvalue = 'johnemail@gma.com';

+--------+--------------+-------------------+----------+-----------+----------+-----------------+
| formid | submissionid | email             | username | firstname | lastname | address         |
+--------+--------------+-------------------+----------+-----------+----------+-----------------+
|     24 |            5 | johnemail@gma.com | johnk    | John      | King     | 100 some street |
+--------+--------------+-------------------+----------+-----------+----------+-----------------+

EDIT:

If you just want to return all 'fields' associated with a given user submission, that could be as follows, but note that common user data would normally be held inside a dedicated table, even, I'm sure, when using a framework like Joomla:

SELECT a.* 
  FROM my_table a 
  JOIN my_table b 
    ON b.formid = a.formid 
   AND b.submissionid = a.submissionid 
 WHERE b.fieldname = 'email' 
  AND b.fieldvalue = 'johnemail@gma.com';
+--------+--------------+-----------+-------------------+
| formID | SubmissionID | FieldName | FieldValue        |
+--------+--------------+-----------+-------------------+
|     24 |            5 | address   | 100 some street   |
|     24 |            5 | email     | johnemail@gma.com |
|     24 |            5 | firstname | John              |
|     24 |            5 | lastname  | King              |
|     24 |            5 | username  | johnk             |
+--------+--------------+-----------+-------------------+

Answer 4

First, get the formId and SubmissionId corresponding to the email in a subquery and get the username from the obtained formId and SubmissionId

SELECT FieldValue FROM table t1, 
       (SELECT FormID, SubmissionID FROM table 
        WHERE FieldName='email' AND FieldValue = '$user->email') 
t2 WHERE t1.SubmissionID = t2.SubmissionID 
        AND t1.FormId = t2.FormID 
        AND t1.FieldName = 'username'