Add extra peel around query

Hi,

My joomla website has a table: jos_rsform_submission_values.
This table has the following columns: SubmissionValueId, FormId, SubmissionId, FieldName and FieldValue.

The Fieldname column contains: Region, Name, Adress, Telephone and Email.

I managed to create a query that puts some data in a nice line. (See below)
The “problem” is, it shows all the form submissions.

I would love to have this query a bit adapted. I would like to have shown only the data that belongs to a certain region. So, in the form region 111 is submitted. Can i adapt or expand the query below, so that it only shows the Name, Adress,Email and telephone from region 111 ?

Can someone help me on this, or at least point me into the right direction ?
Thanks in advance !

==========================================================

SELECT A.FormId, B.SubmissionId, B.DateSubmitted, B.UserIp,
(SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
(X.FieldName = ‘Name’) AND (X.SubmissionId=B.SubmissionId)) as
Naam ,
(SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
(X.FieldName = ‘adress’) AND (X.SubmissionId=B.SubmissionId)) as
Adress ,
(SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
(X.FieldName = ‘telephone’) AND (X.SubmissionId=B.SubmissionId)) as
Telephone ,
(SELECT FieldValue FROM jos_RSFORM_SUBMISSION_VALUES AS X WHERE
(X.FieldName = ‘email’) AND (X.SubmissionId=B.SubmissionId)) as Email,

FROM jos_RSFORM_FORMS A,
jos_RSFORM_SUBMISSIONS B
WHERE A.FormId=B.FormId
AND A.FormId = 2

==========================================================

Mesjoggah !

SELECT A.FormId
     , B.SubmissionId
     , B.DateSubmitted
     , B.UserIp
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'Name'
            AND SubmissionId = B.SubmissionId ) as Naam 
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'adress'
            AND SubmissionId = B.SubmissionId ) as Adress 
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'telephone'
            AND SubmissionId = B.SubmissionId ) as Telephone 
     , ( SELECT FieldValue 
           FROM jos_RSFORM_SUBMISSION_VALUES 
          WHERE FieldName = 'email'
            AND SubmissionId = B.SubmissionId ) as Email 
  FROM jos_RSFORM_FORMS A
INNER
  JOIN jos_RSFORM_SUBMISSIONS B
    ON B.FormId = A.FormId
[COLOR="Blue"]INNER
  JOIN jos_RSFORM_SUBMISSION_VALUES C
    ON C.SubmissionId = B.SubmissionId 
   AND C.FieldName = 'region'
   AND C.FieldValue = '111'[/COLOR]
 WHERE A.FormId = 2

Thank you !
From the bottom of my heart !

Could not figure it out myself for some reason…

What if i wanted to show the data from a certain region and a certain capacity, located in the same table ?

So, show data, where “region” is 111 and “capacity” is 20 ?
Can I add a simple INNER JOIN to it, like:

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId
AND C.FieldName = ‘region’
AND C.FieldValue = ‘111’
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES D
ON D.SubmissionId = B.SubmissionId
AND D.FieldName = ‘capacity’
AND D.FieldValue = ‘20’

Thanks in advance,
Mesjoggah

what happened when you tested it? :wink:

Hi r937

Thank you for your reply !!!

It does not return any errors, but it returns an empty value…

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0034 sec )

that would indicate that there aren’t any submissions with the specified characteristics

Hi r937,

You’re right.
The database value had some spaces in it.
So the value was not “123” but " 123"

Thank you very much for your support !

Mesjoggah

Yep, right. Here I am…again. :rolleyes:

Well, The “capacity” field can contain more than one number.
Users can check on the front end of the website some checkboxes so the data at the capacity field can contain for example:
>10
10-20
20-30 etc.

I hoped that “LIKE” would be helpfull in this, so i tried:

SELECT A.FormId
, B.SubmissionId
, B.DateSubmitted
, B.UserIp
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘Name’
AND SubmissionId = B.SubmissionId ) as Name
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘adress’
AND SubmissionId = B.SubmissionId ) as Adress
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘telephone’
AND SubmissionId = B.SubmissionId ) as Telephone
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘email’
AND SubmissionId = B.SubmissionId ) as Email
FROM jos_RSFORM_FORMS A
INNER
JOIN jos_RSFORM_SUBMISSIONS B
ON B.FormId = A.FormId
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId
AND C.FieldName = ‘region’
AND C.FieldValue = ‘152’
INNER
JOIN jos_RSFORM_SUBMISSION_VALUES D
ON D.SubmissionId = B.SubmissionId
AND D.FieldName = ‘capacity’
AND D.FieldValue LIKE ‘10-20’
WHERE A.FormId = 2

For some reason it does not return any data or any error. And I’m sure that the 10-20 record exists. Here’s the actual table:

SubmissionValueId FormId SubmissionId FieldName FieldValue
258 2 28 formId 2
256 2 28 Capacity <10
10-20
20-30
30-40
40-50
>50
255 2 28 Telephone 1234567890
254 2 28 Email email@email.nl
251 2 28 Adress Road 1
250 2 28 Name Mr. Testert nr2
247 2 28 Region 97

I’m quite sure you are shaking your head, thinking, how did he miss that…

Thanks in advance,
Mesjoggah

not really

actually, i am shaking my head, thinking, what kind of crappy cms is this poor guy stuck with…

Haha, sorry to see you give up.
Anyway, you helped me a lot.
Thank you very much for that !

Greetz,
A complete mysql nono from The Netherlands.

try AND D.FieldValue LIKE ‘%10-20%’

That did the trick.
You’re brilliant !

I promise, i will not bother you again on this one ! :wink:

Realized i really need much more knowlegde regarding PHP and MySql, so registered today for a course… :slight_smile:

Thanks,
Mesjoggah.


Did a bit of google-ing and came to the conclusion that i was lucky enough to deal with the pick of the bunch when it comes to MySql knowlegde. I mean, when you are mentioned on O’Reilly, yep, then you are big !